auto generated unique reference ID

[bobc]

Hi, I need help with Microsoft Access 2003.
In one of the fields of a table I need to auto generate a unique reference ID using a combination of letters, numbers and information from another field. For example: ABC-DE000-FG-00/00/00 (auto number) (information from other field (Date)).
I can do the first bit (ABC-DE001-FG-) but I’m unable to find a way to get the information from another field (Date) to the end of this auto generated unique reference ID.
Please help
Ps simple terms please J

 

[jdraw]

And why exactly do you need this customized code?
Access can use an autonumber field to uniquely identify a record.An autonumber field is used by Access to identify records. It is not intended to be meaningful to a user.

What is the importance of this concocted code to you and the application?

 

[bobc]

It is for a incident reporting system. A unique reference ID that can be given to the reporter that can be linked back.
can this be done? or is there another way?

 

[jdraw]

If the Reference number is for use by People, you can have a number field that you can control through code. That number can be an Alternate key and would NOT be the PK.
If you are relating this table to other Tables, I would recommend using an autonumber field as PK that is totally controlled and used by Access.

When you create a new record, Access would control the PK. It would be your application that would increment/assign a new ReferenceId to the newly created record. The ReferenceId, could be any combination of characters you want, BUT it would NOT have an autonumber component.

 

[John Big Booty]

The attached sample demonstrates the sort of thing that jdraw is talking about.

It uses the DMax() function plus one, to increment an order number based on the date and the office from which the order originated. You will note that the raw order number is stored but through formatting is displayed to indicate the office, the date and the order number.

 

[bobc]

Ok that’s cool, I don’t know how to do the code for this.
I need to have the date (from the date column) to be deployed at the end of the unique reference ID (ABC-DE000-FG-DATE)
Please help my boss is breathing down my kneck for this to be done and im really stuck.

 

[John Big Booty]

Sorry my bad the field that is displaying the Order number is actually an unbound text box and uses the following as it's Control Source;

="C" & Left(Combo4.column(1),1) & Format([OrderDate],"mmyy") & Format([OrderNum],"0#")

You should be able to modify that to suit your own requirements.

 

[bobc]

Thanks John
Still cant get it perfect and its not exactly what I needed but it will sorter suit. I needed it to be next to the record on the table.
Thanks for all your help guys in a real novice J