Form auto-fill linking

[Kevin]

Hi,

I am very new to Access (2003) and don't have any familiarity with coding. I
am trying to develop a form for entering chart information for a large number
of patients. Each patient has a unique record number associated with him/her
("Patient Information" table), and one to several visits. Each visit of each
patient has its own chart ID number ("Patient Visits" table), which in turn
is associated with the chart information for that particular visit ("Chart
Information" table).

I would like to be able to enter the record number of a patient into a form
and have the form retrieve all of the visit dates associated with that
patient. Then, I'd like to be able to select a specific visit date (ideally
from a drop down list) to bring up the chart information categories so that I
can enter data to be stored in the Chart Information table. If the record
number of the patient does not already exist in the database, then I'd like
to be able to add it as a new record and continue on to add a corresponding
visit date and the chart information from said date (to be stored in their
respective tables). Any advice/direction would be greatly appreciated; thank
you in advance for whatever you can offer.

 

[Steve Schapel]

Kevin,

One of the standard approaches to this type of scenario would be to make
a continuous view form based on the Patient Visits, and place this as a
subform on the Patient Information form. The Link Master Fields and
Link Child Fields properties of the subform would ensure that when you
open the main form at the record for a specific patient, you only see
the Visits data for that patient.

I am puzzled by the distinction between Patient Visits and Chart
Information. Is there more than one Chart record for each Visit? If
so, can you give an example? If not, shouldn't these two tables be
combined?

 

[Kevin via AccessMonster.com]

Steve,

Thanks for your response. You're correct in that each patient visit
corresponds to only one set of chart information. I have a many-to-many
relationship between patients and dates when they have visited, but I was
unable to get a functional junction table to work between the two. As such, I
assigned each visit (combination of patient number with one date) a unique
number which I then related to the chart information for that visit.

I've been able to successfully add a patient visits subform to my patient
information form, however, I still can't view the specific chart info
associated with each visit date. Also, is there any way that I can change the
control regarding the patient ID number so that it will bring up all of the
information associated with an already existing ID (or create a new record if
it is a new ID)? Thanks again for your help.

Kevin

Kevin,

One of the standard approaches to this type of scenario would be to make
a continuous view form based on the Patient Visits, and place this as a
subform on the Patient Information form. The Link Master Fields and
Link Child Fields properties of the subform would ensure that when you
open the main form at the record for a specific patient, you only see
the Visits data for that patient.

I am puzzled by the distinction between Patient Visits and Chart
Information. Is there more than one Chart record for each Visit? If
so, can you give an example? If not, shouldn't these two tables be
combined?

Hi,

[quoted text clipped - 16 lines]

respective tables). Any advice/direction would be greatly appreciated; thank
you in advance for whatever you can offer.

 

[Steve Schapel]

Kevin,

.... As such, I
assigned each visit (combination of patient number with one date) a unique
number which I then related to the chart information for that visit.

I think you have misunderstood the concept of a many-to-many
relationship. What you have here is not a valid design for your tables.

All you need is your Patient Information table, plus a Visits (or
whatever you call it) table. This second table will have a VisitDate
field, along with all the associated Chart information. If you need to
see a list of all the visit dates for a particular patient, or for each
patient, this is easily obtained via a query.

 

[Kevin via AccessMonster.com]

Steve,

Thanks again for your response; I've changed things around so that they're
more directly related. I'm now able to type a patient number into a combobox
which will then bring up all of the visit dates associated with that number
in another combobox. Is there any way to relate the selection of a date from
this list with a subform that contains the chart information? The main form
is based on the patient information.

 

[Steve Schapel]

Kevin,

Please give details of the tables as you now have them, and also how you
have the Row Source of the visit dates combobox.

Also, where are these comboboxes? I am imagining they are in the Form
Header section of the Patient form?

 

[Kevin via AccessMonster.com]

As of now, I have a table (Patient Information) which contains contact
information for each patient. A unique record number from this table is
related to a second table (Patient Visits) in a one-to-many relationship. The
second table contains the patient number, their dates of visit, and the chart
information associated with each visit. Each combo of patient number and date
has its own chart ID, which serves as the primary key.

Both the combobox (Combo12) for looking up the record number as well as the
dates of visit combobox (Combo16) are located in the Form Header of the
patient information form. They are both unbound. The Detail section of the
form contains the bound controls for patient record number and patient name
(I have an independent form for adding contact information). Within the
detail section I also have a subform based on the Patient Visits table that
includes chart ID, Date of Visit (bound), and all of the chart information
associated with that visit.

After looking on this website for a while, I found this code for the combobox
relationship. It is included in the After Event for the record number
combobox:

Private Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Medical Record #] = '" & Me![Combo12] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo16.RowSource = "SELECT [Patient Visits].[Date of Visit] FROM [Patient
Visits] WHERE [Patient Visits].[Medical Record #] = '" & Me.Combo12 & "';"
End Sub

This accurately brings up the correct options of dates of visit in the second
combobox. However, while I can view the dates, I can’t actually select one
and have it populate the combobox (the combobox has two columns, date of
visit and chart ID number). I was hoping to use the chart ID number in the
second column of Combo16 as the source for the information presented in the
subform.

Right now, the subform accurately pulls up the information associated with
whatever patient number I enter into the lookup combobox. I can scroll
through different dates using the record navigation buttons at the bottom of
the subform, but I would prefer to use the DOV combobox to select different
records (as I am attempting to design this so that people with no familiarity
to Access can use it to enter information).

Sorry for the long post, and thank you very very much for your help.

Kevin,

Please give details of the tables as you now have them, and also how you
have the Row Source of the visit dates combobox.

Also, where are these comboboxes? I am imagining they are in the Form
Header section of the Patient form?

Steve,

[quoted text clipped - 4 lines]

this list with a subform that contains the chart information? The main form
is based on the patient information.

 

[Steve Schapel]

Kevin,

Thank you very much for the beautifully clear explanation. You are on
the right track.

It is basically that AfterUpdate code that is messing things up. You
say "the combobox has two columns", but the effect of the code, which
resets the RowSource property of the combobox, is to change that to only
one column.

Is the Medical Record # field really a text data type?
(By the way, as an aside, it is not a good idea to have a # as part of
the name of a field or control.)

Ok, here's how I would do it...

1. Make a new Query, based on the Patient Visits table. Just the Chart
ID, Date of Visit, and Medical Record # fields (in that order).

2. In the Criteria of the Medical Record # column in query design,
enter this...
[Forms]![Patient Information]![Combo12]

3. Save this query, and then enter this query as the Row Source
property of the Combo16 combobox.

4. Set these properties of the Combo16 combobox:
Column Count: 2
Bound Column: 1
Column Widths: 0;x
(where x is whatever number of inches or centimetres you need to display
the date)

5. Change the AfterUpdate code on Combo12 to this:
Private Sub Combo12_AfterUpdate()
Me![Medical Record #].SetFocus
DoCmd.FindRecord Me![Combo12]
Me.Combo16.Requery
End Sub

6. In design view of the Patient Information form, look at the
Properties of the subform. Set the Link Child Fields property to:
[Chart ID]
Set the Link Master Fields property to:
[Combo16]

7. Let us know whether that does the trick for you.

--
Steve Schapel, Microsoft Access MVP

As of now, I have a table (Patient Information) which contains contact
information for each patient. A unique record number from this table is
related to a second table (Patient Visits) in a one-to-many relationship. The
second table contains the patient number, their dates of visit, and the chart
information associated with each visit. Each combo of patient number and date
has its own chart ID, which serves as the primary key.

Both the combobox (Combo12) for looking up the record number as well as the
dates of visit combobox (Combo16) are located in the Form Header of the
patient information form. They are both unbound. The Detail section of the
form contains the bound controls for patient record number and patient name
(I have an independent form for adding contact information). Within the
detail section I also have a subform based on the Patient Visits table that
includes chart ID, Date of Visit (bound), and all of the chart information
associated with that visit.

After looking on this website for a while, I found this code for the combobox
relationship. It is included in the After Event for the record number
combobox:

Private Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Medical Record #] = '" & Me![Combo12] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo16.RowSource = "SELECT [Patient Visits].[Date of Visit] FROM [Patient
Visits] WHERE [Patient Visits].[Medical Record #] = '" & Me.Combo12 & "';"
End Sub

This accurately brings up the correct options of dates of visit in the second
combobox. However, while I can view the dates, I can’t actually select one
and have it populate the combobox (the combobox has two columns, date of
visit and chart ID number). I was hoping to use the chart ID number in the
second column of Combo16 as the source for the information presented in the
subform.

Right now, the subform accurately pulls up the information associated with
whatever patient number I enter into the lookup combobox. I can scroll
through different dates using the record navigation buttons at the bottom of
the subform, but I would prefer to use the DOV combobox to select different
records (as I am attempting to design this so that people with no familiarity
to Access can use it to enter information).

 

[Kevin via AccessMonster.com]

Steve,

Thanks again, and sorry for my delayed response (I've been a little under the
weather). When I try to enter the form view (leaving design view or just
opening the form), I immediately receive a prompt titled "Enter Parameter
Value" with Combo16 and a text box for me to enter a value. If I cancel the
prompt, my subform just turns up as a big blank box, and I can't access
anything from Combo16. The autocomplete from Combo12 still works (it pulls up
the MR # and name in the main form).

If I enter a date into the prompt, I am able to view the subform. However,
there is no data in it, and the main form data that appears is not related in
any way to the date that I entered.

If I enter a Chart ID number into the prompt, the subform pulls up the
information for that Chart ID number regardless of whether or not it is
supposed to be associated with what is being viewed on the main form. Doing
this does not change the state of Combo16, which displays one empty box upon
clicking the arrow). Entering a value for Chart ID number brings up the
message that "The text you entered is not an item from the list, or enter
text that matches one of the listed items." Any suggestions?

Kevin,

Thank you very much for the beautifully clear explanation. You are on
the right track.

It is basically that AfterUpdate code that is messing things up. You
say "the combobox has two columns", but the effect of the code, which
resets the RowSource property of the combobox, is to change that to only
one column.

Is the Medical Record # field really a text data type?
(By the way, as an aside, it is not a good idea to have a # as part of
the name of a field or control.)

Ok, here's how I would do it...

1. Make a new Query, based on the Patient Visits table. Just the Chart
ID, Date of Visit, and Medical Record # fields (in that order).

2. In the Criteria of the Medical Record # column in query design,
enter this...
[Forms]![Patient Information]![Combo12]

3. Save this query, and then enter this query as the Row Source
property of the Combo16 combobox.

4. Set these properties of the Combo16 combobox:
Column Count: 2
Bound Column: 1
Column Widths: 0;x
(where x is whatever number of inches or centimetres you need to display
the date)

5. Change the AfterUpdate code on Combo12 to this:
Private Sub Combo12_AfterUpdate()
Me![Medical Record #].SetFocus
DoCmd.FindRecord Me![Combo12]
Me.Combo16.Requery
End Sub

6. In design view of the Patient Information form, look at the
Properties of the subform. Set the Link Child Fields property to:
[Chart ID]
Set the Link Master Fields property to:
[Combo16]

7. Let us know whether that does the trick for you.

As of now, I have a table (Patient Information) which contains contact
information for each patient. A unique record number from this table is

[quoted text clipped - 40 lines]

records (as I am attempting to design this so that people with no familiarity
to Access can use it to enter information).

 

[Steve Schapel]

Kevin,

The most likely cause of this problem is a misspelling. Is Combo16 the
exact name of the combobox? And in the Link Master Fields property of
the subform, you have it exactly like [Combo16]? And just to check that
I have understood correctly, the Combo16 combobox is on the main form,
the same form that the subform is on?

 

[Kevin via AccessMonster.com]

Steve,

Combo16 is the exact name. Both Combo12 and Combo16 are located on the main
form (based off of patient information), however, they are located in the
Form Header rather than in the Detail subsection. Could this be causing the
problem?

I just double-checked all of the entries, and I found that I had not had
"Chart ID Number" in [] in the Link Child Fields. After correcting this, I no
longer get a prompt when I enter the form from design view. However, whenever
I enter a value into the patient number lookup box (Combo12), I get a prompt
asking for a value for Combo12 (as based off of the script you had me include
in the criteria for the query. Also, I still can't view any dates in Combo16
after entering a value in the prompt box.

Kevin,

The most likely cause of this problem is a misspelling. Is Combo16 the
exact name of the combobox? And in the Link Master Fields property of
the subform, you have it exactly like [Combo16]? And just to check that
I have understood correctly, the Combo16 combobox is on the main form,
the same form that the subform is on?

Steve,

[quoted text clipped - 17 lines]

message that "The text you entered is not an item from the list, or enter
text that matches one of the listed items." Any suggestions?

 

[Kevin via AccessMonster.com]

Steve,

Never mind my previous post. In the query criteria that you specificied you
referred to the form "Patient Information." That is not the title of my form
with Combo12. When I changed that portion of the criteria to accurately
reflect the title of my form, everything seemed to fall into place. As far as
I can tell, everything is working the way that I had hoped. Entering a value
into Combo12 accurately pulls up the correct dates in Combo16; the selection
of one of these pulls up the correct information for the subform. Thank you
VERY much for your assistance, I really appreciate it.

Kevin

Steve,

Combo16 is the exact name. Both Combo12 and Combo16 are located on the main
form (based off of patient information), however, they are located in the
Form Header rather than in the Detail subsection. Could this be causing the
problem?

I just double-checked all of the entries, and I found that I had not had
"Chart ID Number" in [] in the Link Child Fields. After correcting this, I no
longer get a prompt when I enter the form from design view. However, whenever
I enter a value into the patient number lookup box (Combo12), I get a prompt
asking for a value for Combo12 (as based off of the script you had me include
in the criteria for the query. Also, I still can't view any dates in Combo16
after entering a value in the prompt box.

Kevin,

[quoted text clipped - 9 lines]

 

[Steve Schapel]

That's excellent, Kevin, I am happy to know that it's working for you
now. Sorry to have presumed the name of your form. Please take a few
minutes to review the process we have used here, so you understand the
principle behind it, as you will certainly want to do something similar
again in the future.