Dmax not working (3 Viewers)

[dullster]

I am trying to create a Dmax in a subform to increase my "RecordNum" field. It either doesn't work or it produces the same number all the time.

 

[GaP42]

What is the expression you are using? What type of field is RecordNum? Integer? Autonumber?

 

[The_Doc_Man]

Are you trying to store something based on a "DMax() + 1" type of formula?

For "Doesn't work" the question is, then what does it actually do?

For "same number" that means that something didn't get stored or didn't get incremented BEFORE it was stored. (We can't tell which of those two from here.)

 

[dullster]

What is the expression you are using? What type of field is RecordNum? Integer? Autonumber?

=DMax("tblDemoTrans","RecordNum")+1 . It is a number field.

 

[Gasman]

Crossposted first at https://www.accessforums.net/showthread.php?t=90708

 

[dullster]

Are you trying to store something based on a "DMax() + 1" type of formula?

For "Doesn't work" the question is, then what does it actually do?

For "same number" that means that something didn't get stored or didn't get incremented BEFORE it was stored. (We can't tell which of those two from here.)

Currently I am getting an error with this code =DMax("tblDemoTrans","RecordNum")+1

 

[Gasman]

Are you trying to store something based on a "DMax() + 1" type of formula?

For "Doesn't work" the question is, then what does it actually do?

For "same number" that means that something didn't get stored or didn't get incremented BEFORE it was stored. (We can't tell which of those two from here.)

Already been advised of not incrementing in the other forum Doc.

 

[dullster]

Already been advised of not incrementing in the other forum Doc.

what does that mean. I could not find my other post.

 

[pbaldy]

Currently I am getting an error with this code =DMax("tblDemoTrans","RecordNum")+1

You've reversed the arguments:

General: DLookup Usage Samples

 

[GaP42]

' Typical Use
' Numerical values. Replace "number" with the number to use.
    variable = DMax("[FieldName]", "TableName", "[Criteria] = number")

From https://learn.microsoft.com/en-us/office/vba/api/access.application.dmax
(Criteria is optional)
Place your arguments in the correct sequence
Increment the variable after getting the max value

 

[Mark_]

=DMax("tblDemoTrans","RecordNum")+1 . It is a number field.

Why are you trying to do this instead of having "RecordNum" be autonumbered?
I'd also give it a better name than "RecordNum". DemoTrans_ID may be a better name as it makes it very apparent it is the primary key for the DemoTrans table, the main reason you use an autonumbered field.

 

[Gasman]

You've reversed the arguments:

General: DLookup Usage Samples

Already advised on the first reply on other site?

 

[GaP42]

Why are you trying to do this instead of having "RecordNum" be autonumbered?
I'd also give it a better name than "RecordNum". DemoTrans_ID may be a better name as it makes it very apparent it is the primary key for the DemoTrans table, the main reason you use an autonumbered field.

Speculating it might be a customer-facing transaction ID and not the PK for the table.

 

[Mike Krailo]

=DMax("tblDemoTrans","RecordNum")+1 . It is a number field.

You got the table and field reversed. I don't think this is a PK.

=DMax("RecordNum","tblDemoTrans")+1

 

[The_Doc_Man]

=DMax("tblDemoTrans","RecordNum")+1 . It is a number field.

As noted, you have the table and field arguments reversed, so this should do nothing. HOWEVER,...

Where is that snippet of code used? If it is on a form as the .ControlSource of a control on the form, that means the particular control is not bound to a record and therefore will not be stored when you increment it. So you have TWO reasons why this won't work.

 

[Mark_]

Speculating it might be a customer-facing transaction ID and not the PK for the table.

Normally when I see DMax() used for customer facing numbers it is stored as a "PO" or "Receipt" number rather than "RecordNum". Hence the question as I'd hate to think OP is trying to reinvent fire!

 

[GaP42]

Yes - or it is part of the construction of that customer facing identifier - often with a year/ alpha prefix...