• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

incrementing in sub sub form using dmax...should I use OnCurrent or OnInsert

Thanks to @ mbizup and @als315 for their help with this.
I have a main form: mFrm_Patient_Details (with a combobox)
Then a sub form (sFrmTreatmentDetails) which @mbizup and @als315 very kindly helped me with This now increments the treatment number when the button is clicked.
I have tried adapting this to work for the session subform (subform to SFrmTreatmentDetails) see code below - alas, a new number doesn't appear..
Private Sub Form_Current()

If IsNull(Me.SessionNumber) Then
    Me.SessionNumber = Nz(DMax("SessionNumber", "tbl_Session_Details", "TreatmentNumber=" & Forms!subsFrm_Session_Details!TreatmentNumber)) + 1
End If
End Sub

Also what is the difference between putting this code in OnCurrent or OnInsert for the form properties? What piece am I missing?  hyperbaric03.accdb
0
agwalsh
Asked:
agwalsh
  • 7
  • 4
1 Solution
 
mbizupCommented:
The Current Event fires as the user opens the form or changes records.

The Insert events are triggered when a record is added to the underlying table.

What form/steps should we be following to see this?
0
 
mbizupCommented:
Ok - I think I see the issues:

First, use the Current Event.

Second, check to verify that you are on a new record before using DMax +1

Finally, you can refer to the patient ID from that form as "Me.PatientID".

So in the Current Event:

If Me.NewRecord Then
     TreatmentNumber = Nz(DMax("TreatmentNumber", "tblTreatmentDetails", "PatientID=" & Me.PatientID)) + 1
End If

Open in new window

0
 
mbizupCommented:
You may also want to explicitly specify zero as the 'Value If NULL' for your NZ function:

If Me.NewRecord Then
     TreatmentNumber = Nz(DMax("TreatmentNumber", "tblTreatmentDetails", "PatientID=" & Me.PatientID),0) + 1
End If

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
agwalshAuthor Commented:
OK, so let me be clear. This is what I think I need to do:
Open up the sub sub form ( SubsFrm_Session_Details ) in Design view, locate the Current Event , then the code builder and then copy in the following code - but should I substitute Session details for it.
If Me.NewRecord Then
     TreatmentNumber = Nz(DMax("TreatmentNumber", "tblTreatmentDetails", "PatientID=" & Me.PatientID),0) + 1
End If
I've adapted it for the actual session table (sub sub form source) and this is what I now have..
Private Sub Form_Current()
If Me.NewRecord Then
     SessionNumber = Nz(DMax("SessionNumber", "tbl_Session_Details", "PatientID=" & Me.PatientID), 0) + 1
End If
End Sub
However this is working partially but what do I need to do to set it up so that if a new treatment is added, the session numbering starts again at 1...as at the moment it's continuing to increment the session numbers ...even when I add a new treatment
Thanks so much for your help!
0
 
mbizupCommented:
Okay, then for the sub-sub form only, you may need to add a second criteria for the session number:

SessionNumber = Nz(DMax("SessionNumber", "tbl_Session_Details", "PatientID=" & Me.PatientID & " AND SessionID = " & me.SessionID),0) + 1
0
 
agwalshAuthor Commented:
@mbizup...do I add this new session number into the OnCurrent Event of that sub form - because when I've tried it and then tried to add a new session I got the following message..

Runtime error 3075 Syntax error (missing operator) in query expression PatientID =1and SessionID = '.

Thanks!
a
0
 
mbizupCommented:
Sorry about that -

The new session number is linked to the PatientID and TreatmentID, so the second criteria should be TreatmentID, not SessionID (Check the exact field names - I'm typing them off the top of my head):

SessionNumber = Nz(DMax("SessionNumber", "tbl_Session_Details", "PatientID=" & Me.PatientID & " AND TreatmentID = " & me.TreatmentID),0) + 1
0
 
agwalshAuthor Commented:
@mbizup - that worked beautifully.. thank you..
However I now find I am having the following problems..
I locate a patient and go to add a new treatment. I get the following message:
Runtime error 3075, Syntax error (missing operator) in query expression PatientID=1 and Treatment ID
When I click End it then seems to add a new treatment number...
Also for the treatments when I use the Next Treatment button - the treatment numbers seem out of sequence?

For the session numbers it seems to increment the session numbers when I click on either the Previous session button or Next session button..so does the code which is currently on the Current_Form event need to be put into the command button?
Again, your help is really appreciated.
 hyperbaric04.accdb hyperbaric04.accdb
0
 
mbizupCommented:
Both of your subform current events need to be verifying that you are on a new record before creating a new ID.

The Current Event of your TreatmentDetails subform should look like this:

If Me.NewRecord= true Then
    Me.TreatmentNumber = Nz(DMax("TreatmentNumber", "tblTreatmentDetails", "PatientID=" & Forms!MFrm_Patient_Details!PatientID)) + 1
End If

Open in new window


Additionally you need to add handling for Null Treatment IDs in your Session Details subform, using NZ like this:

If Me.NewRecord = True Then
SessionNumber = Nz(DMax("SessionNumber", "tbl_Session_Details", "PatientID=" & Me.PatientID & " AND TreatmentID = " & Nz(Me.TreatmentID, -1)), 0) + 1
End If

Open in new window

0
 
agwalshAuthor Commented:
Worked like a dream - am most grateful for this
0
 
mbizupCommented:
Glad to help out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now