Search TextBox with option group search group.

[jhallcb]

Hi there. I'm trying to create a search form that has six fields to search. I have created an option group with the 6 fields I would like users to be able to search by. The idea that they select an option, enter the data into the textbox and then hit search and it should display the results below.......

Private Sub Command15_Click()
Dim sOption As String
Select Case Me.Frame16
Case 1
sOption = "Reference Number"
Case 2
sOption = "Pallet / Tray Number"
Case 3
sOption = "Date Logged"
Case 4
sOption = "Week"
Case 5
sOption = "Rack Location"
Case 6
sOption = "Description"
End Select
If IsNull(Text12) = False Then
Me.Recordset.FindFirst "[Frame16]=" & Text12
Me!Text12 = Null
If Me.Recordset.NoMatch Then
MsgBox "No Record Found!", vbOKOnly + vbInformation, "Search Record"
Me!Text12 = Null
End If


End If
End Sub

Problem is that when I hit search it just returns the message box saying "No Records Found" every time. Guessing that it isn't using the option buttons properly somehow but I'm pretty new to vba and I'm struggling to figure out where I'm going wrong. Anyone able to see the problem?

[rpeare]

I don't think you have to get too crazy with this you don't need to pick an option at all especially if most of these are strings

you can just use something like

SELECT * FROM tblTest WHERE [ReferenceNumber] like "*" & forms!formname!text12 & "*" OR [PalletNumber] like "*" & forms!formname!text12 & "*" OR ... and so on

for the date fields you might have to get a little creative in checking to see if the stuff entered in text12 is a valid date but for the most part you don't need the option box at all.

[jhallcb]

Thanks for the reply rpeare. I gave that a shot but it is highlighting the * after SELECT and returning "Expected Case"? Tried rejigging a few things but no joy. When actually clicking the comand button in form view it returns a syntax error.

I was previously using an ApplyFilter macro with: [Reference Number] Like "*" & [Forms]![Search Query]![Text12] & "*" Or and so on. This was working well however I run out of space in the macro to carry this on for all 6 fields of search. Tried to convert this to vba and failed being a rookie. was hoping the option group may make things easier but maybe not.

[rpeare]

I don't use macros, only vba so I don't know that I can help you with macro syntax and I don't use filters either, I build my queries with criteria based on specific selections. If you have a sample database you can share I can look at your form and offer a suggestion but it will likely be at the query level (using criteria) rather than at the report level (filtering).

[jhallcb]

rpeare,
Thank you very much for the reply. I've done away with the option group and gone for a multi-field search with a text box for each field. I went with VBA which is now working well with just a few kinks to iron out.... I'd like it to display a message box when no records are found, doesn't seem to want to play ball without screwing up the rest of the code at the moment but I can fiddle with that. Been a good learning curve with the VBA though, again thanks a lot for the help and advice