Automatically create next ID for new records??

[btj]

Is there a way that I can automatically generate an ID when "Add New" button is clicked? The twist for this problem is that there are multiple categories and the abbreviation for each category is part of the ID.

Example
Current record:
Category: MAINT (this is a combo box which lists both ID and Name, displayed as MAINT - Maintenance)
ID: MAINT001

So, if a user clicks "Add New" then a blank form would be
generated (this is not a problem). Then, when they select a category (ex: MAINT, which is Maintenance) there would be code which looks up the next available number (ID) within the MAINT set.

New record:
Category: MAINT
ID: MAINT002

Please let me know if you can help. If I have explained this poorly, let me know and I will try and clarify.

In advance, thank you.

 

[SA812]

I'd use independent forms and apply a different mask on the indiviadual forms. Set up auto number in the database. Depending on which for is opened the auto number will be formated by the mask in the forms.

There could be an easier way but i'm no coder "just a hack"
SA

 

[btj]

That is a good idea. Unfortunately, I am unsure if my client would accept that as a solution.

Part of the job is to take existing records (with pre-established IDs) and import them into the database I am developing.

So, I am not sure how that could work...but if you see something I don't, please let me know.

 

[Ricky1946]

btj,
Can you use a Total Query to Count the number of records. Then you can easily format a text box (in the properties box Control Source using SQL) to add on 101 or similar to give you a regular serial number then the 'handle' can be added on at the last stage before displaying on the form.
Each time you exit the form the records would automatically update and on opening the Count would return the new amount of records and so the process goes on.
Let me know if this does not make sense and I will try to explain further.
Regards
Ian "To say 'thankyou' encourages others."

 

[MichaelRed]

Review faq700-184 It was originally designed for Ms. A '97, and modified to be (somewhat) less specific to the original situation. It would need to be modified to have additional fields in the 'next number' (QI?) table, but it is a 'proven' approach which works with multiuser apps.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[JimHorton]

btj,
Here's a way. First, I highly recommend you not use autonumber. But for sake of ease of explanation, we'll employ it here. Make a field with autonumber, call it "hiddenID".

IN the addnew button, it should look like:
Dim strCat
strCat = InputBox("enter cat&quot 'upon push of button, user is prompted for category
DoCmd.GoToRecord , , acNewRec
Me.Cat = strCat
Form_beforeInsert 'must force this event

Now...in the BeforeInsert:

me.MyNewID = me.cat & me.hiddenid

This should work. I recommend you use a homegrown autonumber, though. Also you could use a Form in Dialog mode with a combo box using only the available Categories instead of InputBox, which is very spartan.

--Jim

 

[btj]

Thank you for all of the ideas. I had an idea last night and wanted to run it past you. If you have a moment I would value your input.

Idea:
As mentioned, I have two fields that are critical for this task - Category (consisting of CategoryID and Category) and tcID (formatted as "TC-FW001", but data is just FW001).

So, as a user adds or replicates a record, the cursor goes to Category field. On Category_AfterUpdate, code will be run that will do the following (not exact code):
If Me.tcCategory = "FW" Then
Me.tcID = DMax("tcID", "[table/query]","[search cond.]&quot+1
ElseIf Me.tcCategory = "EMS"... continue code for rest of Category IDs

The basic premise is to use the Category as the springboard to automatically populating the ID field.

Two things I am unsure about -
1) Will this work (i.e. is it a sound concept)?
2) Should I create a query that will be run for each CategoryID to list the numbers associated with it? Example - if Category "FW" has 2 records (FW001 and FW002) a query would list those 2 records and then the DMax function would identify FW002 and then add 1.

Please let me know what you think...

- Ben

 

[SA812]

I'm a little lost i have a multi user database that assigns an autonumber to the form when it is open. I have set the forms to recored level locking. Are you telling me i shouldn't be using an autonumber, but instead should be using above mentioned way?

 

[JimHorton]

Ben,
First, I have reservations about the theme in general, but to put those aside for a bit...

In the code you have in AfterUpdate, you don't need an If/Else--you can just us a variable for the Dmax criteriea (that is, if the table in dmax has the category--which I assume it must), ie the dmax would be

Me.tcID = DMax("tcID", "[table/query]","Category = '" & me.TcCategory & "'&quot+1

Note that in multiuser settings there is the chance that 2 users getting the number at the same time (and this window can be large--if you sit on a record for 15 minutes before you save it--anyone within that 15 minutes who inserts a new record in same category will get the same result from dmax--and one of you will get the "Duplicate reocrd" error--whomever saves last.

Now, in general I tend not to go with "intelligent" keys or compound keys. From a purely logical standpoint, you don't need it. Yes, it may seem easier to have the category as part of the key, but in my opinion, if the category is a foreign key, then you always have access to it if you have the key to the record, so it's really kind of redundant to do this. But that's just my opinion. Other disadvantages are the discussion we're having now--it's a spahgetti-coding mess when you're dealing with input masks, formatting, data that looks like one thing but underlying is another, etc, etc.

Any reporting, display or whatever can always show the category, you can even put it next to the "id" to mimic this compound key.

Sa812,
My reservations about autonumber are *not* that the above is favorable--it's that with *any* numbering scheme, whether it's a clean ID# or some compound key, the Autonumber is a horrible way to go, again, my opinion.

One reason is that you lose control of the seeding--ie where you start the numbering. Another is that when you have related records in other tables--ie other tables with a foreign key that is in a number field, but whose 'parent' was autonumber--if you do any importing or replacing of that data into the table with Autonumber, the integrity is gone--autonumber assigns the parent new id's, which no longer match the existing foreign keys. Other reasons exist, but those are 2 big ones.

--Jim