open forms from listbox

[natep26]

On my form I have a listbox "Insplist" generated by a query. The listbox shows the types of inspections available (there are 5 unique inspection types).

When a value from the listbox is double clicked, I want it to open the corresponding inspection form.

At this point I can only get one form to open regardless of the value selected from the listbox...or with this code, it seems I am close but I receive a Syntax error (missing operator) in query expression '[insptype] =el paso stripper 2 headers'.

Where 'el paso stipper 2 headers' is an inspection type.

Private Sub InspList_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(InspList) Then
bopeneps_Click
End If

End Sub

Private Sub bopeneps_Click()
On Error GoTo Err_bopeneps_Click

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.openform "finspeps", , , "[insptype] =" & Me.InspList

Exit_bopeneps_Click:
Exit Sub

Err_bopeneps_Click:
MsgBox Err.Description
Resume Exit_bopeneps_Click

End Sub


Thanks for your help

 

[boblarson]

Try this:

DoCmd.openform "finspeps", , , "[insptype] ='" & Me.InspList & "'"

 

[natep26]

Thanks Bob. That got rid of the syntax error.

How do I set up the code to open a specific form for each value in the listbox?

Thanks

 

[boblarson]

You can use a select case if you have certain forms. Here's a quick sample with bogus info. Since InspList is returning text you need to use the quotes for the case statements, as shown.

Select Case Me.InspList

Case "WhateverHere"

   DoCmd.OpenForm "FormWhatever"

Case "AnotherValueHere"
   
   DoCmd.OpenForm "AnotherForm"

Case Else
   DoCmd.OpenForm "AnythingThatDoesn'tMatch"
End Select

 

[Rich]

Are you really saying that you have separate forms depending on the inspection type?

 

[natep26]

Yes. Each inspection type has a different inspection method and therefore completely different fields