?
Solved

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

Posted on 2011-10-25
11
Medium Priority
?
382 Views
Last Modified: 2012-06-22
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
Comment
Question by:agwalsh
  • 7
  • 4
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37023743
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37023791
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37023797
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:agwalsh
ID: 37023988
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37024004
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
 

Author Comment

by:agwalsh
ID: 37024152
@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
 
LVL 61

Expert Comment

by:mbizup
ID: 37024179
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
 

Author Comment

by:agwalsh
ID: 37036757
@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
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 37040593
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
 

Author Closing Comment

by:agwalsh
ID: 37057936
Worked like a dream - am most grateful for this
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37060242
Glad to help out.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question