Clear unbound text boxes after running query 1

[amberdextrous]

I posted about this awhile back and didn't get any responses that fixed the problem. I put this project on hiatus for awhile and now I'm back to it, and I'd still like to get this figured out.



There's a screenshot of the form I'm having a problem with. I've got it set up so a user can type in any or all of the information at the bottom of the screen (in unbound text boxes), hit Search, and the records above will be filtered to match the criteria. Then the user can hit "Clear Text" to clear the Search fields out so they can perform a new Search. The problem I have is that the Clear button only works if you hit it after performing a successful search. If your search yields no results, the Clear button acts funky when you click it...it won't actually clear the text until you click in and out of the criteria field to be cleared. So let's say I search for "211" in Client Number and "Dodson" in Project Manager, and no results are found. If I click the Clear button nothing will happen until I click in and out of the Client Number field and Project Manager field. I cannot figure out a way to fix this. Here is my code.

Option Compare Database

Private Sub ClearButton_Click()

Me.EnterClientNumber = Null
Me.EnterClientName = Null
Me.EnterProjectNumber = Null
Me.EnterProjectDescrip = Null
Me.EnterProjectManager = Null
Me.EnterDiscID = Null

EnterClientNumber.SetFocus

End Sub

Private Sub CloseButton_Click()
On Error GoTo Err_CloseButton_Click


    DoCmd.Close

Exit_CloseButton_Click:
    Exit Sub

Err_CloseButton_Click:
    MsgBox Err.Description
    Resume Exit_CloseButton_Click
    
End Sub


Private Sub SearchButton2_Click()

        'Filter data based on search criteria
        Form_ViewDataForm.RecordSource = "SearchQuery2"
        Form_ViewDataForm.Caption = "Search Results"
        
        MsgBox "Results have been filtered."
End Sub

Private Sub ShowAllButton_Click()

Dim LSQL  As String
    
    'Display all customers
    LSQL = "SELECT Client.[Client ID], Project.[Project ID], Disc.[Disc ID], Client.[Client Description], Project.[Project Description], Project.[Project Manager] FROM (Client INNER JOIN Project ON Client.[Client ID]=Project.[Client ID]) INNER JOIN (Disc INNER JOIN [Project On Disc] ON Disc.[Disc ID]=[Project On Disc].[Disc ID]) ON Project.[Project ID]=[Project On Disc].[Project ID] ORDER BY Client.[Client ID], Project.[Project ID], Disc.[Disc ID]"
    
    Form_ViewDataForm.RecordSource = LSQL
    Form_ViewDataForm.Caption = "View All Records"
    
    MsgBox ("All records are now displayed.")
    
End Sub
Private Sub GenerateReportButton_Click()
On Error GoTo Err_GenerateReportButton_Click

    Dim stDocName As String

    stDocName = "rptSearchResults"
    DoCmd.OpenReport stDocName, acPreview

Exit_GenerateReportButton_Click:
    Exit Sub

Err_GenerateReportButton_Click:
    MsgBox Err.Description
    Resume Exit_GenerateReportButton_Click
    
End Sub

If anyone has any ideas of how I can fix this, I'd appreciate the help. It's got to be a problem with either my Clear button or my Search button.

 

[dhookom]

I would use a main form that was unbound and a subform to display the results of the search. I would then dyanmically build a SQL statement and apply it to the Record Source of the subform.

Duane
Hook'D on Access
MS Access MVP

 

[amberdextrous]

Thank you dhookom! I did what you said and now I don't have the error anymore. I moved the search results to a subform and all the buttons and search fields to the main form.