Building SQL Statement

[pd06498]

HELP! This code wont work

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 & " (tblItem.ItemName) Like '*" & Me.lookup1 & "*' AND" '<--otherwise, apply the LIKE statement to the QueryDef
End If

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

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

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

If Not IsNull(Me.lookup5) Then
strWhere = strWhere & " (tblAccountableItem.DestructionYear) Like '*" & Me.lookup5 & "*' AND"
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 & " " & strWhere & "" & strOrder

End Sub

 

[pbaldy]

Is the line setting the rowsource (at the end) really commented out completely like in your post?

 

[pd06498]

The code was a copy from an example database I got from here. The last line from Me. etc is not commented out.

 

[Mile-O]

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

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

 

[jfgambit]

I think the code was from a sample Db I posted. Mile-O is right; you need to strip only 4 characters from the end of the SQL statement.

Also, a suggestion:

1. If Year is a number and must be entered in the YYYY format (i.e. 2003) then you should code the line for that textbox to be:

If Not IsNull(Me.lookup5) Then
strWhere = strWhere & " (tblAccountableItem.DestructionYear) = " & Me.lookup5 & " AND"
End If

I would also convert that textbox to a Combobox that has all the Years in it so that there is no confusion when entering the year. This would be more efficient especially if the column is formatted as a Date in the table.

If you are still having problems zip a copy and sent it to me. I'll have a look at the code.

 

[pd06498]

Thanks jfgambit. The code was from a sample I got from here.

I have the list box being populated now, and have run into another problem.

Using the above code it populates fine, but the next part (the double click) to select a record from the list to open the AccountableItem Form gives an error in that the reference to the field in tblItem is wrong.

When I change the original code to drag all the required data from tblAccountableItem table (which includes the link to tblItem.ItemName) it displays the autonumber for that field (which will mean nothing to the end user), but allows the right form to be opened when double clicked.

Any ideas?

 

[jfgambit]

Is the Bound column of the listbox set to the AutoNumber field?

What is the code you have on the DoubleClick Event of the listbox?

 

[pd06498]

The same as the one used in your example, with the relevant changes to reflect my database. If you like I can send you the entire db for a peek!!!

 

[jfgambit]

yea...go ahead...make sure and zip it