Going Crazy over my embedded SQL in my VBA in Access 2010 - Please Help

[louvalterry]

I have a Access Application where I am attempting to filter the records by using a control with VBA code. I am however getting the error "Run-time error '13: Type mismatch" when running the control. The line that has the error is bold and red in the sample. If I recreate the SQL as a query there is no problems and it returns the correct rows from the table.

Private Sub cmdSearch_Click()
  
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        'Generate search criteria
        GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
        
        'Filter frmCustomers based on search criteria
              
        Form_Frm_Resource.RecordSource = "SELECT Tbl_Resource* FROM Tbl_Resource WHERE (((cboSearchField.Value) Like " * txtSearchString * "))"
        
        Form_Frm_Resource.Caption = "Resources (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        'Close frmSearch
        DoCmd.Close acForm, "frmSearch"
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

[HiTechCoach]

Try changing this:

Form_Frm_Resource.RecordSource = "SELECT Tbl_Resource* FROM Tbl_Resource WHERE (((cboSearchField.Value) Like " * txtSearchString * "))"

to

Form_Frm_Resource.RecordSource = "SELECT Tbl_Resource.* FROM Tbl_Resource WHERE ((([" & cboSearchField & "]) Like ""*" & txtSearchString & "*""));"

[louvalterry]

HiTechCoach,

First, Thanks for responding to my query. I did not think this was going to be so troublesome when I took on this task.

I cut and pasted your code into mine. When I clik on the filter button which launches the code I get the error message

"Run-time error 424: Object required"

How can I tell what object I am missing? I have read that this may be casues because the Form that the launches my search Form needs something done to it, but I truely do not undersatnd the inforamtion I am getting when I Google the error.

Try changing this:

Form_Frm_Resource.RecordSource = "SELECT Tbl_Resource* FROM Tbl_Resource WHERE (((cboSearchField.Value) Like " * txtSearchString * "))"

to

Form_Frm_Resource.RecordSource = "SELECT Tbl_Resource.* FROM Tbl_Resource WHERE ((([" & cboSearchField & "]) Like ""*" & txtSearchString & "*""));"

[HiTechCoach]

What line us generating the error. It will be the the line in the VBA code that is highlighted when you go into debug mode?

[louvalterry]

HiTechCoach,

The error was coming from the new line of code you suggested. After exploring the parent form and working with some of the VBA code on that Form I got the code to work. So your code worked, but for some VB reason it was encountering an error on another Form and highlighing the code on my pop-up search form.

It is gettting furstrating to learn Access 2010 and VB while working on a real project.

Thanks for the help.

[HiTechCoach]

Glad to hear you are making progress.

So your code worked, but for some VB reason it was encountering an error on another Form and highlighing the code on my pop-up search form.

That is usually how it highlights the error. It is showing you the line of code that was executed last that generated the error as best it can. This is where coding in good error handling becomes important.

The Art of Debugging and the use of error handling is rarely covered in most Access books or training.

This may help: Error Handling and Debugging Tips and Techniques

It is gettting furstrating to learn Access 2010 and VB while working on a real project.

Have you split your database into a front end and back end to make testing a lot easier?

I do ALL my experimenting and testing in a copy of the front end. Once I have it working, I import the changes into the master copy of the front end.