Type mismatch from search field

[gemadan96]

I have converted an embedded macro to VBA. The macro worked with no problem. Now that it is converted to VBA when I click the button it displays a message box that just says Type mismatch.



The VBA code is as follows:

'------------------------------------------------------------
' Search_Click
'
'------------------------------------------------------------
Private Sub Search_Click()
On Error GoTo Search_Click_Err


TempVars.Add "strSearch", Replace(Forms!ViewStudyList!SearchBox, """", """""")
TempVars.Add "strFilter", "([PATIENT] Like "" * " & [TempVars]![strSearch] & " * "" )"
TempVars.Add "strFilter", TempVars!strFilter & " OR ([MRN] Like "" * " & [TempVars]![strSearch] & " * "" )"
TempVars.Add "strFilter", TempVars!strFilter & " OR ([STUDY] Like "" * " & [TempVars]![strSearch] & " * "" )"
DoCmd.OpenForm "StudyList", acNormal, "", TempVars!strFilter, , acNormal
TempVars.Remove TempVars!strFilter
TempVars.Remove "strSearch"
DoCmd.Close acForm, "ViewStudyList"


Search_Click_Exit:
Exit Sub


Search_Click_Err:
MsgBox Error$
Resume Search_Click_Exit


End Sub

The command is supposed to execute a search of the PATIENT, MRN, and STUDYID fields that match whatever is entered in the search box.

Any suggestions are appreciated.

[rpeare]

Any reason your form/report doesn't just use your search boxes as criteria rather than using a filter?

Have you tried using the debug.print statement to see if there are any errors in your code?

for instance debug.print strsearch would show you what your search text is
debug.print strfilter would show you what your filter statement actually says

Are you actually typing "" marks into your search box, just curious about that replace statement

Is there a reason you're using the form viewstudylist only as your prompt for what to find? You can perform that same functionality on your form studylist without having to use the intermediary form.

[gemadan96]

The search macro was borrowed from another database to be able to search through multiple fields. The replace function is in there to handle apostrophes. The view study list form allows the user to select what they want to see from the study list. the study list is actually a SQL Server linked table with over 100,000 records. The users won't typically have a need to view the entire list.

[rpeare]

My point is that on the form you can have it come up blank, in other words no record showing. A record would only show after they select items from your combo boxes. There's really no need to have a form who's only function is to set lookup criteria.