Question Custom auto number

[glb466]

Hello. I have scoured the forum for an answer but have not been able to piece one together. Here is what I am trying to do....

We occassionally have to scrap inventory and in order to be audit complient we have to have a control number and log for each form created. Currently we are doing this manually in Excel but I would like to move it to Access and automate it a bit. I have created 2 tables: 1 for the form info and 1 for the part info. The 2 are linked by a 'SCRAP NUMBER' field. The form has been created and it seems to work. HOWEVER, I need the SCRAP NUMBER to be a number that starts with 'SCRP' followed by a 6 digit number (control #) that will auto assign the next consecutive number to a new record. I also have a field for the requestors initials. I would like to their initials as the end of the number. That way each scrap form will have a unique SCRP number.

I would want it to look like this.... SCRP000017GLB

I originally had the 2 tables use the SCRP number as the primary key and I got it to work but then I read all the posts about not using the primary key for that purpose and to use DMAX... I am not familiar with DMAX. I then added another row to the table for an autonumber primary key.

Thanks in advance!

 

[RuralGuy]

See if this link helps: http://www.baldyweb.com/CustomAutonumber.htm

 

[glb466]

It did help a bit. I think I know what needs to be done i am just not sure how. I can not get the DMax function to work properly.

table: scrap form
this contains the field that I want to automate

Table: Scrap parts
contains the parts and is used as a sub form

Form: Scrap form
this is where I want the auto number to show up.

For something so simple this sure is kicking my A$$.

 

[RuralGuy]

What code are you using?

 

[gemma-the-husky]

don't make the scrp number the PK. Just keep it aa a field on whatever table is appropriate.

you have two ways to generate the scrap number

either lookup the last number used nad add 1 to it (that's dmax)
or store the next number in a separate table, and go from there.

Personally, I would do that. Then you can store the sequence number in your table, and add the SCRP prefix. Actually, personally, I would not store the SCRP prefix, but that's another issue!

 

[glb466]

the code I used was
=DMAX("scrap form","scrp num")+1

 

[RuralGuy]

Because of your embedded spaces in the names you may need to use brackets:
=DMAX("[scrap form]","[scrp num]")+1