I have designed a search Form with the following codes:
Option Compare Database
Option Explicit
Dim strRowsource As String
Private Sub Form_Open(Cancel As Integer)
Me.Caption = "SEARCH FORM"
strRowsource = "SELECT AddressQry.ID, AddressQry.NAME, AddressQry.CITY, AddressQry.STATE, AddressQry.TYPE " & _
"FROM AddressQry " & _
"ORDER BY AddressQry.Name"
List1.RowSource = strRowsource
End Sub
Private Sub List1_DblClick(Cancel As Integer)
If Me.List1.Column(4) = "[Seed Party]" Then
DoCmd.OpenForm "frmSdPartyCorrection", , , "[seedPartyID]=" & List1.Column(0)
ElseIf Me.List1.Column(4) = "[Factory]" Then
DoCmd.OpenForm "frmFactoryCorrection", , , "[factoryID]=" & List1.Column(0)
End If
End Sub
Private Sub txtSearch_Change()
If Frame1 = 1 Then
strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
"FROM AddressQry " & _
"WHERE[Name] Like'*" & Me.txtSearch.Text & "*'" & _
"ORDER BY AddressQry.Name"
ElseIf Frame1 = 2 Then
strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
"FROM AddressQry " & _
"WHERE[City] Like'" & Me.txtSearch.Text & "*'" & _
"ORDER BY AddressQry.Name"
ElseIf Frame1 = 3 Then
strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
"FROM AddressQry " & _
"WHERE[State] Like'" & Me.txtSearch.Text & "*'" & _
"ORDER BY AddressQry.Name"
ElseIf Frame1 = 4 Then
strRowsource = "SELECT [ID], [Name], [City], [State], [Type] " & _
"FROM AddressQry " & _
"WHERE[Type] Like'" & Me.txtSearch.Text & "*'" & _
"ORDER BY AddressQry.Name"
End If
List1.RowSource = strRowsource
End Sub
The List Box loads correctly when the Form opens. The txtSearch_Change Function also works properly. However, on double clicking a particular item in the List, the relevant Form should open based on the 5th item in the column ie, Column(4). I will be either "Seed Party" or "Factory". The Double click event is not generating the forms.The SQL is from the Union Qry based on two different tables.
Please help.