DMax trouble on a filtered form

raysoff

New member
Local time
Today, 02:28
Joined
Jul 18, 2014
Messages
6
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
 
Try adding a Criteria...

Code:
Nz(DMax("ClaimNo", "ClaimData","[ClientNo] = " & Me![txtFieldOnForm]), 0) + 1
 
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?
 
Did you see the link and the different syntax required for a text field?
 
Try...

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

If the above doesn't work, please see the link pbaldy supplied.
 
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!
 

Users who are viewing this thread

Back
Top Bottom