Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search form problem

Status
Not open for further replies.

Alex85

Programmer
Aug 29, 2003
3
US
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.
 

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

Hope this helps
Hymn
 
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

 
Jimmy,

thanks a lot, it works like a charm!!

Alex
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top