Solved Combo Box to populate multi-value field in many-to-many relationship (1 Viewer)

[mikenyby]

Hello all!

I've started work on what should be a rather simple database for a school archive, but right away I've come across a quandary I can't solve. In order to make this simple, I've downsized the database to only the relevant tables and fields to show the relationships in question.

Ultimately, an end user should be able to find all items associated with a person by searching for the person's name. But many persons will be associated with multiple items and most items are associated with multiple persons. I need to make an entry form that will allow a user to enter a new item and select all the persons that it is associated with. The problem here is that the database will likely end up with over 1000 names in the Persons table, so the standard multi-select list box will be too hard to navigate. I'd like to populate a multi-value "AssociatedPersons" field in a form by using a ComboBox, and when the user hit enter after finding each name, the name is populated to the AssociatedPersons field on the form. Ideally the combobox can display both the FamilyName and GivenName fields. Is this possible?

Thanks!

 

[Ranman256]

combo box is for 1 item.
Do use the listBox ,but put a text box above it to filter down the listbox:

If IsNull(txtFilter) Then
   lstBox.RowSource = "qsAllNames"
Else
   lstBox.RowSource = "qsFltName"
End If

'qsFltName sql would be like:
'select * from table where [LastName] like '*" & txtFilter & "*'"

 

[MajP]

Can you diagram this out so we can see what you would like it to work? What you describe is not the normal way to enter many to many values, although some version of that could be done. You show a normalized table structure, but you hint at using a multi value field

I'd like to populate a multi-value "AssociatedPersons" field in a form by using a ComboBox

It is also confusing because you talk about Searching and Entering, which are two separate features.

Normally this is done with a Parent form and Subform. With a many to many you can have the Parent form being Items and the child Persons associated to the items. Or you can have the main form be Persons and the child forms be items associated to the person. I often provide both views and let the user switch back and forth.

However if the Main form is Items you should be able to add a new item or navigate to an existing item. The subform in continuous view is a list of Persons. Each record is simply a combobox to add a new person. No multiselect is needed because each record has a combobox. With 100 people and a standard listbox there should be no issue because it will autocomplete. If you need something more advanced, you can add a find as you type but that is problematic in a continuous form but can be done.

 

[Pat Hartman]

Here's a link to a many-to-many sample that shows different views from each side.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[mikenyby]

Can you diagram this out so we can see what you would like it to work? What you describe is not the normal way to enter many to many values, although some version of that could be done. You show a normalized table structure, but you hint at using a multi value field


It is also confusing because you talk about Searching and Entering, which are two separate features.

Normally this is done with a Parent form and Subform. With a many to many you can have the Parent form being Items and the child Persons associated to the items. Or you can have the main form be Persons and the child forms be items associated to the person. I often provide both views and let the user switch back and forth.

However if the Main form is Items you should be able to add a new item or navigate to an existing item. The subform in continuous view is a list of Persons. Each record is simply a combobox to add a new person. No multiselect is needed because each record has a combobox. With 100 people and a standard listbox there should be no issue because it will autocomplete. If you need something more advanced, you can add a find as you type but that is problematic in a continuous form but can be done.

You're correct in that I've a normalized table structure and erroneously used the multi-value field term here. I can understand where the confusion comes from, that's my fault.

It is a normalized structure, I need to associate many persons with items during item entry. This leads to two challenges: First, finding the most elegant way to select multiple persons out of a list of over 1000 names without making it too labourious for the data entry clerk (which will likely be me), the second is finding an easy way to add more names to the persons table while entering new items. Closing all the forms to open a person entry form then reopening the forms is tedious. So far what I've come up with are pop-up forms. The first is an add associated person pop-up, looks like this:

But that form will have to be submitted and reloaded over and over again if an item has 30 or so people associated with it. I haven't come up with a design that will add multiple records to my personitem linking table successfully. A continuous form seems like a great idea but something I'm doing hasn't allowed them to populate the table properly (or at all for that matter).

I've a similar pop-up for adding new persons, but I've managed to get that one to work fairly well through a process of making the pop-up form save and close the item entry form when it is open, then reloading the item entry form to the last entry (ordered by timestamp des). I'm going to add another button to the form labelled "submit and add another" that submits the form then reloads it so a user doesn't have to work too hard to add a lot of people at a time.

 

[CJ_London]

Seems like you have an item which ultimately want to associate with multiple people

so main form is based on the item

and you have a continuous subform based on your person item table using a combo to populate the personid field

the basic rowsource would be based on your persons table but you probably need two features for this.

The first would be to exclude people already selected so users don’t get the ‘duplicate record’ error.

The other is find as you type as with 1000 people, could take some time to find people

 

[mikenyby]

The first would be to exclude people already selected so users don’t get the ‘duplicate record’ error.

Is there a standard way of executing this? If it's a command that has to be repeated on every row in the continuous subform, that seems pretty complicated!

The other is find as you type as with 1000 people, could take some time to find people

Wouldn't a combo box automatically do this?

 

[MajP]

Here is a very simple example. But even with 10K people I can find someone very quickly. Of course we can demo other more sophisticated option. If someone is not in the list click the add button to add a new name.
For demo purposes start typing Jones (since there are lots of those)

I kept this simple without a Find as you type and it works well. This does not allow you to search be first name, but is that really realistic where you know the first but not the last. I can demo that.

 

[CJ_London]

Re FAYT - yes but only ‘begins with’. Might be sufficient, up to you

re exclusion, yes but involves code to change the combo rowsource. Certainly doesn’t need to be repeated fir each row. There is only one row and each record has its own instance of the row controls

 

[mikenyby]

MajP, thanks, I think I'm going to work around your suggestions here. I think I've got a fairly user-friendly structure starting to shape up. Thanks!

 

[MajP]

Here is another idea of using a listbox to select the names. The listbox is a find as you type and does not include the already selected names. See the Form with the listbox. I also added code in the first form to exclude already selected values. All these things are doable, but add complexity. The listbox is actually easier to code than the continuous form with exclusion.

 

[MajP]

Wouldn't a combo box automatically do this?

Sort of. A standard combobox works well finding something that starts with a specific string. With a list of names you are probably going to have either two columns or last name first name. If you type "Smi" it will go to the first name starting with Smi. It will not go to records that have Smi not at beginning. So there is no way to search by first name. If you look at the listbox I incorporated the ability to search anywhere in the string. Most likely with names you kind of know what you are looking for. You are probably going to know the last name. So my point is that a standard combo probably works well. A FAYT probably works better with something like a product description where you are not quite sure what the name is, but you have an idea of what it might contain.