If Then Else (1 Viewer)

[mr_fish]

I have some code attached to a command button (shown below) that is supposed to check a form to see whether a number of fields are filled in before executing code to link a child field and open a the child table.

For some reason it only ever completes the first if for journalists field checking, when you then fill in the journalist field, the next time you click the command button it executes the code to link the tables even though the other conditions aren't true.

Not sure where I'm going wrong, any help would be greatly appreciated. THANKS

Private Sub Command100_Click()
On Error GoTo Err_Command100_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim inCovID As Integer

If IsNull(Me!cmboJournalist) Or Me!cmboJournalist = "" Then
MsgBox prompt:="Please Select a JOURNALIST!", buttons:=vbOKOnly, Title:="Integrity ERROR"
Exit Sub
ElseIf IsNull(Me!cmboProductSelect) Or Me!cmboProductSelect = "" Then
MsgBox prompt:="Please Select a PRODUCT!", buttons:=vbOKOnly, Title:="Integrity ERROR"
Exit Sub

ElseIf IsNull(Me!cmboCoverageType) Or cmboCoverageType = "" Then
MsgBox prompt:="Please select COVERAGE TYPE!", buttons:=vbOKOnly, Title:="Integrity ERROR"
Exit Sub

ElseIf Me!cmboCoverageType = "1" Then
MsgBox prompt:="You can't send a sample for News Coverage, enter a separate REVIEW field!", buttons:=vbOKOnly, Title:="Integrity ERROR"
Exit Sub

ElseIf IsNull(Me!txtChildLink) Or Me!txtChildLink = "" Then
inCovID = Me![txtCoverageID]
Me!txtChildLink = inCovID
Me.Requery

stLinkCriteria = "[tbl_Coverage_ID]=" & inCovID
stDocName = "SAMPLE_TRANSACTION"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else

stLinkCriteria = "[tbl_Coverage_ID]=" & Me![txtCoverageID]
stDocName = "SAMPLE_TRANSACTION"
DoCmd.OpenForm stDocName, , , stLinkCriteria


End If
Exit_Command100_Click:
Exit Sub

Err_Command100_Click:
'MsgBox Err.Description
Resume Exit_Command100_Click

End Sub

 

[KenHigg]

I think you should simply make these a series of seperate 'if's', not 'else if's'

Make sense?

 

[mr_fish]

hi ken,

have thought about that, but I wanted to check that three condition are true each time the button is pressed before the code is run and I imagined separate ifs wouldn't do that?

 

[Bat17]

ElseIf Me!cmboCoverageType = "1" Then

Dont think this will fire if Me!cmboCoverageType is numeric

Try

ElseIf Me!cmboCoverageType = 1 Then

Peter

 

[KenHigg]

The seperate ifs should work. But two things here. One is that a little cleaner test would be as follows:

Intead of:

if IsNull(Me!cmboJournalist) Or Me!cmboJournalist = "" then

How about

if Me!cmboJournalist & "" = "" then

Second, I would do all the tests and supply the user with a list of all the errors at one time instead of one error at a time.

 

[Bat17]

If you add a break point you could step through the code and see what values are being returned. That should help you spot the problem.

peter

 

[Rich]

Replace all but the last ExitSub with EndIf

 

[mr_fish]

Thanks for your help so far, have tried recoding a bit with just ifs as suggested. This time I've got it to display all errors on the button press (when the form is completely empty), but as soon as the journalist field is filled in it no longer checks the other ifs and just goes ahead with the linking code.

I'm going to try breakpoints, but this may take a little while as I haven't tried them before.

 

[KenHigg]

So you have something like:

'Test fields section
If Me!cmboJournalist & "" = "" Then
    MsgBox prompt:="Please Select a JOURNALIST!", buttons:=vbOKOnly, Title:="Integrity ERROR"
    Exit Sub
End If

If Me!cmboProductSelect & "" = "" Then
    MsgBox prompt:="Please Select a PRODUCT!", buttons:=vbOKOnly, Title:="Integrity ERROR"
    Exit Sub
End If

If me!cmboCoverageType  & "" = "" Then
    MsgBox prompt:="Please select COVERAGE TYPE!", buttons:=vbOKOnly,  Title:="Integrity ERROR"
    Exit Sub
End If

If Me!cmboCoverageType = "1" Then
    MsgBox prompt:="You can't send a sample for News Coverage, enter a separate REVIEW field!", buttons:=vbOKOnly, Title:="Integrity ERROR"
    Exit Sub
End If

'Open Form section
If Me!txtChildLink & "" = "" Then
    inCovID = Me![txtCoverageID]
    Me!txtChildLink = inCovID
    stLinkCriteria = "[tbl_Coverage_ID]=" & inCovID
    stDocName = "SAMPLE_TRANSACTION"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
    stLinkCriteria = "[tbl_Coverage_ID]=" & Me![txtCoverageID]
    stDocName = "SAMPLE_TRANSACTION"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End if

 

[mr_fish]

yes ken, thats pretty much it, yet as soon as the first if is true, none of the others trigger except the one that tries to link and open the new form.

I've done similar condition tests before without having these problems so its getting pretty frustrating.

 

[KenHigg]

Ok. I see I'm in up to my neck on this one so I may as well plough thru

To start with, here's how I would do the error checks, see if it makes sense:

dim strErrMsg as string

'Test fields section
If Me!cmboJournalist & "" = "" Then
    strErrMsg = "Please Select a JOURNALIST!"
End if

If Me!cmboProductSelect & "" = "" Then
   strErrMsg = strErrMsg & vbCRLF & "Please Select a PRODUCT!" 
End If

If me!cmboCoverageType  & "" = "" Then
    strErrMsg = strErrMsg & vbCRLF & "Please select COVERAGE TYPE!" 
End If

If Me!cmboCoverageType = "1" Then
    strErrMsg = strErrMsg & vbCRLF & "You can't send a sample for News Coverage, enter a separate REVIEW field!" 
End If

If strErrMsg & "" <> ""
    MsgBox prompt:=strErrMsg, buttons:=vbOKOnly, Title:="Integrity ERROR"
    Exit Sub
End If

 

[mr_fish]

I see where your coming from Ken, thanks for sticking with me. I just tried your error checking and the same thing is happening. When the form is empty and you click the button, the three errors are reported in the message box, as soon as I fill in one of the three combo boxes being tested none of the errors appear.

 

[KenHigg]

You must have something else going on. Works when I try it - see my attachment...

Can you post a sample db?

 

[mr_fish]

OK, this is definately some sort of access weirdness.

I created a new form and placed three combo boxes called cmbo1, cmbo2, cmbo3 linked to tables via query that is in the Record Source property of the form.

SELECT COVERAGE.tbl_Coverage_ID, COVERAGE.tbl_Coverage_Media_Journalist, COVERAGE.tbl_Coverage_Type, COVERAGE.tbl_Media_ID, COVERAGE.tbl_Product_ID, COVERAGE.tbl_Coverage_Cover_Date, COVERAGE.tbl_Coverage_Publication_Date, COVERAGE.tbl_Coverage_Rating, COVERAGE.tbl_Coverage_Award, COVERAGE.tbl_Coverage_Picture, COVERAGE.tbl_Coverage_Press_Release, COVERAGE.tbl_Coverage_Published, COVERAGE.tbl_Coverage_URL, MEDIA.tbl_Media_CompanyName, MEDIA.tbl_Media_Contact_FirstName, MEDIA.tbl_Media_Contact_Surname, MEDIA.tbl_Media_Circulation, MEDIA.tbl_Media_Type, MEDIA.tbl_Media_Cover_Date, MEDIA.tbl_Media_Audience, SAMPLE_TRANSACTION.tbl_Coverage_ID FROM MEDIA INNER JOIN (COVERAGE LEFT JOIN SAMPLE_TRANSACTION ON COVERAGE.tbl_Coverage_ID=SAMPLE_TRANSACTION.tbl_Coverage_ID) ON MEDIA.tbl_Media_ID=COVERAGE.tbl_Media_ID;

The code I used to test was an exact copy of Ken's database shown above bar the name of the combo boxes changing.

Private Sub Command12_Click()

Dim strErrMsg As String

'Test fields section

If Me!cmbo1 & "" = "" Then
strErrMsg = "Please Select a JOURNALIST!"
End If

If Me!cmbo2 & "" = "" Then
strErrMsg = strErrMsg & vbCrLf & "Please Select a PRODUCT!"
End If

If Me!cmbo3 & "" = "" Then
strErrMsg = strErrMsg & vbCrLf & "Please select COVERAGE TYPE!"
End If

If strErrMsg & "" <> "" Then
MsgBox prompt:=strErrMsg, buttons:=vbOKOnly, Title:="Integrity ERROR"
Exit Sub
End If

MsgBox "The stuff worked!"

End Sub

The results were with no data in the table

Please Select a JOURNALIST!
Please Select a PRODUCT!
Please select COVERAGE TYPE!

in the message box

As soon as the any of the combo boxes is dropped and a selection is made clicking the button showed

The stuff worked!

Without the other two being selected.

No other code is in the form.......A perplexing mystery

 

[Bat17]

I would add a message to the scond box to see what it thinks is there. then run it with a selection in the first and last combo's and leave second blank.

If Me!cmbo1 & "" = "" Then
strErrMsg = "Please Select a JOURNALIST!"
End If

If Me!cmbo2 & "" = "" Then
strErrMsg = strErrMsg & vbCrLf & "Please Select a PRODUCT!"
else
msgbox "found " & Me!cmbo2
End If

If Me!cmbo3 & "" = "" Then
strErrMsg = strErrMsg & vbCrLf & "Please select COVERAGE TYPE!"
End If

HTH

Peter

 

[mr_fish]

Thanks for that code Peter, but it doesn't appear to trigger anything when the button is clicked.

 

[The Stoat]

Hi,

I used this and it worked fine.

HTH

TS

Private Sub Command12_Click()
Dim StrErrMsg As String

If Me!Cmbo1 & "" = "" Then
StrErrMsg = "Please Select a JOURNALIST!"
End If

If Me!Cmbo2 & "" = "" Then
StrErrMsg = StrErrMsg & vbCrLf & "Please Select a PRODUCT!"
End If

If Me!Cmbo3 & "" = "" Then
StrErrMsg = StrErrMsg & vbCrLf & "Please select COVERAGE TYPE!"
End If

If StrErrMsg & "" <> "" Then
MsgBox prompt:=StrErrMsg, buttons:=vbOKOnly, Title:="Integrity ERROR"
Exit sub
End If

MsgBox "The stuff worked!"

End Sub

 

[mr_fish]

With the amended code, after a single selection is made it just brings up the The Stuff Worked! message box.

I'm losing the will to live with this one, it's just about the most basic programming thing and I've got absolutely no idea why it's not doing what it should.

Does anyone have a suggestion that doesn't include IF THEN statements?

cheers

 

[The Stoat]

Ok having re-read this thread my conclusion was a bit hasty as Mr_fish seems to have tried the above code without success in an earlier thread. In which case i'm a out of ideas.

 

[The Stoat]

Ok here is a db with essentially the same code - different underlying table. See if that works for you and see if there is anything different. I've saved it as a 97 so it should work for most versions. I'd double check that you have all the names of the controls referenced correctly in the code as well.