Creating an automatic customized reference number (1 Viewer)

[SShafeeq]

Hey,

I am creating a correspondence database, where employees in the organisation keep track of their outgoing mail by entering in details and attaching the copy of the letter.

I have made the Table and a user friendly input form.

Now the primary key of this table is a inhouse reference number which is:

GYY-DD/MM/000, now the G will remain as it throughout all the references, YY DD and MM will change according to the date, 000 will just be an incremental number

For example

G11-02/02/001
G11-02/02/002
G11-02/02/003
G11-03/02/004
G11-03/02/005
G11-05/02/006

Note, the 000 number at the end just keeps incrementing, regardless of the date.

Now I thought about having an input mask on my form, but to be honest I don't know how to make it work.

Any ideas?

Thanks in advance

 

[boblarson]

1. Personally, if I were you, I would use an autonumber as the primary key. You can still have your reference number but manually handling primary keys just is - well - not necessary. The primary key is only really needed for the SYSTEM and therefore the SYSTEM should handle them. (at least that's my opinion, and there are differing opinions about that).

2. You would need to set this using code. If you have not created a function to return the number. Also, your incremental number at the end is going to end at 999 which isn't going to take long to get to. And, why the date doesn't have the year included is baffling me. Just the month and day is possible to repeat if you have to recycle back to 000 when the three digit number ends at 999.

 

[SShafeeq]

Hey Bob,

Thanks for answering. The year is included, where it says GYY (G11), thats the year. The reason why the year is separate is because when the year changes, ie from G11 to G12, the three digit number at the end should revert back to 000.

Also, i get what you are saying about using an autonumber as a primary key, and I could revert back to it, no problem.

However the Ref number in the format i mentioned is necessary as it is quoted on letters and is a way of tracking them.

I am a bit amateur at coding, could you explain to me what vba coding i could use to generate this reference number?

 

[JANR]

See attached mdb

You need a refrence table to hold the current seed both year and current seed number. The function GetRef opens this table and reads the current value and add 1 to it, pass the new refrence back to the caller and update the table.

The trick is to find the right moment to call the function, the best is to call it at the moment of saving a record and after all validation is accepted.

Private Sub Form_BeforeUpdate(Cancel As Integer)
.... validation code
Me.NameOfRefControl = GetRef
End Sub

Be aware that in a multiuser enviroment there is a chance that more users run the function at the same time, to avoid this you can lock the tblRefence in the OpenRecordset statement and release it when you exit the function.

JR

 

[SShafeeq]

Genuis!! Thank you! It works like a charm!

Also in a multiuser environment how do I lock the table?

 

[JANR]

Open access help and do a search on recordset lockedits and you will find som example using the Northwind db

JR

 

[JANR]

Updated mdb to lock table.

JR