Find by mutliple fields in Access/VBA

You could use a SQL statement:

SELECT * FROM YourTable
WHERE
[Owner Name]= "Something", [Tenant Name] = "SomethingElse", [Month Completed] = "February", [Year Completed] = 2010

Select allOpen in new window

The exact syntax depends on the actual field types, how you are defining the criteria (for example user input from a form, and how you are using the results (such as limiting the records shown in a form or report).

To goto a specific record on a form, you can use bookmarks:

dim rs as dao.recrdset
dim strSQL as string

set rs = me.recordsetclone
strSQL = "[Owner Name]= 'Something', [Tenant Name] = 'SomethingElse', [Month Completed] = 'February', [Year Completed] = 2010"

rs.findfirst strSQL
If rs.nomatch = true then
    msgbox "No Match Found"
Else
   me.bookmark = rs.bookmark
end if

Select allOpen in new window

Sorry - my syntax is off even for example purposes.  For criteria using multiple fields, use AND, not commas:

dim rs as dao.recrdset
dim strSQL as string

set rs = me.recordsetclone
strSQL = "[Owner Name]= 'Something' AND [Tenant Name] = 'SomethingElse' AND [Month Completed] = 'February' AND [Year Completed] = 2010"

rs.findfirst strSQL
If rs.nomatch = true then
    msgbox "No Match Found"
Else
   me.bookmark = rs.bookmark
end if 

Select allOpen in new window

DoCmd.FindRecord takes multple fields so you can do

DoCmd.FindRecord "OwnerName='" & value & "' AND TenantName='" & value & "'...

"How can I search ALL of those fields simulatneously?  "
Question 1: Against what? Form fields? Prompts?

Question 2:  Using AND or OR logic ?

mx

With prompts, using  AND logic:

SELECT TableA.*
FROM TableA
WHERE (((TableA.[Owner Name])=[Enter Owner]) AND ((TableA.[Tenant Name])=[Enter Tenant]) AND ((TableA.[Month Completed])=[Enter Completed ]) AND ((TableA.[Year Completed])=[Enter Year Completed]));

Using OR logic

SELECT TableA.*
FROM TableA
WHERE (((TableA.[Owner Name])=[Enter Owner])) OR (((TableA.[Tenant Name])=[Enter Tenant])) OR (((TableA.[Month Completed])=[Enter Completed ])) OR (((TableA.[Year Completed])=[Enter Year Completed]));

The Prompts could easily be replaced with references to Form text boxes:

Instead of [Enter Tenant] >> Forms!YourFormName!txtTenant

mx

Yes but how do I navigate to a record on a seperate form?  

this is my current method but it only works for two conditions.  I need to update those conditions to for conditions which makes this method very convoluted.

Basically is opens a recordset, creates a counter, iterates through the record set incrementing the counter.  When it finds the result it sets the index and then I use Docmd.Gotorecord, acGoTo

'opens form, goes to job id, finds the corresponding clicked job id and closes the find form
    DoCmd.OpenForm "frmPreviouslyWX", acNormal
    'DoCmd.FindRecord
    Dim cn As New ADODB.Connection
    Set cn = CurrentProject.Connection
    
    Dim rs As New ADODB.Recordset
    rs.ActiveConnection = cn
    rs.Open "SELECT PreviouslyWeatherized.Owner, PreviouslyWeatherized.Tenant, PreviouslyWeatherized.StreetNumber " & _
            "FROM PreviouslyWeatherized " & _
            "ORDER BY PreviouslyWeatherized.MonthCompleted;", , adOpenStatic, adLockOptimistic
    rs.MoveFirst
    Dim ctr As Long
    ctr = 1
    Dim recordPlace As Long
    While Not rs.eof
        If IsNull(rs.Fields(1) = Me.lstPrevWX_Results.Column(2)) Then
            If rs.Fields(0).Value = Me.lstPrevWX_Results.Column(1) And rs.Fields(2).Value = Me.lstPrevWX_Results.Column(3) Then
                recordPlace = ctr
            End If
        ElseIf rs.Fields(0).Value = Me.lstPrevWX_Results.Column(1) And rs.Fields(1) = Me.lstPrevWX_Results.Column(2) Then
                recordPlace = ctr
        End If
        
        ctr = ctr + 1
        rs.MoveNext
    Wend
    
    DoCmd.OpenForm "frmPreviouslyWX", acNormal
    DoCmd.GoToRecord acDataForm, "frmPreviouslyWX", acGoTo, recordPlace
    DoCmd.Close acForm, "frmFind", acSaveYes

Select allOpen in new window

DoCmd.FindRecord "OwnerName='" & value & "' AND TenantName='" & value & "'...

Does not work because it's saying it's recieving too many arguments.

This line throws an error with multiple criteria (however it works with only 1):

    rs.Find "owner = '" & Me.lstPrevWX_Results.Column(1) & "' AND tenant = '" & Me.lstPrevWX_Results.Column(2) & "'"


Dim rs As New ADODB.Recordset
   
    rs.Open "SELECT * " & _
            "FROM PreviouslyWeatherized p " & _
            "ORDER BY p.monthcompleted", CurrentProject.Connection, adOpenStatic, adLockOptimistic
   
    rs.MoveFirst
   
    rs.Find "owner = '" & Me.lstPrevWX_Results.Column(1) & "' AND tenant = '" & Me.lstPrevWX_Results.Column(2) & "'" // ERROR ARGUMENTS ARE OF WRONG TYPE

Just curious why you are using ADO instead of DAO ?

mx

Well this explains why it won't handle multiple arguements:  

From help file:  Only a single-column name may be specified in criteria. This method does not support multi-column searches.

worked perfect!