Clear Form and Query results (1 Viewer)

[testdba]

I'm having trouble clearing a form and the query results at the same time. I have a list box that is populated by a query that gathers search criteria from user input in unbound textboxes. If the user types in criteria, then that is used in the query. If they just click search, then all results are returned. The problem is that I want to add a button to reset the form and clear the results.

In order to clear the form, I modified a bit of code that I have used before (I think I found it here somewhere ). The code clears the form, but when I click search, I get whatever results were previously returned instead of getting all results or results based on the current selection criteria. Does anyone have any suggestions?

Here is the code to clear the form:

Private Sub cmdClear_Click()
    Dim I As Integer
    
    ' Cycle through the form's controls, testing for text,
    ' and clear each field.
    For I = 0 To Me.Count - 1
    If TypeOf Me(I) Is TextBox Then
      Me(I) = ""
    ElseIf TypeOf Me(I) Is ComboBox Then
      Me(I) = ""
    End If
    Next

End Sub

And this code executes the query:

Private Sub cmdSearch_Click()

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String

'Constant Select statement for the RowSource
strSQL = "SELECT Orders.PONumber, Orders.CustomerID, Orders.InstallerID, Orders.PaidInstaller, Orders.POType, Orders.Status, Orders.OrderDate, Orders.ScheduledDate, Orders.CompletedDate, Orders.MeasuredLength " & _
"FROM Orders"

strWhere = "WHERE"

strOrder = "ORDER BY Orders.PONumber;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.PONumber) Then
    strWhere = strWhere & " (Orders.PONumber) Like '*" & Me.PONumber & "*'  AND" '
End If

If Not IsNull(Me.CustNumber) Then
    strWhere = strWhere & " (Orders.CustomerID) Like '*" & Me.CustNumber & "*'  AND"
End If

If Not IsNull(Me.POType) Then
    strWhere = strWhere & " (Orders.POType) Like '*" & Me.POType & "*'  AND"
End If

If Not IsNull(Me.Status) Then
    strWhere = strWhere & " (Orders.Status) Like '*" & Me.Status & "*'  AND"
End If

If Not IsNull(Me.InstallerID) Then
    strWhere = strWhere & " (Orders.InstallerID) Like '*" & Me.InstallerID & "*'  AND"
End If

If Not IsNull(Me.PaidInstaller) Then
    strWhere = strWhere & " (Orders.PaidInstaller) Like '*" & Me.PaidInstaller & "*'  AND"
End If

If Not IsNull(Me.OrderDate) Then
    strWhere = strWhere & " (Orders.OrderDate) Like '*" & Me.OrderDate & "*'  AND"
End If

If Not IsNull(Me.ScheduledDate) Then
    strWhere = strWhere & " (Orders.ScheduledDate) Like '*" & Me.ScheduledDate & "*'  AND"
End If

If Not IsNull(Me.CompletedDate) Then
    strWhere = strWhere & " (Orders.CompletedDate) Like '*" & Me.CompletedDate & "*'  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.lstPOInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

 

[modest]

First of all, I'm surprised there's not a syntax error when you run the search when all the textboxes are blank. You include the WHERE clause even when there is no condition -- this surprises me that it will work without any conditions?

Second, if I understand you correctly. When you click the "Search" button a second time, it returns the same results no matter what. I believe this is because you are not recalculating the data.

Include at the end of the sub:
Me.lstPOInfo.Requery

 

[testdba]

First of all, I'm surprised there's not a syntax error when you run the search when all the textboxes are blank. You include the WHERE clause even when there is no condition -- this surprises me that it will work without any conditions?

Second, if I understand you correctly. When you click the "Search" button a second time, it returns the same results no matter what. I believe this is because you are not recalculating the data.

Include at the end of the sub:
Me.lstPOInfo.Requery

Yep, it runs without anything in the text boxes.

Thanks for the requery line. I didn't even think about that. It worked though!