Solved DMax

[dullster]

I have a form with Vendors that enters information to the tblDemoTrans and a subform that the Receipt information from the Vendor entered into. I want to do a DMax on the ReceiptNr field in the subform.

I have tried: DMax("ReceiptNr","tblDemoTrans") + 1 and =[Me]![ReceiptNr].[Value]=DMax("ReceiptNr","tblDemoTrans")+1
In the load on form, After Update of the ReceiptNr field, Before Update of that field, On focus of that field.

I can't get it to populate the next number. Any ideas which property it should go into and if either are the correct codes?

 

[CJ_London]

Assuming your dmax function works (try it in the immediate window) you should only need

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

and perhaps try the subform current event

 

[dullster]

I tried
DMax("ReceiptNr","tblDemoTrans") + 1 and =[Me]![ReceiptNr].[Value]=DMax("ReceiptNr","tblDemoTrans")+1 and ReceiptNr=DMax("ReceiptNr","tblDemoTrans")+1 in current event. I doesn't like any of them.

With =[Me]![ReceiptNr].[Value]=DMax("ReceiptNr","tblDemoTrans")+1 I get Syntax error

With ReceiptNr=DMax("ReceiptNr","tblDemo It can not find the object

With DMax("ReceiptNr","tblDemoTrans") + 1 I get syntax error

with =DMax("ReceiptNr","tblDemoTrans") + 1 I get Syntax Error

With DMax ([ReceiptNr].[tblDemoTrans]) + 1 I get argument not optional

 

[CJ_London]

have you tried in in the immediate window. If not, try it. What do you get?

Is the table populated? if not you will get an error since dmax will return null -so use the nz function

sometimes it helps if you understand what the function does

DMin, DMax Functions - Microsoft Support

support.microsoft.com

 

[Mark_]

Wrong spot.
The easy way to do this is when the record is SAVED.
IIRC, this should be a "Check if we have a receipt number in the form's before update. If it doesn't exist, load it here".
This avoids having "Gaps" when you have multiple users and some entries are discarded.

Last time I had to do this was in a different development environment so I'm not as sure for which Access event you'll need.

 

[dullster]

have you tried in in the immediate window. If not, try it. What do you get?

Is the table populated? if not you will get an error since dmax will return null -so use the nz function

sometimes it helps if you understand what the function does

DMin, DMax Functions - Microsoft Support

support.microsoft.com

Yes
=[Me]![ReceiptNr].[Value]=DMax("ReceiptNr","tblDemoTrans")+1 I get Expected: line number or label or statement or end of statement

DMax("ReceiptNr","tblDemoTrans") + 1 I get Expected: =

the table is populated.

 

[arnelgp]

if you are evaluating it on immediate window, prefix it with ? (question mark).

? DMax("ReceiptNr","tblDemoTrans") + 1

 

[dullster]

Wrong spot.
The easy way to do this is when the record is SAVED.
IIRC, this should be a "Check if we have a receipt number in the form's before update. If it doesn't exist, load it here".
This avoids having "Gaps" when you have multiple users and some entries are discarded.

Last time I had to do this was in a different development environment so I'm not as sure for which Access event you'll need.

Thank you. I put it in the Control Source. =Max([ReceiptNr])+1 this gets me a 1. If i try DMax, it changes it back to Max.

 

[dullster]

if you are evaluating it on immediate window, prefix it with ? (question mark).

? DMax("ReceiptNr","tblDemoTrans") + 1

That returned the number I need. I put it in control source and it doesn't work. Where do i put it?

 

[arnelgp]

can you use this as Control Source:

=Nz(DMax("ReceiptNr","tblDemoTrans"), 0)+1

 

[Pat Hartman]

@dullster What do you intend to do with this number? Are you aware that it will NOT be saved AND the same record can easily have a different result depending on what the value of ReceptNr is at the time the expression was executed.

You were told two very important things by others.
1. Always use the Nz() function when calculating the next number - So - follow the advice in #10
2. If you want the number to be permanent, you need to generate it in the Form's BeforeInsert event so the calculation happens only for a new record. Also, you need to be aware that in a very busy input form, you can generate duplicates so you need to handle that plus, you should add a unique index on the sequence number field to avoid saving a duplicate (in some cases, you may need a two-field unique index if you are generating a sequence number within a higher level grouping).

 

[dullster]

can you use this as Control Source:

=Nz(DMax("ReceiptNr","tblDemoTrans"), 0)+1

It enters it on the form, but doesn't save it to the table. If i enter another record, it uses the same number and when i check the table, there's a 0 there.

 

[dullster]

@dullster What do you intend to do with this number? Are you aware that it will NOT be saved AND the same record can easily have a different result depending on what the value of ReceptNr is at the time the expression was executed.

You were told two very important things by others.
1. Always use the Nz() function when calculating the next number - So - follow the advice in #10
2. If you want the number to be permanent, you need to generate it in the Form's BeforeInsert event so the calculation happens only for a new record. Also, you need to be aware that in a very busy input form, you can generate duplicates so you need to handle that plus, you should add a unique index on the sequence number field to avoid saving a duplicate (in some cases, you may need a two-field unique index if you are generating a sequence number within a higher level grouping).

I am trying to populate the field. I was not aware it would NOT be saved. I tried BeforeInsert and AfterInsert. It does not save.

 

[CJ_London]

I tried BeforeInsert and AfterInsert. I does not save.

You are doing your usual thing of vague descriptions which turns the thread from perhaps 3 or 4 posts to who knows how many.

So what did you try - show the code you used - for all we know your form recordset is not updateable, the code is in a different location, the field/control is misspelled or doesn’t exist since ‘it does not save’ is as bad as ‘doesn’t work’. It does not help us to help you

 

[dullster]

You are doing your usual thing of vague descriptions which turns the thread from perhaps 3 or 4 posts to who knows how many.

So what did you try - show the code you used - for all we know your form recordset is not updateable, the code is in a different location, the field/control is misspelled or doesn’t exist since ‘it does not save’ is as bad as ‘doesn’t work’. It does not help us to help you

I posted all that i have tried in #3. For everything i tried, i thought i gave of a response. I tried =Nz(DMax("ReceiptNr","tblDemoTrans"), 0)+1 in Afterinsert and Beforeinsert. The ReceiptNr field remained blank.

 

[CJ_London]

I give up - as described you would get an error. Good luck but I’m dropping off this thread.

 

[dullster]

I give up - as described you would get an error. Good luck but I’m dropping off this thread.

 

[dullster]

I don't think it was said I would get an error. I found a way around it. Thanks

 

[Pat Hartman]

I don't think it was said I would get an error. I found a way around it. Thanks

It would be ever so much more helpful if you actually copied the code from your event procedure and pasted it rather here than just typing something you think is equivalent.

In the form's BeforeInsert event, NOT the AfterInsertEvent or any other event use:

Me.SomeField = Nz(DMax("ReceiptNr","tblDemoTrans"), 0)+1

Don't forget to make Me.SomeField the name of the bound control you want the value to be saved into.

You can use the form's BeforeUpdate event but then you need additional code because you ONLY want the code to execute for a new record. You do NOT want the code to run if you update an existing record. That is why I specifically said to use the form's BeforeInsert event. It only runs when you start a new record. The Access event model is not random. Specific events are intended to be used for specific situations.

The "After" events run AFTER THE RECORD IS SAVED and so those events are too late AND using them for this puts the code into an infinite loop so who knows what you end up with as a value once Access finally breaks out of the loop.