If statements not working! Troubleshooters needed...

[caringsharingbristolbilly]

Hi guys.

I don't know what's going wrong with this. I've been toying with it for a few days, and can't figure it out. It's the first bit of code which I'm struggling with.

I want it to first look at the ProductType box, and if it hasn't changed from the default, to prompt the user to enter a valid choice.

Then I want it to go through all the mandatory required fields on the form and check that they all have values in, and again alert them if any are missing (I've only done the FirstName field so far, but I need to do about 8).

It does the first bit okay, warning people about the ProductType , but if the ProductType entry is okay, but the first name is missing, I don't get a message box, it just carries on with the rest of the code.

Could someone a little more experienced than me suggest where I'm going wrong? I'm very new to VBA, so I might have done it fairly clumsily...

Private Sub FinishedButton_Click()
On Error GoTo Err_FinishedButton_Click

If Me.ProductType = "Please Select One" Then
    MsgBox "You haven't selected a product type. Please select the required product and try again.", 48, "Insufficient Information"
    Me.ProductType.SetFocus
    
ElseIf Me.FirstName = Null Then
    MsgBox "You haven't entered a first name. Please do so and try again.", 48, "Insufficient Information"
    Me.FirstName.SetFocus

' This bit looks at the product name and assigns a code accordingly:
ElseIf Me.ProductType = "Unit Trust" Then
    ProductCode = "UT"
    GoTo FinishRoutine
        ElseIf Me.ProductType = "ISA" Then
        Me.ProductCode = "ISA"
        GoTo FinishRoutine
            ElseIf Me.ProductType = "Bond" Then
            Me.ProductCode = "B"
            GoTo FinishRoutine

' This bit assigns a number and makes the relevant cells visible:

FinishRoutine:
Me.Finished = "Finished"
Me.AutoNumberZeroes = Format(Me.AutonumberID, "0000000")
Me.PolicyNo = Me.ProductCode & Me.AutoNumberZeroes
Me.DBInput = Now()
Me.UserName = Forms![MainMenu]![NameBox]
Me.PolicyNo.Visible = True
Me.PolicyNo_Label.Visible = True
Me.DBInput.Visible = True
Me.DBInput_Label.Visible = True
Me.UserName.Visible = True
Me.UserName_Label.Visible = True
Me.NextRecordButton.Visible = True
Exit_FinishedButton_Click:

End If

    Exit Sub

Any help greatly appreciated...

 

[macleanb]

I havent studied your code line by line, and I am no access guru - but I do notice that your if/elseifs check different things (product type & name). Remeber only one stament in an if/elseif/else block is going to be executed, even if stuff further down is also true - I suspect you may need to seperate this into seperate if staements (except where you are testing the same thing which is safe)

 

[Norie]

Does this code work any better?

Private Sub FinishedButton_Click()

    If Me.ProductType = "Please Select One" Then
        MsgBox "You haven't selected a product type. Please select the required product and try again.", 48, "Insufficient Information"
        Me.ProductType.SetFocus
        Exit Sub
    End If
    
    If Me.FirstName = Null Then
        MsgBox "You haven't entered a first name. Please do so and try again.", 48, "Insufficient Information"
        Me.FirstName.SetFocus
        Exit Sub
    End If
    
    ' This bit looks at the product name and assigns a code accordingly:
    Select Case Me.ProductType
    
        Case "Unit Trust"
            Me.ProductCode = "UT"
        
        Case "ISA"
            Me.ProductCode = "ISA"
            
        Case "Bond"
            Me.ProductCode = "B"
    End Select
        
    Me.Finished = "Finished"
    Me.AutoNumberZeroes = Format(Me.AutonumberID, "0000000")
    Me.PolicyNo = Me.ProductCode & Me.AutoNumberZeroes
    Me.DBInput = Now()
    Me.UserName = Forms![MainMenu]![NameBox]
    Me.PolicyNo.Visible = True
    Me.PolicyNo_Label.Visible = True
    Me.DBInput.Visible = True
    Me.DBInput_Label.Visible = True
    Me.UserName.Visible = True
    Me.UserName_Label.Visible = True
    Me.NextRecordButton.Visible = True

Exit Sub

By the way could you not use Validation to check that the fields are filled in?

You could even set the Required property in the underlying table to yes.

 

[caringsharingbristolbilly]

Hi Norie & Macleanb...

Many thanks, as always for your input. As always, I'm not worthy ray:. Macleanb, I think Norie did in his code what you suggested, so I'll focus on that...

Unfortunately, while the code looks quite a bit tidier than mine, I'm getting the same result. The code is stopped if the ProductType field is "Please select one", but the code carries on without the warning MsgBox even when FirstName is blank...

I've looked at using validation, but I need the validation to run when I hit the button, not when they try and leave the record. This is because I only want to assign a policy number (which is what the second part of the code does) once all the information is present & correct... Also, the required fields will ultimately (once I get this bit of code fixed) be dependent on the product they've selected.

Any further ideas?

 

[Norie]

Try a different test to see if the FirstName field is empty.

Maybe

If Len(FirstName)= 0 Then

 

[caringsharingbristolbilly]

Hi and thanks again, Norie.

Tried the suggestion, but sadly still nothing... Same problem as before. Anything else I should try? Bear in mind I'm fairly newbie to all this, so consider stupid things I might not even know I could have done wrong!!

 

[Norie]

Have you stepped through the code using F8?

Does FirstName have a default value?

 

[caringsharingbristolbilly]

Hi again.

Just tried stepping through it in Break mode, and the second "If" function doesn't seem to be recognising that the FirstName field is blank, it's skipping straight to the "End If" bit, then continuing...

FirstName doesn't have a default value, either.

Bit of a head-scratcher... No doubt this'll be something obvious and quite embarrassing for me...

 

[Norie]

Add a watch on FirstName and see what value it has.

Also might be worth trying Me.FirstName.Text (or .Value, don't have Access available right now).

 

[caringsharingbristolbilly]

Hi again, Norie.

Tried both of those. FirstName remains blank throughout, and the Me.FirstName.Text hasn't worked either.

I also tried doing it the other way round, namely:

If FirstName <> Null Then
        GoTo Finishroutine
        Else: MsgBox "You haven't entered a first name. Please do so and try again.", 48, "Insufficient Information"
        Me.FirstName.SetFocus
        Exit Sub
    End If

...but this had the opposite effect, claiming it was missing when it patently wasn't.

I think Access is laughing at me...