DMax Expression in a Form

[Oscar Mike]

Hi all,

I have an unbounded text box where I want to be able to display the highest number of a certain field in a certain record. I've tried using DMax but all I am getting is #Errors and it's getting frustrating.

=DMax("Issue_Number","Issue_Tracker","StaffID=" & Forms!Manager!StaffID)

Where Issue_Number is the field, Issue_Tracker is the table and I want the Staff ID's to match up.

Is there a simple solution to this?

Thanks in advance

Also I'm using 2003.

 

[boblarson]

If the code is on the same form as the StaffID text box, then

=DMax("Issue_Number","Issue_Tracker","StaffID=" & Me.StaffID)

If StaffID is a text datatype then



=DMax("Issue_Number","Issue_Tracker","StaffID=" & Chr(34) & Me.StaffID & Chr(34))

 

[Oscar Mike]

Hi Bob,

Thanks for the reply.

I tried doing what you suggested but no luck.

It's giving me a #Name error now. You have the right idea on what I want to be able to do. I've been stuck on this for a good few hours now and it's really grinding my gears.

 

[boblarson]

Make sure that there is NO CONTROL on the form with the name StaffID. If there is, rename it to txtStaffID but before you do, make sure to turn off Name AutoCorrect (which should be turned off for each database you create anyway as it is also known as Name AutoCorrupt).

 

[Oscar Mike]

Am I right in that I put that expression in the "Control Source" of the textbox I want it displayed in?

 

[boblarson]

Am I right in that I put that expression in the "Control Source" of the textbox I want it displayed in?

That is correct. However, the default for Access, if you use the wizard to create a form is to name the controls the same name as the fields. If any of those fields are used in a formula in a control source of another control, then it will bring back #NAME because Access can't distinguish between the field and control in that case. So any fields that are used in formulas on the form in control sources need to have the controls they are bound to named something different from the actual field name.

 

[Oscar Mike]

Not having much luck on this Bob, could I send you my database in an email? It's perfectly alright if you prefer not to, we are all busy people.

 

[boblarson]

Can you just post it here (after running Compact and Repair and then zipping the file by right-clicking on it and selecting SEND TO > COMPRESSED FOLDER)? When zipped the file size would need to be under 2Mb.

 

[Oscar Mike]

I'm afraid I can't, it's for a government organisation. I'll keep working on it, thanks for your time.