Multi-Select Checkbox Form/Sub-Form

[leem888]

Hello Everyone -

I'm hoping to get some expert help on an issue I've been trying to resolve all weekend.

Set Up
To make things simple here is a quick list of how I'm setup using an example of how I've approached the design.

Purpose
I want to interactively build the association between two tables when a checkbox is selected in the subform. In the Main form, the listbox switches between parent records and the SubForm, should show all records from the Child table with those associated records to a parent and those that are not associated yet.

Table/Form Design

1. Parent Table (ID, Column)
   • 1, Parent1
   • 2, Parent2
2. Child Table (ID, Column)
   • 1, Child1
   • 2, Child2
   • 3, Child3
   • 4, Child4
3. Join Table (ParentID, ChildID, Column)
   • 1, 1, Yes
   • 1, 2, Yes
   • 2, 3, Yes
   • 2, 4, Yes
4. Main Form
   • Parent ListBox - shows one parent
   • Child SubForm (DataSheet View) - shows associated child records

Note* The third column in the join table is a Yes/No checkbox.

Everything above works as it should. For example, when I initially launch the form, I see Parent1 in the ListBox and Child1, Child2 in the subform datasheet view.


Issue
What I would like to do is show all the possible records in the subform datasheet view whether there is an associated record or not. Hence, for Parent1, the subform should show:

• Child1, Yes
• Child2, Yes
• Child3, No
• Child4, No

I can replicate this within a query changing the join type and showing all records from the Child table and those linked to the Parent table. However, when I show the subform (which references that query), I still only see the linked records and not the other two.

I understand that since I don't already have the records associated in the join table that that may be my issue, but I don't want to create "ghost" records in the join table if I don't necessarily have too.

Finally, once a user checks those Child records that haven't been selected, the join table will get updated with the associated records accordingly.

Any suggestions on some alternative approaches would be greatly appreciated?

Michael Lee

 

[vbaInet]

Hello Michael. As long as your subform is linked, it will only show the linked records. If you want to show all the values the you should unlink it from the parent form. And if that's the case then it shouldn't be a subform.

 

[leem888]

vbaInet - I understand your response. I was just wondering "if" there was another way to show all the child records in the subform.

Based on your response, if I open a new form, won't the same problem exist when I pass the parent ID to the new form to associate to the Child records?

Alternatively, it seems like I can should create the records in advance for the join table. But that introduces a number or other concerns like retroactively updating the join table when a new child record is created.

Nevertheless, thanks again for the response.

 

[vbaInet]

You have control over how your records can be filtered. Your form or subform has a Filter property that you can set at anytime using vb. If you decide not to set up a link between your forms then your parent ID would have no effect on the subform. Does this help?

 

[leem888]

vbaInet - I understand I can control the filter. However, I don't believe either approach will resolve my issue of being able to see the total possible records that are available in the Child table as well as those already associated in the join table. ???

 

[vbaInet]

Anything is possible. Zip, attach and post your db pointing out in your db which field or subform should be looked at.

 

[leem888]

vbaInet - Thanks again for your response. I'll copy it below so all can benefit...

***
I understand. In that case, can you post screenshots? I was struggling to understand your problem but after having another look I think I have a better understanding. Basically, your subform is linked to your main form right? If you remove the link, i.e. clear the Source Object, Link Master Fields and Link Child Fields properties of the subform control and it will show all the records. Try that and let me know the outcome.
***

I removed the Master and Child field links and yes, that gave me all the possible records in the child table, but it also gave me those records in the join table that are linked to a different parents.

I've attached a screen shot to help explain.

What should have been shown for this Parent (Client) are all the possible Child (Metrics) records in the second subform. as a user checks the checkbox, those records will need to be added to the join table showing that parent is now associated to that child.

Let me know if I'm not making any sense or harder than it should be.

 

[vbaInet]

So what you want is to associate the selected metrics to the client that is currently being viewed yes?

 

[leem888]

Yes. but here is the kicker. (always a kicker) intially, the client will not have any associated metrics but should see all the metrics available.

As the client checks off those metrics they want associated, the next time they come to this screen they should see the list of metrics, both those that are unchecked and those that are checked. Those that are unchecked are a result of an outer join query between the join table and the metric table. those that are checked are identified in the join table.

I hope that makes sense.

 

[vbaInet]

Then you would need to create a seperate table that acts as an intermediary between the metrics and employee. Maybe call it MetricAllocations. Your Metric table will store the list of all available metrics. Structure should look something like this:

MetricAllocations
----------------
MetricAllocationsID (PK) - Autonumber type
MetricID_FK (Foreign key that looks-up its values from MetricID field in Metric table)
ClientID_FK (Foreign key that looks-up its values from ClientID field in Client table)
MetricSelected - Yes/No field type
MetricAllocationDate - Date or Text field - Default value of Date() - Shouldn't be visible on your form.

Do you already have this kind of setup?

 

[leem888]

Yes. I do have that setup. I am able to add the records to the join table, however, the records are getting inserted without a parentID. Any thoughts?

 

[vbaInet]

That's a strange behaviour. It could be with the relationships. Show me a blown up print screen of the relationships between MetricAllocations, Metrics and Client tables. Crop the other bits. The print screen you attached before was pretty small.

 

[leem888]

Here you go...

 

[vbaInet]

Looks like a pretty neat job you've done. From what table is it not adding the ClientID?

 

[leem888]

thank you. I used the Northwind Template.

when I'm on the main screen and selecting the checkbox in the subform. a record will be inserted into the client_metrics table, however without the clientID. I'm assuming since we removed the bound column from the client comboBox from the main form, there is no clientID for the subform to reference, hence unable to supply it to the insert.

in the attached screen shot, for this client (in the combo box), there should NOT be any checked records. it should show the full list of metrics possible. as the user selects the checkBox in the subform, that record should be added to the Client Metric table.

keep in mind, in the Associated Metrics subform, the Associated column is from the Client Metric table and all the remaining fields are from the metric table. Here is the query used (SELECT [Client Metrics].ClientID, [Client Metrics].Associated, Metrics.* FROM Metrics LEFT JOIN [Client Metrics] ON Metrics.ID=[Client Metrics].MetricID; )

Thanks again for all your guidance...

 

[vbaInet]

In that case, I think what you are trying to achieve has to be through code. Do you know some VBA?

 

[leem888]

a little. just enough to be dangerous. what ideas do you have in mind?

 

[vbaInet]

haha!! These are my thoughts. For every new Client (on the After Insert event) you will run code that will loop through all the records in your metrics table and for each metric it inserts a new record into the Client Metrics table for that client. Basically creating a clone of the metrics table. What do you think?

 

[leem888]

Glad to know great minds think alike. I was afraid that was the approach I would have to take.

Thanks for all your help vbaInet.

 

[vbaInet]

They do indeed If the metrics are not many and they don't change (ever) then you could create a column for each metric in the ClientMetrics table and on your form create a grid of checkboxes.

Glad to be of help. If you get stuck, just post back.