Search form problem

[Alex85]

I'm trying to create a form having in the header section 6 combo boxes containing values to search for (like supplier, subject, category, person...) What I want to do is that I select values from 1 to 6 combo boxes, click n a button and search the database for records matching all these values and display them on a subform. I think I tried all possible ways to do it except the good one!! A little help would be appreciated.

 

[hymn]

have a look at the threads below
Thread702-534577
Thread702-563930

Hope this helps
Hymn

 

[ClydeData]

This Code is based on an unbound mainform with a subform CustomerSelect

place this code behind the search button
'========================
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
Dim field1 As String

ArgCount = 0
MySQL = "SELECT * FROM Customers WHERE "
MyCriteria = ""
field1 = Me![MyFieldChoice1]

AddToWhere [FindFIRM], "[Company]", MyCriteria, ArgCount

AddToWhere [FindTown], "[Town]", MyCriteria, ArgCount


If MyCriteria = "" Then
MyCriteria = "True"
End If
MyRecordSource = MySQL & MyCriteria

Me![CustomerSelect].Form.RecordSource = MyRecordSource

If Me![CustomerSelect].Form.RecordsetClone.RecordCount = 0 Then
MsgBox "No Clients Found", 48, "Customer Search"

Else
End If
'===============================
You will also require the following private sub which is called from the main search button
'==============================
Private Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)
If FieldValue <> &quot;&quot; Then
If ArgCount > 0 Then
MyCriteria = MyCriteria & &quot; and &quot;
End If
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & FieldValue & Chr(42) & Chr(39))
ArgCount = ArgCount + 1
End If
End Sub
'==========================
Create textboxes/combos named FindFIRM and FindTown in the unbound main form these are used for the crieria in the searching

u can have any amount of additional search criteria
adding appropraitly named text/combo boxes and the line of code
AddToWhere [FindMyfield1], &quot;[Myfield1]&quot;, MyCriteria, ArgCount

AddToWhere [FindMyfield2], &quot;[Myfield2]&quot;, MyCriteria, ArgCount

etc
etc


best of luck

Jimmy

 

[Alex85]

Jimmy,

thanks a lot, it works like a charm!!

Alex