Auto increment number based off of other values

[3acks]

I've read a lot of articles and forums trying to figure this one and have yet to come up with a solution I can get to work. I need to create a unique id. I can do this using Dmax to find the largest value and add 1. The problem is that I need the unique ID to be based on the value of two other fields. So, in table of hundreds of records I want to look at only certain values to obtain the next available number. For example, if I have a table of orders and want the order number to be unique based off the client name and the product type.

So if Dmax is stated like this:

DMax ( expression, domain, [criteria]

and I have multiple criteria, how does that work??

I have a form where I would first have them select the client and then the product and then want the next available order number to pop up.

[Rabbit]

"Criteria1 AND Criteria2"

[puppydogbuddy]

Private Sub Form_BeforeUpdate()
Dim sOrderID As String
Dim iOrderSeq As Integer
Dim sCustName As String
Dim sProdType As String
 
sCustName = Me!CustomerName.Value
sProdType = Me! [ProductType] .Value
 
If Not IsNull(sCustomerName) And Not IsNull(sProdType) Then
sOrderSeq = Dmax(Val(“[OrderID]”, “qryOrders”,”[CustomerName] = ‘” & sCustName & “’“ And [ProductType] = ‘“ & sProdType & “’”))  +  1
 
sOrderID = sCustName & sProdType & Format(iOrderSeq, “0000”)
Else
    “Can’t generate the OrderID….Customer Name or Product Type are missing”
End If
End Sub
 

[Rabbit]

”[CustomerName] = ‘” & sCustName & “’ And [ProductType] = ‘“ & sProdType & “’”

[3acks]

Where do I place the code? I notice you have it on the BeforeUpdate of the form, but there needs to be multiple values selected first and they have not had the chance to choose the customer or product yet??

[puppydogbuddy]

Where do I place the code? I notice you have it on the BeforeUpdate of the form, but there needs to be multiple values selected first and they have not had the chance to choose the customer or product yet??

The Form_BeforeUpdate is correct. This event runs just before the changed data on the form has been saved to the database. This is where validation of the changes is usually done...and it can be cancelled if needed.

[3acks]

”[CustomerName] = ‘” & sCustName & “’ AND [ProductType] = ‘“ & sProdType & “’”

I can not get this to work with two criteria... even if I use specific values for sCustName and sProdType...

[puppydogbuddy]

”[CustomerName] = ‘” & sCustName & “’ AND [ProductType] = ‘“ & sProdType & “’”

I can not get this to work with two criteria... even if I use specific values for sCustName and sProdType...

That is Rabbit's suggested correction to my code. Did you correctly blend it in to my code, or are you testing that code line by itself? Please post your updated code and describe the error you are getting.

[3acks]

Alright, I got it to work using a simple multiple criteria inside a query. Now when I try to transfer this concept to my form where I actually have 3 criteria, it fails. Here is the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then


[txtseqno] = DMax("[seq no]", "Line_List", "[Pipe spec] = " & Forms![line test]!PipeSpec & " And [fluid code] = " & Forms![line test]!FluidCode & " And [system number] = " & Forms![line test]!SystemNumber & "") + 1
Else

MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"


End If


End Sub


When I use the form, it gives me a 0 value for the next sequence number as soon as I select the first one of the three variables. Then when I go to leave the record I an error "Run-time error 2001, you canceled the previous operation"

[3acks]

When I got out and got back in, this time the error was different. Run-time error 3085, undefined function 'A' in expression.

?????

[puppydogbuddy]

When I got out and got back in, this time the error was different. Run-time error 3085, undefined function 'A' in expression.

?????

OK. Before I look over the code, can you give me an example of what the data for each criteria looks like so that I can get a visual picture of what the final OrderID should look like? Thanks.

[Rabbit]

That is Rabbit's suggested correction to my code. Did you correctly blend it in to my code, or are you testing that code line by itself? Please post your updated code and describe the error you are getting.

Yeah, sorry, I should have made that clear.

[3acks]

yeah, just a 3 digit number is all. I'll concatenate at times for reports and things to create a longer number, but this field is just a 3 digit number.

[puppydogbuddy]

Alright, I got it to work using a simple multiple criteria inside a query. Now when I try to transfer this concept to my form where I actually have 3 criteria, it fails.

yeah, just a 3 digit number is all. I'll concatenate at times for reports and things to create a longer number, but this field is just a 3 digit number.

[/quote]

If the OrderID is going to be a 3 digit number, try the syntax below. Keep in mind that the tab order for the 3 criteria must precede the txtseqno in order for it to work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then
 
 
[txtseqno] = DMax("[seq no]", "Line_List", "[Pipe spec] = " & Forms![line test]!PipeSpec & " And [fluid code] = " & Forms![line test]!FluidCode & " And [system number] = " & Forms![line test]!SystemNumber) + 1
Else
 
MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"
 
 
End If
 
 
End Sub
 

[3acks]

Undefined function 'A' in expression???? No number generated.

[3acks]

Is this some reference file that I need to load? Just a thought...

[puppydogbuddy]

Undefined function 'A' in expression???? No number generated.

Did you make sure the tab order of the 3 criteria fields preceded the txtseqno?
Can you tell me which part of the code was highlighted? Can you tell what is showing for the value of each variable?

[3acks]

This is the line that is highlighting.

[txtseqno] = DMax("[seq no]", "Line_List", "[Pipe spec] = " & Forms![line test]!PipeSpec & " And [fluid code] = " & Forms![line test]!FluidCode & " And [system number] = " & Forms![line test]!SystemNumber) + 1


It is giving the correct value from the form for each field.

[puppydogbuddy]

This is the line that is highlighting.

[txtseqno] = DMax("[seq no]", "Line_List", "[Pipe spec] = " & Forms![line test]!PipeSpec & " And [fluid code] = " & Forms![line test]!FluidCode & " And [system number] = " & Forms![line test]!SystemNumber) + 1


It is giving the correct value from the form for each field.

Try this code and tell me what happens.

[txtseqno] = nz(DMax("[seq no]", "Line_List", "[Pipe spec] = " & Forms![line test]!PipeSpec & " And [fluid code] = " & Forms![line test]!FluidCode & " And [system number] = " & Forms![line test]!SystemNumber) + 1, 0)
 
MsgBox "seq no " & [seq no]
MsgBox "Pipe spec " & [Pipe Spec]
MsgBox "Fluid Code " & [fluid code] 
MsgBox "System Number " & [system number]
 

[3acks]

It keeps telling me I've canceled the previous operation and won't go on.

[3acks]

I've gotten past that and I'm getting the unknown function 'A' again. I've tried repacing the three variables with a predefined string, but it's not reading it correctly. It's giving me the next available number for the entire database and not for the criteria.


Dim Pspec As String
Dim Fcode As String
Dim Synum As String
Dim STR As String




Pspec = Me![Pipe Spec].Value
Fcode = Me![fluid code].Value
Synum = Me![system number].Value

If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then
STR = ([Pipe Spec] = Pspec And ([fluid code] = Fcode And ([system number] = Synum)))

[txtseqno] = DMax("[seq no]", "Line_List", STR) + 1



MsgBox "seq no " & [seq no]
MsgBox "Pipe spec " & [Pipe Spec]
MsgBox "Fluid Code " & [fluid code]
MsgBox "System Number " & [system number]


Else

MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"


End If

[puppydogbuddy]

OK, try this. Please limit your changes to syntax corrections. Don’t rework my code (it gets confusing when two people are making code changes to the same code).……..just tell me what happened (in detail) when you ran the code.

Private Sub Form_BeforeUpdate()
Dim Pspec As String
Dim Fcode As String
Dim Synum As Integer
Dim STR As String
 
 
If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then
Pspec = Me![Pipe Spec].Value
Fcode = Me![fluid code].Value
Synum = Me![system number].Value
 
STR =  Pspec & Fcode 
 
‘am assuming that [seq no] is the control source of txtseqno 
 [txtseqno] = STR & DMax("[seq no]", "Line_List", “[Pipe Spec] = ‘” & Pspec & “’” & “And [fluid code] = ‘” & Fcode & “’”) + 1
 
 
 
MsgBox "seq no " & [txtseqno]
MsgBox "Pipe spec " & [Pipe Spec]
MsgBox "Fluid Code " & [fluid code]
MsgBox "SystemNumber " & [Synum]
MsgBox "STR " & STR
 
 
Else
 
MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"
 
 
End If
End Sub
 

[3acks]

Finally got it to work right. I think one of my problems is that System Number is a number format, so no (')s go around the string.

Thanks for the help.


Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim Pspec As String
Dim Fcode As String
Dim Synum As Integer
Dim STR As String


If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then

Pspec = Me![pipe spec].Value
Fcode = Me![fluid code].Value
Synum = Me![System number].Value

[seq no] = DMax("[seq no]", "Line_List", "[pipe spec] = '" & Pspec & "'" & " And [fluid code] = '" & Fcode & "'" & " And [system number] = " & Synum & "") + 1

Else

MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"

End If

End Sub

[puppydogbuddy]

You are welcome. Glad I could help.

[3acks]

Just a follow up for anyone who refers to this at a later date. I moved the code to the AfterInsert update because on BeforeUpdate it would re-calculate the number with any change to any field in the record and I only want a number calculated when a new record is added. I also added an If statement to handle if the search returned no current value, so that the first record would get a value = 1.


Private Sub Form_AfterInsert()

Dim Pspec As String
Dim Fcode As String
Dim Synum As Integer
Dim STR As String
Dim Seq As Integer

If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then

Pspec = Me![pipe spec].Value
Fcode = Me![fluid code].Value
Synum = Me![System number].Value


[seq no] = DMax("[seq no]", "Line_List", "[pipe spec] = '" & Pspec & "'" & " And [fluid code] = '" & Fcode & "'" & " And [system number] = " & Synum & "") + 1

If IsNull([seq no]) Then
[seq no] = 1
Else
End If

MsgBox "Sequence No. =" & [seq no]

Else

MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"

End If

End Sub

[puppydogbuddy]

Thank you for taking the time to document your final solution for the benefit of others.

[hyperpau]

yeah, just a 3 digit number is all. I'll concatenate at times for reports and things to create a longer number, but this field is just a 3 digit number.

You can't actually have this as a 3 digit number because
you are concatenating other fields which would produce more than 3 digits.

This is how i picture what youre saying


field1 -- let's say the largest number now 13
field2 -- 220
field3 -- 220

if you dmax the field 1 of whichever table it belongs and then attach codes like
& [field2] & [field3]) + 1

result:
field1 -- 13220221

if you want the result to be 3 digits, use "+" instead of "&"

result:
field 1 -- 454 ((13+220+220)+1)

[cannon]

Hi All and thanks for all participants,
i have search in several websites and articals i dont find a solution for this subject, in this case all the three fields is text data type, i have match case but I would generate text transaction unique ID consist of three parts "textYYYYNNNN", where
text = 4 letter from "Dept" Field, (text datatype)
YYYY = year from "Date" Field, (date datatype)
NNNN = sequence from "Seq" Field, (numeric datatype)

So I have three conditions to generate transaction ID that:

1st condition is
SELECT Transmittal.TransmittalID, Transmittal.TransmittalTitle, Transmittal.Dept FROM Transmittal WHERE (((Transmittal.Dept)= "Geology"));

And from previous result I want to set the date I form and it should save to table then I want to apply 2nd condition

2nd condition is
SELECT Transmittal.TransmittalID, Transmittal.TransmittalTitle, Transmittal.TransmittalDate FROM Transmittal
WHERE (((Transmittal.TransmittalDate)=max)); >>>>>>> it’s mean current day, the reason of that may I have more then on transmittal at one day.

And from previous result I want apply the 3rd condition

3rd condition is
SELECT Transmittal.TransmittalID, Transmittal.TransmittalTitle, Max(Transmittal.Sequence) AS MaxOfSequence FROM Transmittal GROUP BY Transmittal.TransmittalID, Transmittal.TransmittalTitle;

Then I want (4 letter from Dept)&(year from date)&(max seq + 1))
Example: GEOL20080252.

Regads
abdulaziz