Hi, I must first say that my Access knowledge is very limited. But recently got a job and they need me to make a database in access for leaflets from customers. I got most of it sorted, but the one I am struggling with is generating an ID for each Leaflet, which was requested to be alphanumeric along these lines 'REFA00000'. REF for reference, A for part of the generated ID and same for the numbers. So what they would like to happen when the digits reach A99999 it will go to B00001. Is this possible or will it need to be rethinked?
PF's advice is always good. ...but your post made me laugh as I see this request all the time....particularly having REF in front of each - - what a goofy idea - - might as well put that in a separate field or just use it as part of the label....
my stock reply to these type requests is : if you use Autonumber it will be free - and then give them a higher quote for their typically harebrained numbering system format... - - and they almost always just go with autonumber.....
Agreed. If it is to cross reference to paper work then it should be keyed not generated. But they often do want this sort of stuff. I quite like trying to use the inbuilt tools to spoof what they want (as above) rather than use homebuilt stuff. Apart from being more difficult & expensive it will inevitably be serial too.
The real problem (and I'll bet you've seen this too NTC) is when they start asking about gaps in the numbers....
well i have come across a few problems when I added the query into a few forms, one was a list of the Leaflets and the other was the page where you add a new leaflet. On leaflet list page it would be showing 4 entries, but when i add the query there seems to be the same reference number for the 4 entries, like REFA00001 has 4 entries, REFA00002 has 4 entries etc. But when I remove the query from the form it goes back to normal.
With add new leaflet form, normally it would be blank for the new data, but since adding the query it would show the first record.
Sorry but I am still trying to get to grips with Access, only just started this type of database on Tuesday lol
Right, I managed to convince them that alpha numeric was not needed and a numeric id would be much simplier. I have begun to create the ID but when I create a new leaflet it tells me that it would create a duplicate when finished. I am sure I have done it right but I wanted to make sure with you guys
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Validation code, if any, goes here
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.LeafletID = "00000001"
Me.LeafletID = Format(DMax("Val([LeafletID])", "AI2") + 1, "00000000")
The issue is not actually whether or not it is numeric or alpha or dates or rabbits - it is the choice between:
1) User input
2) System generated (using inbuilt procedures)
3) System generated (developer code)
3) is a nightmare and is what you have written. What I produced was derived from 2) and is far preferable.
What you are producing is what NTC was talking about. It is serial and will be buggy and timeconsuming to code.