How can I insert data on a subform once it is on my form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database to keep track of our Consumers and Home Health
Aides. I have my tables created and my form for the Consumers. I thought I
had my subform for the HHA done as well, but it will not work. It keeps
telling me the record already exists. How can I get it where I can keep
track of what HHA worked for what Consumer, when, and for how long and have
it show on the Consumer form?
 
Hi, Artemis.

There is a natural one-to-many relationship between Consumers and Health
Assistance events. This suggests a table for Consumers, a table for Health
Assistants, and a table for each "event". Each should have a numeric primary
key (PK). An AutoNumber is the easiest.

The many side should have a numeric field corresponding to the one side's
primary key, called a foreign key (FK). This foreign key relates the many
side records (the events) to the one side (the consumer).

Something like:

Consumers
-----------------
ConsumerID AutoNumber (PK)
FName Text
LName Text
Address
etc.

HealthAssistants
--------------------
HealthAssistantID AutoNumber (PK)
FName
LName
etc.

AssistanceEvents
------------------------
AssistanceEventID AutoNumber (PK)
ConsumerID Integer (FK to Consumers)
HealthAssistantID Integer (FK to HealthAssistants)
StartDate Date/Time
EndDate Date/Time

You'll need a main form, which you have, based on Consumers, and a subform
based on AssistanceEvents. When you insert the subform, Access may prompt
you to enter the LinkMasterFields and LinkChildFields properties; if it
doesn't, change them manually. These are the fields in the RecordSource of
the main and subform, respectively, that have the matching data--the
ConsumerID.

Hope that helps.
Sprinks
 
I am creating a database to keep track of our Consumers and Home Health
Aides. I have my tables created and my form for the Consumers. I thought I
had my subform for the HHA done as well, but it will not work. It keeps
telling me the record already exists. How can I get it where I can keep
track of what HHA worked for what Consumer, when, and for how long and have
it show on the Consumer form?
Click to expand...

Since we have no trace of any idea how your tables or forms are
structured, all I can say is that you just need to structure them
correctly.

One key concept that might be missing here is - you cannot insert data
in a Subform. A Subform or Form is JUST A TOOL, a window which
displays data from a Table or a Query. You can *use* a subform to
insert data into a table, but the data is stored in the table, and
noplace else!

A Form has a "Recordsource" property which specifies the table (or
query) containing the data to be displayed. It would seem that one or
other of your forms has an incorrect recordsource.

Please post back describing your tables, how they are related, and the
Recordsource properties of your forms.


John W. Vinson[MVP]
 
Back
Top