Help! Can't get this code to work

[pd06498]

Would someone with more knowledge than I have a look at this code and see if they can see why it is not working.

I have set up a form to search with 5 fields and the results of this are supposed to populate a list box on the same form. When I press the search button, nothing happens.

Fields explained:
ItemName = text and number document names
SerialFrom = possible character and number
Regimental = 4 or 5 diget number
ArchiveFolio = Numeral and/or letter
DestructionYear = 4 digit numeral (i.e. year)

Fields are drawn, through a query (called qryPullData) from tables tblItem (ItemName) and AccountableItem (remainder of fields).

Much appreciated in advance...

Private Sub Search_Click()

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT tblItem.ItemName, tblAccountableItem.SerialFrom, tblAccountableItem.Regimental, tblAccountableItem.ArchiveFolio, tblAccountableItem.DestructionYear " & _
"FROM tblItem "

strWhere = "WHERE"

strOrder = "ORDER BY tblItem.ItemName;"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.lookup1) Then '<--If the textbox lookup1 contains no data THEN do nothing
strWhere = strWhere & &quot; (tblItem.ItemName) Like '*&quot; & Me.lookup1 & &quot;*' AND&quot; '<--otherwise, apply the LIKE statement to the QueryDef
End If

If Not IsNull(Me.lookup2) Then
strWhere = strWhere & &quot; (tblAccountableItem.SerialFrom) Like '*&quot; & Me.lookup2 & &quot;*' AND&quot;
End If

If Not IsNull(Me.lookup3) Then
strWhere = strWhere & &quot; (tblAccountableItem.Regimental) Like '*&quot; & Me.lookup3 & &quot;*' AND&quot;
End If

If Not IsNull(Me.lookup4) Then
strWhere = strWhere & &quot; (tblAccountableItem.ArchiveFolio) Like '*&quot; & Me.lookup4 & &quot;*' AND&quot;
End If

If Not IsNull(Me.lookup5) Then
strWhere = strWhere & &quot; (tblAccountableItem.DestructionYear) Like '*&quot; & Me.lookup5 & &quot;*' AND&quot;
End If

'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox Me.ListSearchResults.RowSource = strSQL & &quot; &quot; & strWhere & &quot;&quot; & strOrder

End Sub

 

[MinusM]

Is there a space missing after all those ANDS? Or you can make it
Mid(strWhere, 1, Len(strWhere) - 4)

Also make sure listbox.rowsourcetype=&quot;Table/Query&quot;

 

[pd06498]

That did the trick.

My new problem that has arisen is that when I double glick on the record I want to select from the list box to open the main form, I get an error. Basically because I am drawing information from 2 tables, it wont let me open the main form(AccountableItem) with the linked data (ItemID) in full (i.e. full name of Item), and when i change everything to draw from the main table form only, I just get the ID numbers which mean nothing to a user. Is there a solution to this problem?