By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,820 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,820 IT Pros & Developers. It's quick & easy.

Auto increment number based off of other values

P: 16
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.
May 31 '07 #1
Share this Question
Share on Google+
27 Replies

Rabbit
Expert Mod 10K+
P: 12,441
"Criteria1 AND Criteria2"
May 31 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate()
  2. Dim sOrderID As String
  3. Dim iOrderSeq As Integer
  4. Dim sCustName As String
  5. Dim sProdType As String
  6.  
  7. sCustName = Me!CustomerName.Value
  8. sProdType = Me! [ProductType] .Value
  9.  
  10. If Not IsNull(sCustomerName) And Not IsNull(sProdType) Then
  11. sOrderSeq = Dmax(Val(“[OrderID]”, “qryOrders”,”[CustomerName] = ‘” & sCustName & “’“ And [ProductType] = ‘“ & sProdType & “’”))  +  1
  12.  
  13. sOrderID = sCustName & sProdType & Format(iOrderSeq, “0000”)
  14. Else
  15.     “Can’t generate the OrderID….Customer Name or Product Type are missing”
  16. End If
  17. End Sub
  18.  
May 31 '07 #3

Rabbit
Expert Mod 10K+
P: 12,441
Expand|Select|Wrap|Line Numbers
  1. ”[CustomerName] = ‘” & sCustName & “’ And [ProductType] = ‘“ & sProdType & “’”
May 31 '07 #4

P: 16
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??
Jun 1 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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.
Jun 1 '07 #6

P: 16
”[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...
Jun 1 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
”[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.
Jun 1 '07 #8

P: 16
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"
Jun 1 '07 #9

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

?????
Jun 1 '07 #10

puppydogbuddy
Expert 100+
P: 1,923
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.
Jun 1 '07 #11

Rabbit
Expert Mod 10K+
P: 12,441
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.
Jun 1 '07 #12

P: 16
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.
Jun 4 '07 #13

puppydogbuddy
Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3. If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then
  4.  
  5.  
  6. [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
  7. Else
  8.  
  9. MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"
  10.  
  11.  
  12. End If
  13.  
  14.  
  15. End Sub
  16.  
Jun 4 '07 #14

P: 16
Undefined function 'A' in expression???? No number generated.
Jun 4 '07 #15

P: 16
Is this some reference file that I need to load? Just a thought...
Jun 4 '07 #16

puppydogbuddy
Expert 100+
P: 1,923
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?
Jun 4 '07 #17

P: 16
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.
Jun 4 '07 #18

puppydogbuddy
Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. [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)
  2.  
  3. MsgBox "seq no " & [seq no]
  4. MsgBox "Pipe spec " & [Pipe Spec]
  5. MsgBox "Fluid Code " & [fluid code] 
  6. MsgBox "System Number " & [system number]
  7.  
Jun 4 '07 #19

P: 16
It keeps telling me I've canceled the previous operation and won't go on.
Jun 4 '07 #20

P: 16
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
Jun 4 '07 #21

puppydogbuddy
Expert 100+
P: 1,923
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.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate()
  2. Dim Pspec As String
  3. Dim Fcode As String
  4. Dim Synum As Integer
  5. Dim STR As String
  6.  
  7.  
  8. If Not IsNull(Forms![line test]!PipeSpec) And Not IsNull(Forms![line test]!FluidCode) And Not IsNull(Forms![line test]!SystemNumber) Then
  9. Pspec = Me![Pipe Spec].Value
  10. Fcode = Me![fluid code].Value
  11. Synum = Me![system number].Value
  12.  
  13. STR =  Pspec & Fcode 
  14.  
  15. ‘am assuming that [seq no] is the control source of txtseqno 
  16.  [txtseqno] = STR & DMax("[seq no]", "Line_List", “[Pipe Spec] = ‘” & Pspec & “’” & “And [fluid code] = ‘” & Fcode & “’”) + 1
  17.  
  18.  
  19.  
  20. MsgBox "seq no " & [txtseqno]
  21. MsgBox "Pipe spec " & [Pipe Spec]
  22. MsgBox "Fluid Code " & [fluid code]
  23. MsgBox "SystemNumber " & [Synum]
  24. MsgBox "STR " & STR
  25.  
  26.  
  27. Else
  28.  
  29. MsgBox "Can not generate Sequence Number, the Fluid Code, System Number or Pipe Spec are missing"
  30.  
  31.  
  32. End If
  33. End Sub
  34.  
Jun 4 '07 #22

P: 16
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
Jun 5 '07 #23

puppydogbuddy
Expert 100+
P: 1,923
You are welcome. Glad I could help.
Jun 5 '07 #24

P: 16
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
Jun 6 '07 #25

puppydogbuddy
Expert 100+
P: 1,923
Thank you for taking the time to document your final solution for the benefit of others.
Jun 6 '07 #26

hyperpau
Expert 100+
P: 184
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)
Jun 13 '07 #27

cannon
P: 1
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
Aug 3 '08 #28

Post your reply

Sign in to post your reply or Sign up for a free account.