Expert: Scottgem - 7/12/2009

Question
QUESTION: Hi,

I'm working on a database. I want to include a field named "ID" that is NOT the primary key and want it to be alphanumeric and 8 digits. The format is "ABC-AB-123". Here ABC are the first three alphabets of another field named "MainIndustry". AB should be picked up from another field named "Priority" and this field already has 2 alphabets only. 123 would be any number incrementing in a sequence and starting from may be 001. Now another complication here is that the numerical value should depend upon the first three letters picked from another field. That is when the first three letters are ABC for example, and the first ID generated is ABC-AA-001, second should be ABC-AA-002 and so on, until the first three letters change. When the first three letters change the numerical value should again be 001 and increase in a sequence. The middle value is just picked from another field and does not need to follow any sequence. Please tell me how this can be done. thankx a bunch in advance.

ANSWER: I've answered several questions that were essentially the same as yours. The first issue is that you do NOT want this as a field in your database. Since you are pulling data from 3 different sources, two of which already exist, there is no reason to store this value. One I will show you is how to create the incremental value and then display the whole thing where needed.

So first you need to add a field to your table, call it Increment that will be a Number (Long Integer) data type. To populate this field, you need to do so immediately before the record is saved. When that will be is dependent on your work flow, so I can't tell you exactly when to do it. Next, you will add a HIDDEN (visible property set to No) textbox to your form and bound to the Increment field. The code you need to generate the next Increment is as follws:

Me.txtIncrement = Nz(DMax("[Increment]","tablename","[Mainindustry] = '" & Me.txtMainIndustry & "'"),0)+1
Me.Dirty = False

This will populate the control named txtIncrement, with the highest value for the Mainindustry contained in the control named txtMainIndustry, incremented by 1. If this is the first record for that industry, then the NZ part of the expression returns a 0 whihc is then incremented by 1. The Me.Dirty line then commits the record. So, obviously, you need to enter the Mainindustry first.

Now to display the ID you would use the following expression:

=Left([Mainindustry],3) & "-" & [Priority] & "-" & Format([Increment],"000")

You can use that expression wherever you need to display this ID. For example as the ControlSource of a control on a form or report. In a query you would add a column with an expression like:
ID: Left([Mainindustry],3) & "-" & [Priority] & "-" & Format([Increment],"000")

Now there is one caveat here. I assume you are storing the Mainindustry name in your data table. But the proper way to do this is to have a lookup table for Main Industry and only store the PK from that table as a foreign key. If that's the case, then you will need to adjust the code accordingly.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: thanks a lottttttttt for your help. I still am a little confused as to where i should insert the code. Im a beginner and am in the learning phase. I have made the "increment" field in the table as you had said. i included a textbox in the form, made it invisible, and gave "increment" as the control source. Now do i have to write the code you mentioned in the MainIndustry event or the Increment event? Access asks to choose a builder from among macro, expression and code. what should i choose? Plus should it be before update event, after update event or something else? Please tell me what to do. The increment field in the table is not showing any value no matter what i do. Where am i going wrong? please guide.

ANSWER: Again, I can't answer a lot of your questions because it deals with YOUR workflow.

I can tell you that the code I gave you is VBA and therefore, entered through the Code Builder. Like I said, you need to do this immediately prior to committing the record. You CAN use the After Update event of the Mainindustry control, but I don't know where in entering the record that is. Do you want generate this number after entering only a few pieces of data or would you want to wait until the user has entered all the data? That's why YOU need to make the decision on when to run the code.

One option is to use a Save button and put the code in there. This way the Increment is not generated until the user elects to save the record.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: HI,
Thanks a lot for your help. I've implemented your solution but it's still not wroking. The increment field is not being populated. What should i do to polulate it? And please tell me do i hav to create the txtIncrement and txtMainIndustry controls? how do i do that? If i have "Textile" in the MainIndustry field and i have "AA" in priority field, then i get the ID generated as "Tex-AA-". No numerical value is generated. When i check the increment field in the table, it is always empty. Why am i getting this result? Please tell me what to do. I've put the code

Me.txtIncrement = Nz(DMax("[Increment]","tablename","[Mainindustry] = '" & Me.txtMainIndustry & "'"),0)+1
Me.Dirty = False

that you had advised, replacing "tablename" with the name of my table, i.e. "Company Information".

Answer
First, you do NOT have fields on your form, you have controls that may or may not be bound to fields in a table. This is a subtle bu important distinction.

Second, I use a naming convention for my controls that uses a 3 character prefixe indicating the type of control. Hence, I would name the control bound to the Increment field as txtIncrement. You need to use the ACTUAL control names in the code. So you have to have a control that is bound to the Increment field in your table and that control is referenced in the left side of the VBA command I gave you. You also have to use the actual names for your other controls in that line of code. So wherever you see Me. you need to use the actual controlname.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA