looping thru a fields values

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I am not exactly new to Access but I am trying something
that is a little over my head. I have a form where I
input a series of first names in a field called FirstName
seperated by commas (eg Rob, Jim, Susan), and a series of
last names entered in a field called LastName seperated
by commas (eg Guest, Everett, Wills). I want to match the
first, second, etc names in each field and for each pair
insert a record into a different table (eg record 1 - Rob
Guest, record 2 - Jim Everett, record 3 - Susan Wills).
How should I do this? Should I be using an append query,
a macro, or do i need to use VBA to do it. I tried an
append query but could only use left and instr to get the
first name.
 
The simplest way might be to write a piece of code using the Split()
function to generate an array of FirstNames and an array of LastNames. Loop
from LBound to UBound, and AddNew to a Recordset (DAO) opened into the
target table for each one.

Instr() has a Start argument that you can use to tell it to begin looking
after the comma you have already parsed. You could try that in an Append
query, but I suspect it would be more difficult than using Split() in code,
especially if the number of names in different rows varies.

Is there any chance of people being duplicated here? i.e. could one person
be associated with multiple rows? Ultimately, it would seem that you need
some kind of ClientID table with only one record for each person, and the
table you are trying to build would then store the ClientID rather than the
names.
 
Back
Top