natyboi:
Your actual post is extremely hard to follow; however, it sounds like you need a compound lot/serial/sequence number. For simplicities sake, all of these are interchangeable terms.
The topic of compound lot/serial/sequence numbers comes up here quite frequently; thus, a simple search on the site will turn up a ton of solutions.
By way of a general solution, given that your post is quite garbled:
You will need VBA or Macro code (these are DIFFERENT programming methods, you will receive more help via VBA than in Macro) running behind the scenes... usually a command button on click event, or perhaps an on_current event that checks for the new record etc...
then:
What
I consider the simplest in a
properly normalized database is that in the table where this will be stored, there would be one field for the first part of the serial number (say "va" using your example) and a second field that holds the sequence number.
To determine the next sequence, you run a query grouping on the code field ([Municode]) with filter such that the field returns only the code of interest (for example: [Municode]="va") and the max of the sequence number field. In VBA, you then simply increment the number by one or what ever you need to increment by get the next number.
To get your actual "serial number" would be to use a calculated field or control, so that, say in a query, the field named [Serial_Number] would be:
serial_number: [Municode] & "-" & [SequenceNumber]
I typically prefer to have the calculation at the query level instead of the report or form level when possible; however, especially in a form, there are times that this must be done at the form or report levels such as if these are batch numbers generated at report run time (such as a page number would be) that are not necessarily unique in nature..
As I mention before, there over 30 different examples here on this site within the last 6 months with code and/or SQL provided by ADezii, Neopa, Rabbit, and myself.
IF you don't find something that fits, please post back with a clearer explanation and perhaps an example or two.
At This Time, PEASE DO NOT ATTACH: Pictures, textfiles, or any other file. A clear explanation is all that is needed. If you wish to have a formatted table of text, please click on the [CODE/] button and place the text between the inserted tags as they will preserve your formatting.
Most of the experts on this site will not, or in my case, can not open un-solicited attachments or can not see/access them when you use a third party site.
15
