Auto-incrementing fields based on query result 1

[kevink]

Oops ... forgot about posting commercial stuff here! Sorry <br>
<br>
Anyway, back to the problem ...<br>
<br>
I'm trying to get a database up and running which is proving slightly tricky.<br>
<br>
Basically what the database has to have is:<br>
<br>
A Client Ref which is made up of 3 pieces of info.<br>
<br>
1. The first 3 letters of the last name.<br>
2. A 2 digit number - the client number.<br>
3. A 3 digit number - the &quot;file number&quot;.<br>
<br>
The client number must increment by 1 for each new client last name i.e. <br>
<br>
Murphy --&gt; Client ID --&gt; MUR-01 (Only one Murphy)<br>
Lyons --&gt; Client ID --&gt; LYO-01 (Only one Lyons)<br>
Murphy --&gt; Client ID --&gt; MUR-02 (two different Murphys)<br>
Jack --&gt; Client ID --&gt; JAC-01 (Only one JAck)<br>
Murphy --&gt; Client ID --&gt; MUR-03 (three different Murphys)<br>
<br>
The third piece of the Client Ref - the filenumber which will increment by one for each new entry:<br>
<br>
E.g. <br>
Murphy --&gt; MUR-01 --&gt; 1st file --&gt; Client Ref --&gt;MUR-01-001<br>
Murphy --&gt; MUR-01 --&gt; 2nd file --&gt; Client Ref --&gt;MUR-01-002<br>
Murphy --&gt; MUR-02 --&gt; 1st file --&gt; Client Ref --&gt;MUR-02-001<br>
etc etc.<br>
<br>
As well as having the numbers increment automatically (and storing all the Client ID and Client Ref), I have to allow for users to enter previous clients so the starting point for Murphy could MUR-12 etc.<br>
<br>
I'd appreciate any help!<br>
<br>
Thanks,<br>
<br>
Kevin

 

[DougP]

the basics <br>
Left(Name,3) & &quot;-&quot; & clientNum & &quot;-&quot;& FileNumber<br>
you can concatenate together many items<br>
<br>
<br>

 

[DougP]

this code get the last number used and adds one to it<br>
<br>
'open table and get the last RA number used<br>
Dim MyDB As Database, MySet As Recordset<br>
Set MyDB = DBEngine.Workspaces(0).Databases(0)<br>
Set MySet = MyDB.OpenRecordset(&quot;Last-RA-Number&quot<br>
MySet.MoveFirst<br>
MySet.Edit<br>
Temp1 = MySet.Fields(&quot;Last_RA_Number&quot<br>
Temp2 = Temp1 + 1<br>
MySet.Fields(&quot;Last_RA_Number&quot = Temp2<br>
MySet.Update<br>
MySet.Close<br>
MyDB.Close

 

[elizabeth]

Just throwing in my 2 cents re: so-called &quot;smart&quot; numbering sytems. You can end up with unexpected complexities (read &quot;maintenence nightmares&quot later down the line. Users prefer meaningful IDs to autonumbers, because in the past they had to rely on brainpower to do what automation now does for them. You may be required to use a scheme like the one you describe due to an interface with a legacy system, but the people I know who've had to deal with them through multiple years/multiple sytems do anything and everything they can to re-engineer a process to eliminate them.

 

[WP]

Kevin,<br>
<br>
Your client ID is not unique !<br>
<br>
Let's say my name is Murray. What will my client ID be ?<br>
MUR-01 and my first file will be MUR-01-001. The same as Murphy 1's.<br>
<br>
You need a unique client ID ! And this can be the first 3 letters of the surname plus a unique incremented number but, PAT-01 only tells me the cleint is PATON, PATRICK, PATERSON, PATEL, etc I don't see the value of the first three letters and it may only lead to confusion. For example, MUR-03-001 may be assumed as Murphy3's file 1 when in fact it is Murray's file.<br>
<br>
Convince me.<br>
<br>
I have thought about this and started to code the basics.<br>
<br>
Drop me a line and I'll send you the db.<br>
<br>
WP <p>Bill Paton<br><a href=mailto:wpaton@neptune400.co.uk>wpaton@neptune400.co.uk</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

 

[kevink]

Thanks everyone for your posts - they were very helpful. Elizabeth was right when she guessed that it was interfacing with a legacy system!!<br>
<br>
Hopefully I'll crack this nut yet!

 

[elizabeth]

To get past the non-unique ID you'd just have to increment based on the 3-char prefix, not the entire name. <br>
<br>
One way to work with a legacy sytem ID problem is to create your new db with autonumber as the unique key but preserve the legacy system ID in another field called LegacyID. That way you can relate to the legacy sytem, but avoid a lot of design and processing problems. Sometimes your new systems is so great that they expand the legacy sytem to hold your new SystemID! good luck