Many to Many Input Form

[djphatic]

Hi all

I am looking for a more elegant and easier/quicker form to create for inputting data into a many to many table. Here's how my tables are setup:

tblPractice:
PracticeID (PK-Autonumber)
PracticeName
...

tblEquipment:
EquipmentID (PK-Autonumber)
EquipmentName
...

tblPracticeEquipment:
PracticeEquipmentID (PK-Autonumber)
PracticeEquipmentEquipmentID
PracticeEquipmentPracticeID
PracticeEquipmentRoom
PracticeEquipmentMake
PracticeEquipmentCalibrationDate


I have a list of equipment in tblEquipment which the practice may or may not have in a room in the practice or have in multiple rooms.

At present the data is input using a continuous form where the user selects the Room and Equipment from comboboxes and other information via textboxes.

Going through some of the data that requires inputting some practices have 18 pieces of equipment in 1 or more rooms, inputting this information get's very tedious and is time consuming.

I am looking to create an input form that would list of the equipment in the equipment table and user could select the Room and then go down each piece of equipment ticking which is available in that room. Those that have been ticked, a record would be created in tblPracticeEquipment. Any additional information such as the Make or Calibration Date would then have to be input separately.

Ideally the same form would be used for editing, adding new equipment though I could manage with 2 separate forms one for initial data input and another for editing.

I have managed to achieve something similar to the above in a Report however I cannot reproduce this in a Form for data input/editing.

Please see the attachment as an example.

Any help is greatly appreciated.

 

[stopher]

I think I understand what you're after. But I'll explain what I've done for an order entry system...

So have a basic order entry form. The user can enter products on the form (continuous subform) either via the dropdown or by typing the product code. But like you say, this is tedious.

So I added a button to the header of the form. This opens a new price list form that lists ALL products and a quantity box (but this could just as easily be a checkbox). The user uses +/- either side of the box to set quantities. Obviously with a checkbox only checking/unchecking is necessary. Then there is an ADD button on the form which adds all product with greater than zero quantity to the order detail table. It then closes the price list form and returns to the order entry form (which has been refreshed to show the added products).

The way I do this is to have a table for the price list form. In my case this table is populated from my Price List table when the form is launched and the quantities set to zero (although if you have a fixed list then you only need to reset the checks).

Does that make sense? Do you need an example?

 

[djphatic]

Does that make sense? Do you need an example?

Yes that makes sense I just need to add a new field into the Equipment table to do this.

A solution I was thinking of was using a multi-select listbox and those entries selected on the listbox would be used in a similar fashion.

I will give this a go, if I get stuck I'll post back for an example.

 

[djphatic]

A couple of questions regarding your solution.

Resetting the values back to 0 (or False in my case) when the form is opened, is this done using an Update Query or VBA?

Also, I presume the way the data is added to the order detail detail is done using an Append Query?

 

[stopher]

A couple of questions regarding your solution.

Resetting the values back to 0 (or False in my case) when the form is opened, is this done using an Update Query or VBA?

Either. You you'd create an update query and then run the query from your trigger. Or you could run the SQL in VBA.

Also, I presume the way the data is added to the order detail detail is done using an Append Query?

Yes. But make sure you have some method in place to prevent the data being added more than once.

hth
Chris

 

[djphatic]

Yes. But make sure you have some method in place to prevent the data being added more than once.

The indexes in the table should prevent any duplicate records being created. However, the append query appears to have broken the Autonumber in the table.

I believe this is a bug relating to http://support.microsoft.com/?id=884185

 

[djphatic]

Turns out I had set the wrong field in the Append To part of the query, doh!

Out of curiosity, do you turn Warnings off before running the Append query in your system and then turn back on, or leave on always? If you turn the warnings off, how do you deal with notifying the user that a record already exists.