473,327 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

automatically update subforms when form is filled

2
When my database is opened, it starts with the form Patients.Page 2 of the the Patients form contains a subform called New Record. This New Record subform is where you put entries about the medications taken by a particular patient. The medications are categorized into two: current and past, depending on the specific checkbox that is checked.

What I want to happen is, when the fields in the New Record subform is filled, two other subforms below it, both in datasheet view, will be automatically updated. The two other subforms are: Current Medications subform and Past Medications subform.

In other words, if the checkbox: Current, found in the New Record subform is checked; the data/entries about that particular medication should automatically go to the Current Medications subform. Past Medications should also behave similarly.

Here's an outline:

Patients Form
Page 2: New Record subform
Current Medications subform
Past Medications subform


Details/Links/Relationships:

Patients Form (based on Patients Table)
New Record subform (based on Medications Query)
Current Medications subform (based on Current Medications Query)
Past Medications subform (based on Past Medications Query)

Current Medications Query (based on Medications Query; only shows entries that have checked Current Field; in other words, shows entries with Current Field = Yes
Past Medications Query (based on Medications Query; shows entries with Past Field = Yes)

Medications Query (based on Medications Table; has a calculated field based on two fields from Medications table)



I am using Access 2010.
I have very little background on Access so please give detailed answers.
Thanks!
Attached Images
File Type: jpg database.jpg (52.8 KB, 1192 views)
Mar 2 '14 #1
4 8564
jimatqsi
1,271 Expert 1GB
tamcq, Welcome to Bytes. You may be surprised how helpful the posters at this site will be for you. And I am surprised to see such a well formed request from a new poster. It's rare. That being said, I must add that I hope that name at the top of the form is a "test patient", not the real name of a patient.

You don't say anything about the linking of the forms. I assume from the image that you have done that correctly, since there is data in the subforms. That is, I assume you have linked the sub-forms to the main form by the patient ID field.

If the linking is okay, I think maybe you want to add a line of code to the AfterUpdate event of the main form. That kicks in after a record is added or changed. Not after a field on the form is changed, but after the update is actually written. That line of code should be:
Expand|Select|Wrap|Line Numbers
  1. Me!Subform1.Form.ReQuery
where Subform1 is the name of the sub-form container. To get the name of sub-form container, in design mode, click on the main form, then click on the sub-form 1 time, and look at the properties to get the name of the object.

And then you want to do that also for Subform2.

Jim


It looks like
Mar 2 '14 #2
jimatqsi
1,271 Expert 1GB
Note that you could add to the AfterUpdate event of an object, like the checkboxes Past and Current
Expand|Select|Wrap|Line Numbers
  1. if me.dirty then me.dirty=false
which would cause the record to be written. The only thing is you don't want to check those boxes on a new entry before enough data is entered to make a good, save-able record.

Jim
Mar 2 '14 #3
tamcq
2
Jim,

Thanks for taking the time to answer my post. I have read and tried to apply the suggestions in your first reply to my database. I really had difficulty understanding what you mean at first for I have zero background on databases, programming, and such. What I was able to do so far is just self-taught.

Here's a description of what I did:

1. From the shutter bar, I opened the New Record Form.(The New Record Form is the subform of the Patients Form).

2. In Design view, I opened the Property sheet, clicked the Event tab, and went to the cell beside the After Update property.

At first I tried typing this:
Me!Current Medications.Form.ReQuery

However, it doesn't seem to work as I hoped. After a series of trial and error, I finally came up with:
Current Medications.New Record.ReQuery
(I hope that's a proper interpretation of your suggestion.)

That seemed to work to a certain degree because now, when I edit textboxes or fields of a particular medication regimen, and click save, the Current Medications subform below the page is updated automatically.
For example, I changed the dosing schedule of Amoxicillin trihydrate from 1 cap bid(twice a day) to 1 cap tid(three times a day). After clicking the save button, the changes appeared on the Current Medications subform below.

However, another problem came to notice.

Whenever I want to add a new medication regimen for a particular patient, and clicked the add new record button (the button with an image of a pencil; located beside the save button), the textboxes go blank to permit me to add another set of entry, but the Current Medications subform and Past Medication subform below also become empty.

The entries aren't actually gone, i know this because when I open everything again, the previous entries plus the new entry is shown in the subforms.

How to remedy this?

Thanks again!

-tamcq

PS. the medication record in the attached image is imaginary.
Mar 8 '14 #4
jimatqsi
1,271 Expert 1GB
Well, tamcq, you have done very well and taught me something in the process. I didn't realize you could type that command into the event property like that. In fact, this makes me realize I need to explore the content of the drop-down list that is available in the events property. I always just take the top member of the list. Well done!

I would have chosen the top member of the list, [Event Procedure] and clicked the ... to the right. That would launch the Visual Basic editor. There I would have typed the command.

The other behavior is normal and correctable. Think to the fact that the sub-forms are populated in accordance with the linkage in effect from the main form. When you start a new record there is nothing in the data item (Patient ID and Medication ID?) that should tell the sub-forms what data to show.

There are events on the form for Before Insert and After Insert. Before Insert is fired when you touch the new row. In that event you can put something like me.PatientID= the patient ID from your main form, plus whatever other defaults you can assume. But since there is no record to be shown for the new medication until after you finish your entry, there is nothing to be alarmed about.

You're doing very well. It's tough in the early going, but sticking with it and asking for help as needed will get you where you want to go.

Jim
Mar 8 '14 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Rose | last post by:
I have a database table with fields Proficiency Due Date and Proficiency Recvd Date. The Proficiency Due Date is already populated. When I enter the Prof Recvd Date, I want to automatically add...
30
by: Charles Law | last post by:
Here's one that should probably have the sub-heading "I'm sure I asked this once before, but ...". Two users are both looking at the same data, from a database. One user changes the data and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.