MS Access record set statement

Hello Experts,

Can you please tell me how to revise the statement below so I can make it match on 3 or 4 fields?

The current statement is:

rs.FindFirst field1= & rs2!field1

Basically I'm trying to compare two tables and there isn't a primary field to identify the record, so I need to combine  multiple fields to make a primary field.

Of course, the easiest way to  do this would be to use Office 365 and just dump the tables into Excel and do a compare, but  I only have Office 2007 so I thought I'd just run a subroutine.

Thank you.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  rs.FindFirst field1 = "some value" AND field2 = "some value"

Rey Obrero (Capricorn1)Commented:
assuming all the fields are Number type of data

rs.FindFirst "[field1]= " & rs2!field1 & " And [Field2]= " & rs2!field2 & " And [Field3]= " & rs2!field3

if  [field2] is Text, use

rs.FindFirst "[field1]= " & rs2!field1 & " And [Field2]= '" & rs2!field2 & "' And [Field3]= " & rs2!field3

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
Actually, the easiest way is to build a query.  You can join on multiple columns.  It will be more efficient than using find to move around the recordset.  Don't forget that FindFirst always goes back to the beginning before searching again.  Of course, if the recordset is only a few hundred rows, it won't matter much but once you get into the thousands, you will see a difference.

Or, if you are a programmer, you could sort the two recordsets and then do a two file match.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Can you please tell me how to revise the statement below so I can make it match on 3 or 4 fields?
All sorts of things suggest themselves, but you're a little sparse on detail.

and there isn't a primary field to identify the record, so I need to combine  multiple fields to make a primary field.
 Access is thoroughly capable of a multi-column primary key, but I don't think that does you any good -- although I could be wrong, I always use auto-numbers for PKs.

@PatHartman is right about the query, too.  You can create a query that has multiple JOINs between two tables.  Just because you normally only see one doesn't mean you can't drag-and-create more.  That can be really handy as JOINs are more efficient that WHERE clauses in SQL.

Sample attached
I also normally use autnumbers as primary keys.  HOWEVER, to implement business rules of uniqueness, you MUST define multi-field unique indexes.  Most Access apps do not have data sets large enough to worry about the "efficiency" of single field surrogate keys vs multi-field natural keys so don't even worry about that.  Just use the autonumber PK and make a separate unique index for your business rules.  The single field PK is much more convenient when building queries but the thing that convinced me is when I tried to create a usable combobox on a multi-field PK.  It can't be done due to the way combos work.
Steph_MAuthor Commented:
This works perfect what I need to do.  I need to run the results through some if statements after it matches so this is the most efficient way for this task.

Thank you.
Actually, queries are significantly more efficient than code loops with "findfirst".
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.