Link tables or use list boxes on input form?

[Ann Scharpf]

Hi,

Most of the Access databases I've created so far have been for my own use.
I've worked directly in the data tables. I am now creating a database for a
small NGO to use to track medical data for a project in Africa. I need to
create input forms for the end user to enter the data and a nice menu
structure etc.

My question concerns the best way to limit data entry to prevent errors.
For example, there is a child data form that will have fields like

ClinicID
Outcome
StudyFood
CaretakerType
PriorMalnutrutionTreatment

et cetera. For each of these fields, I need to limit the user's scope of
choices. I had set up data tables for each item and linked the appropriate
fields between the tables. When I enter data directly into the table, I am
appropriately prevented from entering an invalid code.

But I would like to have the ability on the input form to show a new data
entry person a list of their choices. I don't see a way to tell the form to
look the value up from one table but store it in another table.

Would I be better off to just create list boxes on the input form? I am
concerned that this might be difficult for them to maintain if they end up
needing to add codes. The data entry and database maintenance will end up
being done by college/medical school students who are volunteering in Malawi.
I will document the database but I don't want to make it harder than
necessary to maintain.

Obviously I am new to the whole forms concept. So far, the only forms I've
used have been switchboards to run macros for things I do often in my
databases. I've never EVER used them to enter and maintain data. I'm
googling but, if any of you could recommend some good websites to go to for
tuturials/reference, I'd really appreciate it.

Thanks so much for your help and advice.

 

[OssieMac]

Hi Ann,

Your comment: “I don't see a way to tell the form to look the value up from
one table but store it in another table.â€

Forgive me if the following instructions appear to be overkill but it is
difficult to know what level you are at.

The following is for a simple table bound to a form and you want to look up
values for a field using a combo or list box populated from another table.
(It can be more complex using queries etc.)

In Forms Design mode, click in the little square top left of form where the
ruler lines intersect and a little black square appears.

Open Properties.

Select Data tab.

Click Drop down arrow at right of Record source.

Select the table to which the form is to be bound.

Now select a combo or list box on the form. (While still in design mode).

Open properties.

Select Data tab.

Click Drop down arrow at right of Control source.

Select the required field from the bound table.

Go to Row Source Type: Set to Table/Query.

Go to Row Source: Click Dropdown at end of field and select the table with
list of valid values for field. (If more than one field in the valid values
then click the 3 dots and build a query so that the field to display is at
the left end (first field) of the query builder matrix.)

Save the form and now when you select a value from the combo or list box,
the selected value is placed in the appropriate field of the bound table.

Of course the purists will say that you should set a relationship link but
this is answering your specific question as simply as possible and even if
you decide to set a relationship it is not a lot different.

As for adding options to the table for the valid list, that can be done by
opening the table or if you want to do it by forms, place it a double click
event for the combo or listbox and open a form to do so.

 

[Ann Scharpf]

Thank you so much, OssieMac! This was EXACTLY what I needed. I've worked
with Access for a few years now, but my databases have been ones where we
download data from another system. I've set up related tables but everything
was easily handled (by me) directly in the ancillary tables. This is the
first database I'm creating where the data will be entered by hand.

I have already set up relationships between all the tables in the
relationships screen. Is this what your're referring to when you say,
"...the purists will say that you should set a relationship link?"

Thanks again for your help!