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

Making a sub sub form in Access 2010 stable

Hi

I have a form - mFrmPatientDetails (with combo box). User selects patient from combo box.
Each patient has a treatment (with multiple sessions) This is the sFrmTreatmentdetails.  Each treatment has an ID (autonumber). (PK) It is linked to the mFrmPatientDetails via PatientID.
This is the first sub form. Then this form has a sub-form (sessions per treatment).
It is linked to the sFrmTreatmentdetails.via PatientID and Treatment ID. What I would like help with is what do I need to add to (a) the treatment form to make it stable (b) to ensure the combo box always reflects the current patient - to ensure the correct patient is updated (c) How can I make sure that the right treatment is applied to the right patient and the right session details applied to the right treatment & patient. Am attaching a file with my current work  sub-sub-form-example.accdb
0
agwalsh
Asked:
agwalsh
  • 5
  • 5
  • 3
1 Solution
 
mbizupCommented:
It looks like you are very close - your links between main, sub and sub-sub form are good, and that is what really makes these things 'stable'.

The combo box on the main form is accurately displaying the patient's name when it is used to change records.  It doesn't display the correct name when the form is opened or when mainform records are changed using the record selectors at the bottom of the form.  To cure that, use the following code in the main form's current event:

Private Sub Form_Current()
    Me.Combo9 = Me.PatientID   '<--- You should give your combos, etc more meaningful names
End Sub

Open in new window


Other issues -

- You should not allow the users to edit the patient ID field inthe subform.  Hide it or disable it.

- If you hide it altogether, you can display the patient's name in a read-only manner.  The treatment ID label caption might be a good place to do this, and can be done in the current event of your subform as follows:

Private Sub Form_Current()
     Me.lblHeader.Caption = Me.Parent.PatientName & " Treatment ID:"
End Sub

Open in new window


Apart from that, it looks good (and stable).  See the sample-
sub-sub-form-example.accdb
0
 
als315Commented:
You can use default value for your fields in sub forms.
See PatientID in example sub-sub-form-example.accdb
0
 
agwalshAuthor Commented:
@mbizup...had a look at your file and yes, combo MUCH MUCH better. Thank you so much. Will amend as per your other suggestions..Couple of other things..

1. Would it be better to have a Treatment ID (PK for this table), not visible to user and then a treatment number field - set to increment automatically - using some sort of Dmax +1 variation rather than the Treatment ID I am using?
2. Ditto for the Session number - which is left blank at the moment. So that when the user clicks on Add New session, it increments for that session for that treatment..
Thanks for your help!
Anne
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mbizupCommented:
1 & 2 - It depends on whether the ID is of importance to the end user.  When possible, I try to avoid displaying autonumbers in favor of other identifying fields such as date/timestamps or other fields that uniquely identify a record in a way that make intuitive sense to the end user.  If you have a "numbering scheme" for these IDs, then yes - display that instead of the autonumber.

The autonumber field is very useful from a developer's standpoint, but since it is not defined through user data entry, it is almost always meaningless (and potentially confusing when gaps appear from deleted records)  to the end user.
0
 
mbizupCommented:
Another recommendation when working with fields like that - if they are displayed to the user, set the following properties on their textboxes:

1.  Transparent border
2.  Locked = yes
3.  Enabled = no
4.  Transparent background

That will make them display like a label, making it clear to the end user that the data is informational, not editable.
0
 
agwalshAuthor Commented:
@mbizup - thanks a million for that about the autonumber fields. They don't need to see them so I have hidden them all. What is visible to them now is the numbers that are meaningful for them.

I've been working with the Dmax for the treatment number first and what I have done is as follows..

What I want is that (a) when the user opens the form, chooses the patient, the most recent treatment number should appear in the treatment section and that when they click on the New treatment - the TreatmentNumber field (not an autonumber or PK) will increment automatically by 1. What I have at the moment is:
in the following (in the subform)
Private Sub Form_BeforeInsert(Cancel As Integer)
TreatmentNumber = Nz(DMax("TreatmentNumber", "tblTreatmentDetails", "PatientID=" & Forms!MFrmPatientDetails!PatientID)) + 1
End Sub
But there is no number appearing and I think I have got the bit about the PatientID wrong... any suggestions much appreciated!  Also how can I make sure this number gets into the underlying table tblTreatmentDetails?
And since I want to replicate a version of this for the session number and this is in a sub sub form - what do I need to take account of.  hyperbaric03.accdb
0
 
als315Commented:
Test it (event OnCurrent)
hyperbaric03.accdb
0
 
agwalshAuthor Commented:
I really appreciated the community help on this one..I got 90% of what I needed and the other bit only arose from the solutions given.. thanks again!
0
 
mbizupCommented:
agwalsh,

I'm glad to have helped out, but am curious why you didn't split the points?  I believe we both helped out here.
0
 
agwalshAuthor Commented:
@mbizup...my apologies. I didn't realise that I could split the points - I should have done so. This is one of my first postings here. Let me know how I can rectify it for you .. A
0
 
mbizupCommented:
The question closing system can be confusing, even to people who have been here for a while.

Anytime you need any administrative help with a question, you can use the Request Attention button and the Mods will help you out.

Also, if you click on the 'Help' tab, there is some great information about how things work around here.  It's well worth reading in its entirety to help you get the most out of your membership.
0
 
als315Commented:
I'm with mbizup.
0
 
agwalshAuthor Commented:
@mbizup and @als315 - again apologies for that . Back to the RTFM basics really for me :-)
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now