Auto number customizing - Please Help me

[mp3nick]

Hi everybody
Any one know how to change the auto number format ? for instance I may want to start from 1000 or 5000, another thing, I got 3 field "MovieID", "SpecifyNumber", "EpisodeID" I want it to automatically put [MovieID]+[specifyNumber] inside the EpisodeID when user enter it.

also I got another Table including "MovieID", "MaterialID", "MaterialFormat" and material format is inclduing one of the VHS,BetaCam,VCD,DVD and I want the MaterialID will be unique by this format
[MovieID]+[FormatAutonumber]

FormatAutonumber is like that for each MaterialFormat it will start from 10000 for VCD
20000 for DVD
30000 for VHS
40000 for BetaCam
it will be like "12-20012" which you will know 12 is movie and 2 at first after dash is the format id DVD and the number will be the index

Please Help me about this.
Any Idea is appreciated

PS: I need some tutorial or resource or book to learn VBA for Access, I already know how to use VBA for excel

 

[gerard1979]

Better don't work with autonumber if u want this.
Take an textField ID, set primary key (no duplicates)
Now, I think u make forms to fill ur database, choose ur fields and set the textfield to the bounded fields when u save the item.
Search for the maxID with Dmax("expressie","table","criteria&quot

Hope this helps,
gerard

 

[uscitizen]

question wrt gerard's solution which uses Dmax(expression,domain,criterion(optional))?

suppose your form is opened by a button on another form (unrelated to it) and the button launches a macro which filters the record's identifier (in this "patientnumber&quot to correspond with the one appearing on the form with the button. suppose that you want to use the Dmax on the 'filtered' records which the user interacts with, do you need to restrict the records using the Dmax criterion or is it assumed that the records on which Dmax will work from are just the 'filtered' records?

 

[MichaelRed]

It will take some effort on your part. See faq700-184 for an example of creating a custom unique value which can be used for a PK. It is not THE thing you want and it is not necessairly an item for the novice to tackle - but it can at least illustrate an approach and may be of use as a starting point / example.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places