Generate product codes on the fly using DMAX or Count.

[Richard Horne]

Hello all,

I'm trying to develop a product code generator for our stock database. The
format of our product code will be as follows:

AB-CC-DDDD-EE-FFF (all numerics)

The numbers that make up A-E are all generated from values selected in
various combo boxes on my product entry form. I've got these parts working
fine and have created a form that generate a partial product code by
concatenating all the requisite parts together.

The problem I now have is that I want to automatically generate the last
three digits (FFF) based on the number of records saved against AB-CC-DDD-EE.

Let me explain this a bit better. An example of a partial product could be
12-34-5678-91 - note FFF is missing.

And I could have multiple items with that that partial product code. FFF is
what will differentiate between them.

And while the values that can be selected for A-E are all already
predefined, F will be a freeform text box called design. So the form will
need to count how many records in the system have the partial product code
12-34-5678-91- in our given example - and then automatically assign the next
number in the sequence, or 001 if there are none.

But how do I go about doing this live count instantly on a form? The
research I've done suggests I might need to use the DMAX function but while I
can easily do this in PHP, my VBA skills are not quite upto scratch and I'm
unfamiliar with the best practises in terms of form coding.

Thanks in advance.

 

[BruceM via AccessMonster.com]

With a single prefix number your code could be something like this (air code):


Dim lngE as Long, lngF as Long

lngE = Me.E

lngF = Nz(DMax("[F]","[TableName]","[E] = " & lngE),0) + 1

Me.F = lngF

I would try that to get started. To add other parts of the product code:

Dim lngD as Long, lngE as Long, lngF as Long
Dim strWhere as String

lngD = Me.D
lngE = Me.E
strWhere = "[D] = " & lngD & " AND [E] = " & lngE

lngF = Nz(DMax("[F]","[TableName]",strWhere),0) + 1

Me.F = lngF


In the second example I used a string variable for the DMax Where condition,
as I often find it clearer to build such a string outside of the DMax
function.

The above code assumes A, B, C, D, E, and F are fields in the form's
underlying Record Source. For display purposes you would need to use the
Format function to display leading zeros. If the form is based on a query
you can do something like this as a calculated field:

FullNumber: [A] & & Format([C],"\-00") & Format([D],"\-0000") & _
Format([C],"/-00") & Format([D],"/-00") & Format([F],"/-
000")

This assumes that any element with two or more characters could have leading
zeros. It would work even if that is not so, but in that case you could
simplify a little. Let's say C is always two characters, and the first one
is never 0:

FullNumber: [A] & & "-" & [C] & Format([D],"\-0000") & _
Format([C],"\-00") & Format([D],"\-00") & Format([F],"\-
000")

The backslash in the format function tells Access to treat the next character
as literal. It may work if you leave out the backslash, but I prefer to be
specific.

 

[BruceM via AccessMonster.com]

In the last post, note that the underscore in the query expression is
intended for clarity in this posting (I see the line wrap has a mind of its
own anyhow), but leave it out of the expression. It is a line continuation
character in VBA only, not in Access SQL.