DMAX on form question

[punky001]

I have an issue with an old database I inherited. I have updated to Access 2007 front-end and moved the back-end to SQL.
The field in question is called “Number” with field type of Int. This field is populated from the form based on the following calc:
=DMax("[Number]","[RequiredReportTable]","[ContractNumber] =" & [Forms]![AddRequiredReportForm]![ContractNumber])+1
The table – RequiredReportTable – has a number of different Contract numbers. Each time a record is added for a specific contract nr – the “Number” field should increase by 1 – only for that contract nr. This works most of the time – but sometimes it skips numbers – which means it appears as though records may have been deleted.
I can’t figure out why it is skipping numbers. Can anyone help? Is there a better way to do this?

 

[randy700]

Why not just use an autonumber field?

Randy

 

[punky001]

Because there may be numerous contract nrs - each contract nr would start over at 1

 

[MacroScope]

Got way more than ya need there.

=DMax("[ContractNumber]","[RequiredReportTable]")+1 is all ya need!

That is the value you want to set the record to.

I'm not sure I understand one point. Are you using this as a count field, sort of keeping running tabs on how many records are in play, or are you uing it to generate a count, once, to set the ID number of a record?

I ask, because seeing the +1 at the end would not be useful for a count, but it would be for setting the next consecutive ID number.

 

[punky001]

Thanks MacroScope....It's being used as an "autonumber" for records for each contract.
Example:
cont 1234 = Last RecNr = 100 - Next RecNr = 101
cont 3333 = Last Rec Nr = 850 - Next RecNr = 851

 

[MacroScope]

I don't know what the cont 1234 might be, but it's irrelevant. You're looking for the maximum number in a domain and incrementing it by one. That's =DMax("[ContractNumber]","[RequiredReportTable]")+1 is all ya need!

 

[randy700]

Macroscope - the OP needs the where portion.

punk001 - can you give a little more information about the problem you're having? Looks like the code you have should work.


Randy

 

[punky001]

Hey Randy007....

Here's the original:
=DMax("[Number]","[RequiredReportTable]","[ContractNumber] =" & [Forms]![AddRequiredReportForm]![ContractNumber])+1

It hit me that "Number" is a reserved word and should not be used as a field name - so I changed the name to "RRNumber". So far....it has worked correctly.

I'm not sure if that was it or not - I'm still testing...