How do I search 6 fields with one combobox 1

[cheekeemonki]

Any help is greatly appreciated!!!!

I have already created a dialog box that holds the Combobox that contains the list of searchable terms but I am getting stuck on my search/go button. I either need to create a Macro, query or Vb code that will all allow me to search the information in the combobox against the 6 fields in a query.

Any suggestions on the best way to tackle this problem?

Thanks!

 

[ZmrAbdulla]

This is a sample code that searches Employee Table
You can change the SQL to search only those fixed fields

Private Sub cmdSearch_Click()
    Dim dbConnection As ADODB.Connection
    Set dbConnection = CurrentProject.Connection

    Dim rsSearch As ADODB.Recordset
    Set rsSearch = New ADODB.Recordset

    Dim strSearch As String
    strSearch = Me.txtSearch

    Dim strSQLSearch As String
    strSQLSearch = "SELECT * FROM Employees"
    rsSearch.Open strSQLSearch, dbConnection, adOpenKeyset, adLockOptimistic

    Do While Not rsSearch.EOF
        For Each fld In rsSearch.Fields
            If fld.Value = txtSearch Then
                MsgBox rsSearch!EmpID
                Exit Sub
            End If
        Next
        rsSearch.MoveNext
    Loop
End Sub

There is an exit sub on the first find. You can change it search more.

Zameer Abdulla

 

[cheekeemonki]

I am not sure how to apply your suggestions... Here is a copy of the DB Event Search form in question. I have listed the sample fields as Event 1 - Event 6.

The query "EVENT SEARCH" contains combined data from two forms that I would like to search.

In the test db I have two names in the drop down menu... Ron Pressman and Jack Touble... The combobox search should only pull up the records that contain the selected name in any of the 6 event fields.

Thanks again for any help with this issue.

 

[dhookom]

6 event fields

sounds quite un-normalized. Without downloading your file, can you tell us about your application specifically this table?

Are you hoping to use the query as the record source of a form or report?

Duane
Hook'D on Access
MS Access MVP

 

[cheekeemonki]

Duane... It will only be used for mail merges and a quick reference.

 

[dhookom]

If I understand correctly, you could have a simple select query with SQL like:

SELECT *
FROM [YourBaseQuery]
WHERE Field1 = [Forms]![frmYourForm]![cboYourCBO] OR
Field2 = [Forms]![frmYourForm]![cboYourCBO] OR
Field3 = [Forms]![frmYourForm]![cboYourCBO] OR
Field4 = [Forms]![frmYourForm]![cboYourCBO] OR
Field5 = [Forms]![frmYourForm]![cboYourCBO] OR
Field6 = [Forms]![frmYourForm]![cboYourCBO];

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, I wonder if the following works in JetSQL:

SELECT *
FROM [YourBaseQuery]
WHERE [Forms]![frmYourForm]![cboYourCBO] In ([Field1],[Field2],[Field3],[Field4],[Field5],[Field6])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH,
Nice idea. I don't have time to test this at the moment. Hopefully the OP can test and report back.

Duane
Hook'D on Access
MS Access MVP

 

[ZmrAbdulla]

PH,
That is great!!

Zameer Abdulla

 

[cheekeemonki]

Hey Thanks Duane!!! All is up and running

 

[cheekeemonki]

BTW both codes work.... Thanks again!