Checkbox and create new record

[Guest]

Hello,

I'm at a total loss as to handle the following:

I have a search form that when you put in a unit nbr and component code it
brings up the parts related to that particular unit and component code (works
great).

Now what they want to do is pick/check which parts are required (from the
search form), create a new record in "tblPickTickets" and then print the Pick
Ticket with the parts they ticked off. Is this possible to do? I know I
don't want to store redundant information in different tables but I'm not
sure how to go about doing all this.

Tables are:

tblPartsList
PK-PartsListId

tblEquipmentParts
PK-EquipmentPartsId
FK-PartsListId

tblPickTickets
PK-PTId
FK-EquipmentPartsId ????
I assume I should relate this table to tblEquipmentParts (is that correct?)

Any help/suggestions would be greatly appreciated.

TIA

 

[tina]

sounds like you have a many-to-many relationship between tblEquipmentParts
and tblPickTickets, as

one pick ticket may have many equipment parts AND one equipment part may be
assigned to many pick tickets.

to model a many-to-many relationship in Access, you need a "junction" table
(also referred to as a linking table or join table), as

tblTicketParts
PK - PTId
PK - EquipmentPartsId
<these two fields are used as a combination primary key>

there is a one-to-many relationship between tblPickTickets and
tblTicketParts, as

one pick ticket record may be related to many ticket parts records BUT each
ticket part record is related to only one pick ticket record.

there is also a one-to-many relationship between tblEquipmentParts and
tblTicketParts, as

one equipment part record may be related to many ticket parts records BUT
each ticket part record is related to only one equipment part record.

now that the tables are set up and related correctly, you can use your
search form to select the equipment parts records you want, then append
those records to tblTicketParts. based on the limited information available,
i would probably set up a form to create the record in tblPickTickets, then
add an *unlinked* subform control to hold your search form. once the user
checkmarks the parts needed from the subform list, then you can run an
Append query to append the parts records to tblTicketParts, making sure you
include the PTId field value from the current record in the main form.

to print the pick ticket with it's "child" parts records, create a query
that pulls the fields you need from all three tables (tblPickTickets,
tblTicketParts, and tblEquipmentParts), and base your report on that query.

hth

 

[Guest]

Hi Tina,

Thanks so much for taking the time to reply. I will try what you have
suggested and will let you know how it goes (My head is spinning right now so
give me a day or two to let you know).

I apologize for giving such limited information, but I'm always afraid that
if I'm too long winded the readers will get too confused or bored and just
ignore my posts. Sorry.

Again Thanks so much and I'll keep you posted. Have a great day.

 

[Guest]

Hi again Tina,

I apologize for the delay in getting back to you. I finally got this to
work as you had suggested. Thanks so much for your expert advice. I really
appreciate it.

Have a great day.

 

[tina]

you're welcome, glad it worked for you.

Hi again Tina,

I apologize for the delay in getting back to you. I finally got this to
work as you had suggested. Thanks so much for your expert advice. I really
appreciate it.

Have a great day.