If...Then...Else Help

[Guest]

I'm trying to build a database which contains questions for audits. I'm new
at VB and having trouble with an If...Then...Else statement. I'm trying, in
a form, to set up a search of questions. Using three combo boxes, employees
can specify requirements, sub-requirements, and detailed requirements. After
they specify their requirements and such, they hit search which copies the
questions which fit their requirements into a new table. However, I can't
get this to work.

Here's my code:

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String

stDocName = "Search"
DoCmd.RunMacro stDocName
DoCmd.RunSQL "Delete From [Searched Questions]"
If Combo10 <> "" Then
DoCmd.RunSQL "INSERT INTO [Searched Questions] ( [ID #] ) SELECT
[ISO Questions].[ID #] FROM [ISO Questions] WHERE ((([ISO
Questions].Requirement)=[Combo4]) AND (([ISO
Questions].[Sub-Requirement])=[Combo7]) AND (([ISO Questions].[Detailed
Requirement])=[Combo10]))"
ElseIf Combo10 = "" And Combo7 <> "" Then
DoCmd.RunSQL "INSERT INTO [Searched Questions] ( [ID #] ) SELECT
[ISO Questions].[ID #] FROM [ISO Questions] WHERE ((([ISO
Questions].Requirement)=[Combo4] ) AND (([ISO
Questions].[Sub-Requirement])=[Combo7]))"
ElseIf Combo10 = "" And Combo7 = "" Then
DoCmd.RunSQL "INSERT INTO [Searched Questions] ( [ID #] ) SELECT
[ISO Questions].[ID #] FROM [ISO Questions] WHERE ((([ISO
Questions].Requirement)=[Combo4]))"
End If
DoCmd.RunMacro "Refresh Questions"

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub


Am i doing something wrong in my If...Then statements? Any idea on how to
fix the problem?

Thanks for the help

 

[Douglas J Steele]

If nothing's selected in a combobox, it'll return Null, not an empty string.

See whether:

If Not IsNull(Combo10) Then

ElseIf IsNull(Combo10) And Not IsNull(Combo7) Then

ElseIf IsNull(Combo10) And IsNull(Combo7) Then

End If

works any better for you.

 

[Guest]

Works perfect....thank you very much

If nothing's selected in a combobox, it'll return Null, not an empty string.

See whether:

If Not IsNull(Combo10) Then

ElseIf IsNull(Combo10) And Not IsNull(Combo7) Then

ElseIf IsNull(Combo10) And IsNull(Combo7) Then

End If

works any better for you.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm trying to build a database which contains questions for audits. I'm new
at VB and having trouble with an If...Then...Else statement. I'm trying, in
a form, to set up a search of questions. Using three combo boxes, employees
can specify requirements, sub-requirements, and detailed requirements. After
they specify their requirements and such, they hit search which copies the
questions which fit their requirements into a new table. However, I can't
get this to work.

Here's my code:

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String

stDocName = "Search"
DoCmd.RunMacro stDocName
DoCmd.RunSQL "Delete From [Searched Questions]"
If Combo10 <> "" Then
DoCmd.RunSQL "INSERT INTO [Searched Questions] ( [ID #] ) SELECT
[ISO Questions].[ID #] FROM [ISO Questions] WHERE ((([ISO
Questions].Requirement)=[Combo4]) AND (([ISO
Questions].[Sub-Requirement])=[Combo7]) AND (([ISO Questions].[Detailed
Requirement])=[Combo10]))"
ElseIf Combo10 = "" And Combo7 <> "" Then
DoCmd.RunSQL "INSERT INTO [Searched Questions] ( [ID #] ) SELECT
[ISO Questions].[ID #] FROM [ISO Questions] WHERE ((([ISO
Questions].Requirement)=[Combo4] ) AND (([ISO
Questions].[Sub-Requirement])=[Combo7]))"
ElseIf Combo10 = "" And Combo7 = "" Then
DoCmd.RunSQL "INSERT INTO [Searched Questions] ( [ID #] ) SELECT
[ISO Questions].[ID #] FROM [ISO Questions] WHERE ((([ISO
Questions].Requirement)=[Combo4]))"
End If
DoCmd.RunMacro "Refresh Questions"

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub


Am i doing something wrong in my If...Then statements? Any idea on how to
fix the problem?

Thanks for the help