Search form and missing reference

[hockeyfan21]

Hi,

I have been using the below that I found on this site years ago on a search form. I recently pulled it into a 2007 db and it will not work. It works in 2010 but, when I imported the form and code into a 2007 db, the reference to "Microsoft Forms 2.0 Object Library" gets removed. I found it (FM20.dll) and brought it back in on my 2007 machine but it still does not work. No error message, just no results.

My form has 4 text boxes that I can search on. Entering any search criteria gets me no results. If I leave the search fields blank and just hit cmdSearch, all of my records appear in the listbox.

Any ideas as to why this would be happening?

Thanks as always!

Toni


Private Sub cmdSearch_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 [Projects].[ID], [Projects].[Project Name], [Projects].[Owner], [Projects].[Status],[Projects].[Plant] FROM Projects"

strWhere = "WHERE"
strOrder = "ORDER BY Projects.[Project Name];"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtProject) Then '<--If the textbox contains no data THEN do nothing
strWhere = strWhere & " (Projects.[Project Name]) Like '*" & Me.txtProject & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
If Not IsNull(Me.txtPlant) Then
strWhere = strWhere & " (Projects.Plant) Like '*" & Me.txtPlant & "*' AND"
End If
If Not IsNull(Me.txtOwner) Then
strWhere = strWhere & " (Projects.Owner) Like '*" & Me.txtOwner & "*' AND"
End If
If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (Projects.Status) Like '*" & Me.txtStatus & "*' AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the SQL to the RowSource of the listbox
Me.lstProjectInfo.RowSource = strSQL & " " & strWhere & "" & strOrder
End Sub
Private Sub Form_Load()
lstProjectInfo.RowSource = ""
End Sub

 

[VilaRestal]

I see nothing in that code that would require the Microsoft Forms 2.0 Object Library. If they're all standard Access textboxes and listbox then it certainly doesn't need it.

You can happily get rid of:
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

They're not used.

In fact I would rewrite the code like this:

Private Sub Form_Load()
    lstProjectInfo.RowSource = ""
End Sub

Private Sub cmdSearch_Click()
    Dim strSQL As String, strOrder As String, strWhere As String
    'Constant Select statement for the RowSource

    strSQL = "SELECT ID, [Project Name], Owner, Status, Plant FROM Projects "
    strOrder = " ORDER BY [Project Name];"

    'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
    If Nz(Me.txtProject, "") <> "" Then strWhere = "[Project Name] Like '*" & Me.txtProject & "*' AND "
    If Nz(Me.txtPlant, "") <> "" Then strWhere = strWhere & "Plant Like '*" & Me.txtPlant & "*' AND "
    If Nz(Me.txtOwner,"") <> "" Then strWhere = strWhere & "Owner Like '*" & Me.txtOwner & "*' AND "
    If Nz(Me.txtStatus, "") <> "" Then strWhere = strWhere & "Status Like '*" & Me.txtStatus & "*' AND "

    'If any wheres, add WHERE to the beginning and remove the last AND from the SQL statement:
    If Len(strWhere) > 0 Then strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 4)
    'Pass the SQL to the RowSource of the listbox
    Me.lstProjectInfo.RowSource = strSQL & strWhere & strOrder
End Sub

Because I find that easier to read and because it handles the situation where there are no criteria.

Your original code was stripping five characters from the end of strWhere, which would have include the "' " as well as "AND"

 

[hockeyfan21]

Thanks so much for the revamped VBA, appreciate it! However, I'm having trouble getting it to work on my 2007 db. I built it as a 2007 accdb on a 2010 machine. It works if I open and run it on my 2010 machine but not on my 2007 one. Same thing, if I hit Search with no criteria, it will fill the listbox with all of my projects, with criteria, just the headers appear. I have never experienced this before so don't know what the issue could be.

Thanks so much!

Toni