Loop through Records and Make ID

[rob4465]

Hi there - I'm doing a database that will need to link with a Paradox database. To accomplish this I need to make a certain field in Access.



I basically have a table that contains a persons first name and surname.

I need to create another field called ID Make that takes the first six characters of the first name, adds in a hyphen and then adds in the first character of the surname.

I can do this easily with a query like:
Left([First Name],6) & "-" & Left([Surname],1)

For example the name William Gates would be turned into:

Willia-G

Now here comes the tricky bit, if there is another William Gates I would get another Willia-G reference but at this point I need it to add a '1' at the end so I would get:

Willia-G1

If there was a third I would need Willia-G2.... and so on.

What I could do with is some kind of script that looks at each record in the table and then makes the appropriate ID.

I'd appreciate very much any help anyone can give me.

At the moment I just have a test table called tbl_test

Field: First Name
Field: Surname
Field: ID Make

Thanks very much -

[RuralGuy]

Now you know why it is not recommended that a name be used as an ID number. Do you need this ID Make value to access the records in the table?

[rob4465]

Thanks RuralGuy - yes, I'm aware of this - the field I'm trying to make will not be used as an ID field but as a way to link to a paradox db.

[RuralGuy]

Your loop will need either a FindFirst on the RecordSetClone or a DLookup() on the current Make ID you are creating to check for duplicates. There may be other ways as well.