AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
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:
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:
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
