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.

Steph
LVL 1
Steph_MAsked:
Who is Participating?
 
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Steph,

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

Jim.
0
 
PatHartmanCommented:
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.
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.

 
Nick67Commented:
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
multiple-JOINS.mdb
0
 
PatHartmanCommented:
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.
0
 
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.
Stephanie
0
 
PatHartmanCommented:
Actually, queries are significantly more efficient than code loops with "findfirst".
0
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.

All Courses

From novice to tech pro — start learning today.