[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Making a sub sub form in Access 2010 stable

Posted on 2011-10-21
13
Medium Priority
?
277 Views
Last Modified: 2012-05-12
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
Comment
Question by:agwalsh
  • 5
  • 5
  • 3
13 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37005667
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
 
LVL 40

Expert Comment

by:als315
ID: 37005668
You can use default value for your fields in sub forms.
See PatientID in example sub-sub-form-example.accdb
0
 

Author Comment

by:agwalsh
ID: 37005942
@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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 61

Expert Comment

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

Expert Comment

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

Author Comment

by:agwalsh
ID: 37017053
@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
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 37022434
Test it (event OnCurrent)
hyperbaric03.accdb
0
 

Author Closing Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

by:mbizup
ID: 37023399
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
 
LVL 40

Expert Comment

by:als315
ID: 37023568
I'm with mbizup.
0
 

Author Comment

by:agwalsh
ID: 37023651
@mbizup and @als315 - again apologies for that . Back to the RTFM basics really for me :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

865 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