Tie two combox box controls to different lookup tables on same for

[Pat Dools]

Hello,

Is it at all possible to have (on the SAME form) one combo box which, when a
member is chosen from the drop-down list, three patient ID-related text box
fields are filled in via a lookup to a table, and ALSO have another combo box
which behaves the same way, but the resulting three Clinical research
formID-related text box fields that are filled out actually look up to
ANOTHER table?

The reason I ask is because I would like to carry these six fields over to
the header section of some data entry forms I am using in my application so
that the data entry person doesn't have to fill these 'header' fields in for
each form filled out for a given patient. What makes this tricky is that
data entry people will have the flexibility to enter new Patients IDs, etc.
as needed, but will NOT be able to enter in new clinical research form IDs,
etc.-- they will be limited to what is in that drop-down.

Right now, when a Patient is chosen, then I get '#Name?' in the Clinical
research formID-related text box fields, or if I choose a certain clinical
research form, I get '#Name?' in the PatientID-related text box fields. I
understand that the form is expecting that there be a 'match' between
whatever Patient-related info. is chosen and a clinical research form record,
but I need the flexibility to be able to choose a patient record (or create
one) and then choose what clinical research form I want.

The way I have it set up in an older version of the database is to have one
form with just the Patient ID info. on it. Once the Patient ID info. is
filled out, then the user has to click a button to launch the Clinical
Research Form ID form, where they fill in that info. Then, finally, they
launch (again via a button) whatever clinical research form they actually
have to fill out. The 'header' info. regarding both Patient ID info. and
Clinical Research Form ID info. is automatically filled out. Once they fill
out the rest of the form, they have to close out and either navigate back to
the Patient ID info. form, or the Clinical Research Info. form, and it is all
very clumsy. If they could somehow fill out Patient ID info. and Clinical
Research ID info. and then launch the actual clinical research form to fill
out, that would be ideal. Then, once they fill out the rest of the actual
form, they will be brought back to one place where they can choose which
patient ID and which clinical research form ID to fill out. Am I basically
asking for the impossible here?

Thanks.

 

[Ken Snell [MVP]]

See if this article helps:
http://www.mvps.org/access/forms/frm0058.htm

 

[Pat Dools]

Hi Ken,

Should I leave the Record Source of the form blank instead of pointing it to
a 'Lookup' table-- in other words, have only the combo box(es) be the only
things looking up to my lookup tables?

Thanks.

 

[Ken Snell [MVP]]

That depends upon the purpose of the form, and its design.

If the form is meant to just display data based on a selection in a combo
box, and the form is in "Single Forms" view (meaning only one record at a
time would be shown), then it may be best to not have a Record Source for
the form and just use the combo boxes to get the data from the lookup
tables. This design also would mean that the form could not be used to
edit/store data.

If you want to change the data that are being displayed, or other actions
involving the data (including saving the data or adding new data), then you
will need a Record Source for the form.

I've reread your original post, but I don't understand sufficiently what
you're wanting to do here -- if it's just display or if you are wanting to
add new data. If you can post back with more details about what you're
doing, and the use/purpose of the data that are coming from the combo boxes,
then we may be able to assist you further on this question.

--

Ken Snell
<MS ACCESS MVP>

 

[Pat Dools]

Hi Ken,

At the most general level, this is a clinical research database in which we
are keeping track of which clinical research forms (CRF's) have been entered
for which patient. So we need to identify both patients and various clinical
research forms to be able to say whether or not a given CRF has been
completed for a specific patent.

I have the data entry person come to a 'startup' screen which consists of
two sections, a 'Patient ID info.' section and a Clinical Research from ID
info.' section. Ultimately, I need info. from the fields on this form to
carry over to a 'CRF Status' form (where these also fields exist in the
Header Section), where additional info. about whether the CRF has been
received, entered, etc. is then completed.

For the Patient ID info., piece (which consists of Patient ID, initials, and
Site ID they come from), we need the flexibility to choose existing records
or add new records. The Patient ID info. is stored in its own table and will
be allowed to grow as new patients are entered. For the Clinical Research
form ID piece (which consists of Form number, Form Name, and study stage), we
simply will be choosing from an existing list, and the data entry person will
NOT be able to add to it-- it is limited to that list. The Clinical Research
form ID info. is stored in its own separate table.

Following your article, it seems to be doing what I want it to do (as
mentioned above), which is to carry the Patient ID info. and the CRF ID info.
fields onto the 'CRF Status' form. For now, I have BOTH lookup tables
(Patient ID info. and Clinical Research form ID info.) listed as the Data
Source for this 'startup' form. I just wanted to see if it was unrealistic
to have some fields that could combine to choose existing records OR input
new ones into one Lookup table (Patient ID info.) and have another set of
fields on the same form that would simply look up to another table (Clinical
Research form ID info.). So far, it does seem to be behaving the way I need
it to. Please let me know if there are any 'red flags' in my logic that I
should be thinking about. I usually only have one table as a given Form's
Data Source. This is the first time that I've tried to have some fields on
the form who's Control Source looks up to one lookup table (with the
flexibility to add to that lookup table), and another set of fields on that
same form who's Control Source that ONLY does a lookup to another table.
Thank you for your assistance and hopefully I've clarified myself here.

Thanks.

 

[Ken Snell [MVP]]

Having textboxes whose Control Sources "look up" data from different tables
is not a problem, so long as it's recognized that those textboxes cannot
also be bound to a field in the form's RecordSource and thus store their
data directly.

Using more than one table in a query for a form's RecordSource is done in
many cases where each table is having only a single record updated/added.
Note, however, that such situations can easily lead to "nonupdatable"
queries, meaning that ACCESS cannot identify a unique record that is to be
added or edited. In these nonupdatable query cases, you will not be able to
use the form for editing or data entry.

From your description, it appears that you want to use combo boxes and text
boxes to select existing data records so that you can add new data related
to those records. This is a common activity within ACCESS -- it's typically
done in one of two ways:

(1) Put these "find the data" controls in the form's FormHeader section
(all these controls would be unbound), and then use their values to "move"
the form to the desired record (usually a "child" table) for editing or new
data entry. In this case, the form usually will be based on a single table,
but not always.

(2) Put these "find the data" controls on a form (all these controls
would be unbound), , and then click a command button that opens another form
that displays the existing data records (usually from a "child" table
related to the data) based on the values entered, or that allows entry of a
new data record based on the entered values. This often is done via using
the "WhereCondition" of the DoCmd.OpenForm method in VBA code, which allows
you to "filter" the data in that opened form to just the record(s) desired.

In general, the applicability or purpose of a form is driven by the
RecordSource query and the bound controls on that form.

So, it appears that you're using the combo boxes to select existing "parent"
records (PatientID), and displaying "related" data for that patient on a
form. From there, you then want to be able to edit existing data or add new
data. I assume that these data to be edited or added are for "child" tables
(e.g., a new research result from that patient, a new test result, etc.), so
I probably would use the "startup" form as a way to select and confirm that
I'm selecting the correct patient, and then would click a button to take me
to the proper form for editing/adding data. I wouldn't do it (based on what
I'm understanding) on the startup form.

You may find that you'll need more than one form to do data editing/entry
because of the possibility of the "nonupdatable" queries that may result
from many-table queries. But, you will need to discover that as you
design/develop the database.

In summary, what you're desiring to do is not unusual, but there is more
than one way to accomplish it.