Find by mutliple fields in Access/VBA

I have a legacy table where the combination of the Owner Name, Tenant Name, Month Completed and Year Completed are the key field.

How can I search ALL of those fields simulatneously?  

Would docmd.find work?  

Or would I use a recordset that has the same SQL as the form and then iterate through until I find the record and get the Absolute Position and then use docmd.gotorecord AbsolutePosition?

The second method seems somewhat convoluted.

Thanks,
JOe K.
ClaudeWalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
You could use a SQL statement:

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

Open 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).
0
mbizupCommented:
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

Open in new window

0
mbizupCommented:
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 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CodeCruiserCommented:
DoCmd.FindRecord takes multple fields so you can do

DoCmd.FindRecord "OwnerName='" & value & "' AND TenantName='" & value & "'...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"How can I search ALL of those fields simulatneously?  "
Question 1: Against what? Form fields? Prompts?

Question 2:  Using AND or OR logic ?

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
ClaudeWalkerAuthor Commented:
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

Open in new window

0
ClaudeWalkerAuthor Commented:
DoCmd.FindRecord "OwnerName='" & value & "' AND TenantName='" & value & "'...

Does not work because it's saying it's recieving too many arguments.
0
ClaudeWalkerAuthor Commented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Just curious why you are using ADO instead of DAO ?

mx
0
mbizupCommented:
The recordsetclone/bookmark method works for seperate forms - you just need to replave "Me." with the form reference:


dim rs as dao.recrdset
dim strSQL as string

set rs = Forms!YourFormName.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
   Forms!YourFormName.bookmark = rs.bookmark '<---*****
end if  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ClaudeWalkerAuthor Commented:
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.

0
ClaudeWalkerAuthor Commented:
worked perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.