Adding Records to Many side of relationship...

  • Thread starter Thread starter Scott A
  • Start date Start date
S

Scott A

I've been scratching my head over this one for so long I
think I may draw blood...

I have some simple forms for data entry that let users add
records to a primary table, and use a linked subform to
enter maintenance records (The maintenance records are the
many side of the one-to-many relationship).

The problem comes in because we do maintenance in large
batches, and the users have to update several records in
one sitting. It's tedious for them to have to search for
each appliance, and then add the maintenance record to the
subform

I've tried experimenting with a continuous form - which
lets the users see lists of appliances that match filter
criteria on the form, but I can't add the subform to the
continuous form.

I'm wondering if it will work to take the results of the
filtered form and declare a recordset with the primary
keys, and use an append query to add these new records to
the maintenance table - if so, how do I program the form
to display rows for entering the maintenance information
for each appliance?

Any ideas or examples would be very helpful.

Thanks,

Scott A
 
I take it that the main form is appliance, the subform is maintenance
action, and it is common for an appliance to have a small number of
maintenance records to be entered - not a large number. So normally, the
user does not find an appliance then enter 50 maintenance actions. Instead,
he finds an appliance, enters a small number of maintetance actons (perhaps
just one or two), finds the next appliance, & so on. So there is too much
finding, compared to the amount of entering!

Correct? If so, the solution depends on whether the input forms do or don't
show the primary key value for the appliance in question. If they >do<, just
put an unbound textbox on the main form, where the user can enter that
primary key value. Code in the form then navigates the main form that
appliance, and moves the focus to the subform. The user enters the
maintetance record(s) then presses a hotkey to go back to the textbox &
enter the next appliance key value.

Yes? No?

HTH,
TC
 
It's a step in the right direction, but still a little
more labour intensive that what I hope I can accomplish.
There are lots and lots of appliances, and most are
maintained according to type. I can provide a search form
that shows all appliances for a range of criteria (all
appliances of x type, for x department, etc...)

The filtered list contains all of the primary key for each
appliance - I'm wanting to take the results of the query,
perhaps create a recordset(?) to which the user can add
the details of each maintenance event, and append it to
the maintenance table.

Perhaps creating a temporary table of primary keys
formatted with the same field/types as the maintenance
table, using the temporary table as a data source for a
continuous form, then running an append query to move the
updated records out of the temporary table, to the
maintenance table?

Does this sound like something that could do the trick?

Thanks,

Scott
 
I don't understand why you say my solution would be labour intensive.

The user would:
(1) Hit a hotkey to go to the unbound field on the main form;
(2) Type the appliance pk value that he reads from the next piece of paper,
then
(3) Enter the maintetance details from that piece of paper.

Presumably you can't eliminate (3). That leaves (1) & (2). How can those be
"labour intensive"?

Cheers,
TC
 
Sorry - didn't mean to imply that your solution wouldn't
work, but I've got a user population that is already
complaining that they have to move from record to record,
and insist that they don't want to have to enter the PK
and locate single records.

For that reason, I'm investigating a solution that would
allow me to generate a filtered list of equipment from the
principal table and allow users to add single maintenance
records for the filtered equipment list on a single,
continuous form that already includes the PK.
 
Back
Top