Generating ID using letters from related tables and sequential numbers

[simbamfalme]

Hi Guys. I am preparing a database that stores the records of runners such as their results, the runs they take part in, the locations where the runs take part, etc. One of the requirements is to have a the RunID as the first 3 letters of the LocationName, and then a sequential number which is higher than the one currently in use.

For example, if there is a run at location Liverpool, and the current sequential number is 15, the new RunId should be liv16

I have managed to use this:
=Left([LocationName],3) & DMax("Right([Run]![RunID],2)","Run")+1

However, the problem that i am having at this point is if the sequential number is a one digit number, such as 2, and the latest RunID is wes01, then the new RunID for location Liverpool returns Liv2, intead of Liv02, which is a problem since the next RunId cannot be generated, as it tries to add v2 +1 instead of 02+1 as my formula checks the last 2 characters of the previous RunID. A similar error would occur if the sequential number reaches more than 99, if you understand what i mean.

I need a way to get around this. Please guys help me out.

Thanks

 

[MStef]

You have to know how many records you look forward,
and therefore to adapt VBA (Right and Format).
Look at "DemoNewIDA2003.mdb" (attachment, zip).
Open Form and try.

 

[Addyman]

Rather than trying to calculate the next sequential number from previous values, in situations like this I like to have a separate table which holds the sequential number.

So, I might have tbl_Sequential with a number field which holds the sequential number (call it SeqNo for now).

This holds the next available number to use. So you would set it to 16 for now (from example in your post).

Then, your next RunID would be:

=Left([LocationName],3) & DLookup("SeqNo","tbl_Sequential")

You then need to update the value of the Sequential Number:

Dim str as SQL

strSQL = "UPDATE tbl_Sequential SET SeqNo = SeqNo + 1"

CurrentDb.Execute(strSQL)

 

[simbamfalme]

is there a way of doing it from the method i sstated above...using left() and DMax() functions. wat about using instr()????

 

[jdraw]

Why do you need to codify the RunId and Run Location into a single field?

Why not have a field for RunLocation, and a unique identifier/number for the runId?
If you record RunDate, you can use queries to Count RunId by RunLocation etc.

One field, one fact.
Why make it more complex than necessary?

You might wish to read the RDBMS Principles attached to the post at

http://forums.aspfree.com/microsoft-access-help-18/relational-database-principles-208217.html

 

[simbamfalme]

i have a field for the run location. however, a requirement is that the runId must contain the first 3 letters from the location, followed by a sequential number. as it is i have a text file containing older data that followed this system, and need to create a newer system to add more records, hence i am restricted to the format above.

 

[rodmc]

create a seperate field for the letters and the create a composite key from both fields

 

[simbamfalme]

thanks for the help guys but i am limited to running the process without adding any new fields or tables...i know this may sound weird and i knwo it is. ANyway thanks for helping. more solutions are always welcome