Many:many data entry form

[slimjen]

All, I'm using Access 2016..I have a database that the user want to add a table to enter and keep track of incidents. The table was created but because it was related to an existing table in the database, I had to create a junction table because of it's m:m relationship. I need design advice on how to create a data entry form for incidents for the users to create and tie multiple defectids from the other table in which it has the m:m relationship...in other words..create the incident then be able to select multiple defects tied to this incident and store them into the perspective tables...I was thinking I need to create a listbox to be able to select two or more items but that wouldn't work unless the field held multiple values. Maybe a datasheet view to input multiple line items...I don't know...any suggestions would be appreciated to point me in the right direction..

[June7]

Use a form/subform arrangement.

Main form bound to Incidents.

Subform bound to IncidentDefects.

Combobox in subform to select Defect.

What are 'perspective tables'? Why plural?

[slimjen]

Thanks for responding...perspective tables..wrong choice of words...data entry into the tables Incident and incidentdefects. I will try this but would I need to put the subform in datasheet veiw and where should the combobox be? Trying to envision this setup...

[orange]

Can you post a jpg/png of your tables and relationships?

seems

--- Incident-->IncidentHasDefect<----Defect

[June7]

Subform can be in any display view (Single, Continuous, Datasheet).

The combobox would be placed wherever you want on subform.

You need to build two forms: one bound to Incidents and one bound to IncidentDefects. IncidentDefects form will be used as subform sitting on Incidents form.

This is basic Access design and functionality.

[slimjen]

I will in a couple of hours...after my meeting

[slimjen]

attached is my diagram..

[ssanfu]

Note that "Case" is a reserved word and shouldn't be used for object names.

[slimjen]

So, I got the form done based on all your suggestions..I have the subform working with the cbo box so the user can select one or more defects...but, I realized I needed the defect filtered based on the case defect field..so I added a field called case defects on the form with a cbo box to be selected before the defect cbo...the user has to select the defect based on the case defects selection...so, I added the case defects field in the defect cbo query n set the criteria based on the forms selection i.e. forms!frm......
The defect cbo box worked before but since I added the criteria so it would only return defects based on the case defect filter now it only works if I have the subform in single view not datasheet view..I need it in datasheet view because there can be many defects to one issue n vice versa...can anyone tell me what I did wrong pls. Thanks

[June7]

Cascading combobox is common topic. Requires VBA code to Requery combobox when moving to new record and when changing the case value.

Be aware cascading combobox with alias doesn't work nice with Continuous and Datasheet because when combobox list is filtered alias values are not available for display. Managing this is tricky and subject of many discussions.

[slimjen]

Thanks....I got it to work! I compacted the database and it started working...🤔