CASE without SELECT CASE? 1

[tranchemontaigne]

I'm receiving this compile error in MS Access 2000 (9.0.6926 SP-3). The compile error occurs at the 5th case in the list of cases. I don't understand why it can see some cases but not see all of them. Is there something wrong with my code?

(This code is in a form module on frmSearch that is used to determine what records to display in a listbox on the main form of the access application. I want a click event on the cboSearchField control to dynamically display a list of values in listbox on the frmSearch)


Private Sub cboSearchField_Click()
On Error GoTo Err_cboSearchField_Click

Dim strSearchCriteria As String

strSearchCriteria = [Forms]![frmSearch]![cboSearchField].Value

Select Case strSearchCriteria
Case "Drugs"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblIllegal_Drug; "
![lboSearchOptions].Requery
End With

Case "DayCare_Child/Employee"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ;"
![lboSearchOptions].Requery

End With

Case "Close_Contact_To_Case(HH)/(Sex)"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblContact_Status; "
![lboSearchOptions].Requery
End With

Case "Food_Worker"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ;"
![lboSearchOptions].Requery
End With

Case "IG_Provider"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tbIG_Provider;"
![lboSearchOptions].Requery

Case "IG_Not_Given_Reason"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblIG_Not_Given_Reason;"
![lboSearchOptions].Requery

Case "Vaccination_Provided"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ;"
![lboSearchOptions].Requery

Case "Completed_Interview"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ;"
![lboSearchOptions].Requery

Case ">2_Sexual_Partners"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ;"
![lboSearchOptions].Requery

Case "Ate_Raw_Shellfish"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ;"
![lboSearchOptions].Requery

Case "Food_Waterborne_OB"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ; "
![lboSearchOptions].Requery

Case "Arrested_Last_6_Months"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ; "
![lboSearchOptions].Requery

Case "MSM"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ; "
![lboSearchOptions].Requery

Case "Age"
With [Forms]![frmSearch]
![lblSearchFor].Visible = False
![lblBetween].Visible = True
![txtEnd].Visible = True
![lboSearchOptions].Visible = False
'![lboSearchOptions].RowSource = "SELECT "
'![lboSearchOptions].Requery

Case "Hep_C+/other_pre-existing_condition"
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = True
![lboSearchOptions].RowSource = "SELECT * FROM tblYesNo ; "
![lboSearchOptions].Requery

Case Else 'Person_Name_Last, Person_Name_First, Person_City
With [Forms]![frmSearch]
![lblSearchFor].Visible = True
![lblBetween].Visible = False
![txtEnd].Visible = False
![lboSearchOptions].Visible = False

End With

End Select

subfrmOptionValuesStatus

Exit_cboSearchField_Click:
Exit Sub

Err_cboSearchField_Click:
MsgBox "Form: frmSearch" & Chr(10) & "Event: cboSearchField_Click" & Chr(10) & Chr(10) & Err.Description
Resume Exit_cboSearchField_Click

End Sub

 

[PHV]

Lack of End With in many Case blocks ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tranchemontaigne]

subfrmOptionValuesStatus (just after the END SELECT statement) should be removed from the module as it does not belong here. Removing this piece of code does not resolve the CASE without SELECT CASE compile error.

 

[PHV]

Have you read my previous post ?

 

[tranchemontaigne]

Thanks! I copied/pasted so much that I overlooked what I should have seen had I not been looking at the code so much.

The compile error went away with the end with statements.