DMax trouble on a filtered form

[raysoff]

Hi,

I am somewhat new to Access code. I am trying to make a number field in a table default to the highest number in that table plus 1 within a given client.

I have a table (named ClaimData) that I input data through a form which gets filtered by an After Update, Event Procedure macro via an unbound combobox (AuditCode) to the client I am currently working on (table AuditData is the list of all Clients).

When I identify an issue for a client, I enter the new information in ClaimData and I assign it a claim number (ClaimNo). Each client may have anywhere from 15 claims to 200 claims. When I use the below code in On Click, Event Procedure in a vba module:

Private Sub IssueNo_Click()
Me.ClaimNo = Nz(DMax("ClaimNo", "ClaimData"), 0) + 1
End Sub

to automatically increment IssueNo by 1, it can only give me 200 +1. If I am filtered on a client that the next issue is 16, the next number I get is 201.

Any thoughts on how I could make it look for the highest number inside of the forms filtered parameters and stay within the correct AuditCode from AuditData?

Thanks, any help will be appreciated

 

[pbaldy]

Sounds like you need to add a criteria so the DMax is customer specific:

http://www.mvps.org/access/general/gen0018.htm

 

[GinaWhipp]

Try adding a Criteria...

Nz(DMax("ClaimNo", "ClaimData","[ClientNo] = " & Me![txtFieldOnForm]), 0) + 1

 

[raysoff]

Thank you to pbaldy and GinaWhipp for the reply. GinaWhipp, I still have an issue figuring out what the criteria of txtFieldOnForm is for my form. I tried Nz(DMax("ClaimNo", "ClaimData", "[AuditCode] = " & Me![AuditCode]), 0) + 1 and get error 2471 produced by a query parameter.

I forgot to mention the following:
Main form = ClaimEntryForm (this is the form that the combo box filters the sub form
ClaimData = Subform.
AuditCode = Is the field in both tables and is text.
Combobox name is AuditCode.
What goes where to set the correct criteria?

 

[pbaldy]

Did you see the link and the different syntax required for a text field?

 

[GinaWhipp]

Try...

Nz(DMax("ClaimNo", "ClaimData", "[AuditCode] = '" & Me![AuditCode] & "'"), 0) + 1

If the above doesn't work, please see the link pbaldy supplied.

 

[vbaInet]

Not unless the field has a lookup causing it to disguise itself as Text rather than (I suspect) Number type.

By the way once you get this working, you can put it in the Default Value property of the control.

 

[raysoff]

pbaldy, being pretty new to all of this, I was still reading through what you advised and I see why you sent it. Thanks a lot as it did help me understand what GinaWhipp sent the second time!

GinaWhipp, your last post did the trick exactly as I was hoping. I thank you for making what pbaldy suggested understandable to me!

vbaInet, thank you also for your input. That will help keep it all clean!