Make multiple selections without MVFs (1 Viewer)

vent · Oct 30, 2017

Hi all

So in the screenshot below is a form users use to add new company information. The organization I'm with is a local community college and they monitor which companies work with the college. Certain companies or agencies if you will, will accept students from certain programs. E.g. Company A is an accounting firm and they'll look at candidates from accounting programs. Each program has a program code and on this form the user can select a program code (e.g. *NOCODE, 1214, 9382, etc) when they're adding in this new record. The program code field on the main table (tblAgencyInformationNew) is a lookup and gets it's values from a separate table called tblProgramCodes. As of now the user can only enter one program code per agency record. However is there a way for the user to select more than one program code without turning the program codes field into a multivalue field? I know MVFs are known to be problematic in relational databases. But I'm just wondering, is there way to set this up where users can make multiple selections WITHOUT compromising the integrity of the database?

mike60smart · Oct 30, 2017

Hi

You can display a Listbox containing All of the Codes and set the properties to be Multi Value.

Then use a Command Button to Insert the Codes into a table.

Your current table structure will not allow you to do this.

Can you upload a zipped copy of your Db?

arnelgp · Oct 30, 2017

use MVF if necessary.
the field wouldn't be there
for 10 years for nothing.
if you are moved by the "10
commandment in access", don't.
that is not absolute. it is not
even in Standard-making bodies (ANSI, etc).
you, the programmer is resposible for
whatever you do or don't.
if somebody has idea how to twist
your data to get functionality like
MVF, then i like to here that.

not a single author in the books
that i read on access reference
called this field "evil".

Mark_ · Oct 30, 2017

For me, I would create a table that links programs to agencies. After all, you will quickly realize that for each such link there will be more than just "Looking for candidates". In a table to link the two you can include prereqs, GPA minimums, required courses, recency, an other such specifics.

Pat Hartman · Oct 30, 2017

You have a many-to-many relationship. That means that you need a junction table where you would hold the selections. So you would remove ProgramCodeID from the Agency table and create a new table with a compound primary key (select both columns and then press the "key" button).

tblAgencyPrograms
AgencyID (FK to tblAgencyInformationNew, primary key field 1)
ProgramCodeID (FK to tblProgramCodes, primary key field 2)
... other fields if you need to track when the relationship was created, etc.

On your form, you would show this data in a subform. You can remove the borders and make it blend it so it is not obviously a subform. This is the no-code solution.

However, if you like writing code, you can display this as a multi-select list box. But, since multi-select list boxes are not bound, you would have to write code in the current event of the form to populate the existing selections. The trickier part is the updating. In the Form's AfterUpdate event, you would need to delete the existing values and append the current selections. If I were to do this (and I wouldn't), I would keep a hidden flag that got set if the Listbox was changed because you don't want to delete and re-add unless something changed. Also since the listbox isn't bound, it wouldn't trigger the form to update so you would also have to dirty a bound field just in case the only change the user made was to the listbox. All in all, I wouldn't do it.

I also wouldn't use MV fields. Their implementation is convoluted and requires specialized VBA and SQL to work with them. Plus they are not upsizable and that is my biggest objection to them since most of my applications end up as SQL Server, Oracle, DB2, etc. They are an ACE only construct so if you think SQL Server or some other RDBMS might be in your future, you should never use them. They are probably easier than the code driven Listbox but I would not use either.

Relational databases were defined in the 70's so the concept is almost 50 years old and amazingly still relevant today.

PS - I was actually in the room in Redmond when the MS team announced MV fields to a select group of MVPs. There was practically a riot when we found out that the plumbing would be hidden and therefor require specialized VBA and SQL and not be portable.

vent · Oct 30, 2017

Pat Hartman said:
You have a many-to-many relationship. That means that you need a junction table where you would hold the selections. So you would remove ProgramCodeID from the Agency table and create a new table with a compound primary key (select both columns and then press the "key" button).

tblAgencyPrograms
AgencyID (FK to tblAgencyInformationNew, primary key field 1)
ProgramCodeID (FK to tblProgramCodes, primary key field 2)
... other fields if you need to track when the relationship was created, etc.

On your form, you would show this data in a subform. You can remove the borders and make it blend it so it is not obviously a subform. This is the no-code solution.

However, if you like writing code, you can display this as a multi-select list box. But, since multi-select list boxes are not bound, you would have to write code in the current event of the form to populate the existing selections. The trickier part is the updating. In the Form's AfterUpdate event, you would need to delete the existing values and append the current selections. If I were to do this (and I wouldn't), I would keep a hidden flag that got set if the Listbox was changed because you don't want to delete and re-add unless something changed. Also since the listbox isn't bound, it wouldn't trigger the form to update so you would also have to dirty a bound field just in case the only change the user made was to the listbox. All in all, I wouldn't do it.

I also wouldn't use MV fields. Their implementation is convoluted and requires specialized VBA and SQL to work with them. Plus they are not upsizable and that is my biggest objection to them since most of my applications end up as SQL Server, Oracle, DB2, etc. They are an ACE only construct so if you think SQL Server or some other RDBMS might be in your future, you should never use them. They are probably easier than the code driven Listbox but I would not use either.

Relational databases were defined in the 70's so the concept is almost 50 years old and amazingly still relevant today.

PS - I was actually in the room in Redmond when the MS team announced MV fields to a select group of MVPs. There was practically a riot when we found out that the plumbing would be hidden and therefor require specialized VBA and SQL and not be portable.

Hi thank you for your detailed reply. Now my question is, the form's record source is a query. In the split form (screenshot below) there is a button the user can press that will open up this form. I had a previous problem where new records entered did not show up in the datasheet. To solve this, I had to change the form's record source to match the split form so once the datasheet is requeried, the new entry would show up. Do you the solution you offer could potentially conflict with the present functionality?

mike60smart · Oct 30, 2017

Hi

I would recommend that you follow Pat's good advise and create a Subform to store the multiple Codes which does not involve any Coding.

This will not interfere with your current Splitform configuration.

vent · Oct 31, 2017

Hey guys, so I'm following Pat Hartman's great advise as best I can and made a junction table called tblAgencyProgramCodes and drawn out the relationships between both tables (screenshot below called tables). Have made a subform (screenshot below) and set to continuous forms. And below I included this subform in the add new agency form. Note: the form may look different from earlier screenshots because this is a sample data, full of "dummy data" but resembles the actual database in terms of user functionality. My question now is, how do I use this subform as if I'm filling out a new agency for the first time that has multiple program codes? subform just appears blank. Thanks guys.

Pat Hartman · Oct 31, 2017

1. You need to remove ProgramCodeID from tblAgencyInformation. The new junction table manages the relationship and allows multiple values of Program code for each agency.
2. You have to remove the ProgramCodID combo from the form and substitute the new subform. Make sure that the Master/Child properties are correctly set so that Access will sync the subform with the main form.
3. You need two forms to manage Program codes. One to manage the universe of Program Codes. It will look similar to the form you posted and it will be bound to tblProgramCodes. The second form will be the subform that replaces the combo and it should itself contain a combo that selects the ID and Name fields. Set the column width properties to hide the ID column but show the name column. Make sure to sort the RowSource by the name field.

vent · Nov 2, 2017

Thanks everyone!

Pat Hartman · Nov 3, 2017

You're welcome.

Make multiple selections without MVFs (1 Viewer)

vent

Registered User.

Attachments

mike60smart

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

vent

Registered User.

Attachments

mike60smart

Registered User.

vent

Registered User.

Attachments

Pat Hartman

Super Moderator

vent

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread