Issue with Dependent Multi Pick lists in ACCESS 2016 Multi Item form (1 Viewer)

jmaxton1

New member
Local time
Today, 06:47
Joined
Nov 20, 2020
Messages
8
I have a question regarding dependent multipick lists. I have subjects Client and organization. Client and Organization can come together in tjnClient^Organization. Within tblOrganization, you are able to select various services that the organization can offer. On the junction table between these subjects, there is a relationship to the same lookup table for services, offering a choice of services used by the client. I am needing a way to limit the choice of services for each client/org pair based on the org's choices on their table.
Here is what I have done:


  1. I created a query, qryOrganization_Services. In that query, I have returned the primary keys of the organizations and their services, using an inner join to only collect the organizations which have picked services.


  1. I have limited this query to have an organization primary key equal to the cboOrganization-RecordIdentifier, which is the combo box where the user selects an organization on the junction form between Client and Organization.
  2. The rowsource for the combo box to select services that the client uses has been set up with an inner join between the services lookup table and the query mentioned earlier.


  1. I placed a requery macro on the AfterUpdate for the combo box to choose an organization, as well as a requery on the OnCurrent for the form.

The issue is the following. It appears that every single record updates its client services box based on the very first record’s choice of organization. I am not sure what is causing this.


Any thoughts appreciated.




Note this occurs in a continuous multi record form. I really need a fix for this. Any ideas would be appreciated.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:47
Joined
Feb 19, 2013
Messages
16,553
welcome to the forum

describing what you have does not really help us to help you. You know your app/business we don't.

- what is a multipick list? do you mean a multivalue lookup field? a multi select listbox? or something else?
- this sounds like denormalised data 'Within tblOrganization, you are able to select various services that the organization can offer' Or perhaps it is a multivalue lookup field?

if you are using multivalue fields, they are very restrictive in what you can do

it may be you are talking about what are commonly called 'cascading combo's'. If so, they need special treatment in continuous forms


as regards your update issue, I can't suggest anything without having a clearer picture of what you are actually doing - some example data and the outcome required, the code you are using, etc. A screenshot of the form may help as well
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

If you can share a sample copy of your db with test data, it may help make things move a little easier.
 

jmaxton1

New member
Local time
Today, 06:47
Joined
Nov 20, 2020
Messages
8
The file is too large. Is there another way I can upload?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
The file is too large. Is there another way I can upload?
You could try trimming down the sample db to contain only enough objects to demonstrate the issue.
 

jmaxton1

New member
Local time
Today, 06:47
Joined
Nov 20, 2020
Messages
8
Has been trimmed to primarily include objects of interest.

Note:
  • The client and organizations meet in a junction table, which is a many to many relationship. This table is tjnClient^Organization_(Pairing)
  • These junction records are managed through the form fjnClient^Organization_(Pairing)
  • The organization records can select multiple items from a category relationship that uses a table called tlk*Services. This shows up in the form as a multi-select combo box.
  • The short story of the row source for the "Services Used" combo box (cbo(*Service)-RecordIdentifier) is that it is based on a query which returns the list of services per organization (What they have selected within their own table). This query is limited to only return records that match the primary key of the organization in question for that particular row.
  • There is an after update macro on the combo box cboOrganization-RecordIdentifier. This should theoretically update the list of "Services Used" to match those available for the new organization picked. A similar macro is placed on the OnCurrent event.
The problem is that the when one instance of cboOrganization-RecordIdentifier updates, every single dependent combo box of services used also updates.

I hope I have explained enough. Thanks in advance.
 

Attachments

  • FreelanceDatabase.accdb
    3.3 MB · Views: 410

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:47
Joined
May 7, 2009
Messages
19,169
only comment so far.
avoid using *- on your field (table) name.
if you use it in Query, msa will (sometimes) interpret it as mathematic operator.
 

jmaxton1

New member
Local time
Today, 06:47
Joined
Nov 20, 2020
Messages
8
There was a query error which made the fjn form uneditable. Here is it again. It should be fixed now. Thanks for the tip on field names!
 

Attachments

  • FreelanceDatabase.accdb
    4 MB · Views: 390

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:47
Joined
May 7, 2009
Messages
19,169
using Multi-select combobox can be very confusing.
try using simple tables.
 

Attachments

  • ClientOrg.zip
    38.6 KB · Views: 411

jmaxton1

New member
Local time
Today, 06:47
Joined
Nov 20, 2020
Messages
8
That might do it. Not to discredit your solution, but I am really wanting to try and find a way to do this within a form. I will keep the tables in mind, however. Thank you!
 

mike60smart

Registered User.
Local time
Today, 11:47
Joined
Aug 6, 2017
Messages
1,899
I would recommend going the Table route as given in arnelgp's example.
Multi-Select fields in a table will always cause problems.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:47
Joined
May 21, 2018
Messages
8,463
The problem is that the when one instance of cboOrganization-RecordIdentifier updates, every single dependent combo box of services used also updates
The issue you describe is the same issue seen when trying to manipulate the rowsource (or any property) in a continuous form or a listbox or combobox. There is really only one control all occurences are simply paintings of the control. So if you manipulate one you manipulate all renderings.
With a simple combobox there are ways to make it work or at least appear to work. This can probably be done with an MVF, but as pointed out this is so confusing. A lot of people frown on Multi Value Fields for a lot of good reasons. I do not have a problem if I am storing the visible value, but when you start storing a foreign key that is not visible it is super confusing.

If this was me I would do it with a popup. The pop up would could easily be filtered. However, there is definately code involved to make this work. Not sure you level of vba. You would have to manipulate the queries.
See example of how I would do it.

I tried to look at this to see if I could fake it the same way as a single combo. But with the crazy bad naming convention, macros, and referencing MVF this is a puzzle, wrapped in a riddle, wrapped in an enigma. I think I could fake a cascading MVF in a continuous form but not this one. If you are set on the MVF I would use the same trick as in my fake MVF but use a real MVF on a popup. Show the concatenated values but pop up a little form with the MVF when you add or edit. Then the MVF could be filtered. I think this could be more easily done and less confusing. So your display remains the same for the continuous and your MVF control remains the same, you just do it on a popup form.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 04:47
Joined
Oct 29, 2018
Messages
21,357
That might do it. Not to discredit your solution, but I am really wanting to try and find a way to do this within a form. I will keep the tables in mind, however. Thank you!
I gave it a quick look and tried to see what's possible. In my humble opinion, I would say that this was a very confusing design. I wouldn't recommend it either. However, since I already spent the time, I thought I'd post what I came up with anyway. Again, though, you might seriously consider going in a more proper direction. Just my 2 cents...
 

Attachments

  • FreelanceDatabase.zip
    1.3 MB · Views: 384

jmaxton1

New member
Local time
Today, 06:47
Joined
Nov 20, 2020
Messages
8
Thank you all. I really appreciate the time and consideration that you've given me in this situation. The thoughts you have shared and suggested solutions mean a lot to me. I will take the time to go through this some more. Unfortunately, the naming convention must remain the way it is given my set of circumstances. I recognize it may not be perfect in some areas. Again given my limitations for this situation and the eventual receiver of this DB, I need to try and keep it to macros as much as possible. I recognize that may not be the absolute best option, but it is the situation I am up against. I will most likely move in the direction of the table set up or a "pop up" form for editing as has been suggested. Again, thank you all very much.
 

Blonkos

New member
Local time
Today, 12:47
Joined
Nov 22, 2020
Messages
7
Depicting what you have doesn't generally assist us with helping you. You know your application/business we don't.

* What is a multipick list? do you mean a multivalue query field? a multi select listbox? or on the other hand something different?

* This seems like denormalised information 'Inside tblOrganization, you can choose different administrations that the association can offer' Or maybe it is a multivalue query field?

In the event that you are utilizing multivalue fields, they are extremely prohibitive in what you can do .it could be you are discussing what are usually called 'falling combo's'. Provided that this is true, they need unique treatment in persistent structures .As respects your update issue, I can't propose anything without having a more clear image of what you are really doing - some model information and the result required, the code you are utilizing, and so forth A screen capture of the structure may help too
 

Users who are viewing this thread

Top Bottom