Dmax on form getting Name#error

[abalserv]

Hi All
I have trawled through the forums and I think I'm just missing one small piece here.

I have a sub-form with a field (Numeric) called Treatment Number. Essentially what I want to happen here is that when a new treatment is added - the number increments from the last one e.g. the first treatment was 1, add a new treatment it's 2 etc for each individual patient. What I want it to do is locate what patient has been picked in the combo box and then match the increment to that.

I have added this to the control source for the Treatment Number field in my subform:
=DMax([tbl_Treatment_Details]![TreatmentNumber],"[TreatmentNumber]"," [txtPatientID] =" & [Forms]![MFrm_Patient_Details 01]![cboFindPatient])+1

[txtPatientID] is the name given to the PatientID on the main form
However when I enter this I get #Name? .
What am I doing wrong?

Thanks!
A

 

[vbaInet]

Correct syntax (without knowing what datatype TreatmentNumber is):

=DMax("TreatmentNumber", "TreatmentNumber", "txtPatientID = " & [Forms]![MFrm_Patient_Details 01]![cboFindPatient]) + 1

What is the data type of TreatmentNumber?

Note, you don't need square brackets if your field or table name doesn't contain spaces or special characters and your field name should just be the field name not TableName!FieldName.

 

[abalserv]

hi VBAiNet
Thanks for quick response and appreciate the heads up on the syntax.
Treatment number is number (Single) and when I tried your revised dmax (amended your one to show the table name first ...I now get an #Error message
=DMax("Tbl_Treatment_Details","TreatmentNumber","txtPatientID = " & [Forms]![MFrm_Patient_Details 01]![cboFindPatient])+1
What am I missing (again!)
A

 

[vbaInet]

DMax is:

DMax("Field", "Table", "Criteria")

Your syntax is off. Amend and paste back. And what is the data type of PatientID? I doubt that txtPatientID is the actual field name. It should be the field name and not the control name.

 

[abalserv]

Have amended and that works better. PatientID is an autonumber field and txtPatientID is the name of the field. I've tried that and also added some treatment numbers up to 8 to the underlying table. Now it just seems stuck at 9 and won't increment..and it shows 9 for both of my sample records (even though I have only added up to treatment 5 on one..) :-((
A

 

[vbaInet]

So the #Error has been resolved?

 

[abalserv]

#error has been resolved - many thanks. Any idea what else I might be missing?
Anne

 

[vbaInet]

Let me see your full code.

 

[abalserv]

This is what I have at the moment in the Treatment Number control on a sub form. The txtPatientID is the name for the PatientID on the main form (I gave it different names because Allen ?? MVP recommended doing so .
I think where I'm going astray is that I am not getting the value into the underlying table...

=DMax("TreatmentNumber","Tbl_Treatment_Details","txtPatientID = " & [Forms]![MFrm_Patient_Details 01]![cboFindPatient])+1

I haven't added any code and Access hasn't added anything to any of the events on this control..
Thanks.
Anne

 

[vbaInet]

txtPatientID should be PatientID in the code.

DMax requires the field name to apply the criteria on.

 

[abalserv]

Just tried that. I have up to 8 in the treatment table itself for one record and so it adds 9 but won't increment beyond that. So when I add a new treatment it still shows 9 and stays at 9
For my other sample record it won't show the treatment number at all. (I'm using Access 2010 - sorry had Access 2007) .
I wonder if there is something I have to requery?