Use Checkbox to select record, Send data to Tbl

[SisterSue]

Hi, thanks for your time. I am a novice user; but, here is my goal.
I have a form with 2 cascading combo boxes and a subform.
I want the subform to list all records from it's table, and have the check box checked on the record that corresponds to the combobox.

Form: KB_Overview (bound to Project_Desc)
Combobox: ProjList (unbound with query to same table as form)
Subform: based on table (Knowledge_Divi sions) that includes checkbox
Parent and Child field: KD_ID

I would like the subform to display all rows from its table (Knowledge_Divi sions) and the check box selected on the record that corresponds to the main form record (Project_Desc.P roject_Name). For example if the table Project_Desc had a KD_ID that =5; then, the subform should have row 5's check box checked.

Also, I would like to be able to change the check box and then, I assume, an after update to change record in corresponding primary table.

Thanks so much for any insight. Please let me know if I can provide more information.

[jimatqsi]

You say you want "the subform to display all rows from its table (Knowledge_Divi sions)". Don't you mean you want it to display all rows that match the main form's ID?

Are you aware that the subform and the main form can be linked so that the subform shows only the rows from Knowledge_Divis ions that have the same ID as table Project_Name?

What do you want to change in the primary table? You check a box on one of (potentially) several rows in the sub-form - some of the rows are checked, some are not ... what do you want to change in the primary table based on the fact that one of the sub-form rows has been checked?

Jim

[SisterSue]

[IMGnothumb]http://www.techlaborat e.com/wp-content/uploads/2014/02/Scrnshot.jpg[/IMGnothumb]

I would like the subform to show all rows. AND the row that corresponds with the Main Form to show the checkbox. (Is possible to have no box checked/record selected).

Yes, I am aware of liking the parent/sub fields. When I was messing with it the first issue is that it only wants to display the corresponding row - I want it to show all rows.

Second, I do not know how to get a check box, when selected (or changed), to change the corresponding data in main form.

The Main form table and subform table both have an ID field (KD_ID). If a box is checked in subform (or changed), I want the corresponding ID# to change in the main form.

Thanks so much!!!

[NeoPa]

Some questions :

1. Do you need a subform or could a detail section work well for you?
2. When you refer to record #5 in your example, are you saying the fifth in the list or that one which has an ID of 5?

[jimatqsi]

Let me try to understand. Let's you have 10 records in Project_Desc, with IDs 1-10, and you have 50 records in Knowledge_Divis ions.

No matter which of the 10 records you navigate to in the Main form, you want always to show 50 records in the sub-form, correct? If so, then whether the check box is checked (true) on the main form is really a function of which of those 50 rows you are on in the sub-form. If you navigate to ID#5 on the main form, when you go to any record in the sub-form that has an ID=5 then you want the checkbox in the main form to be checked ? Or only if that sub-form record with the ID=5 has a checkbox that is checked? What if another record in the sub-form has an ID=5 but the checkbox is not checked on that one?

If you want all records with ID=5 to all be on or all be off, you would be better off controlling it from the main form. Check the box on the main form and then use some code to change all the sub-form records to have the same true/false value as the main form record. Controlling it from the sub-form is just confusing because multiple records can have the same ID# but not necessarily the same true/false value in the checkbox.

The easy part is to make the sub-form always show all the records. To do that, in the main form click on the sub-form only 1 time. In the properties Data tab, remove any linkage between the two forms. That will give you two unrelated forms, but one is a main form and one a sub-form.

I hope that helps. There's probably something I don't understand about what you're trying to, so just let me know where I missed the mark.

Jim

[jimatqsi]

The image you attached is too small to be helpful. I can't read it.

Jim

[SisterSue]

@NeoPa
1. No, sir. It does not need to be a subform. I just need the data to be displayed consistently across pages. (PS. I don't suck as bad at the formatting part. LOL
2. There will only be 5 records in that table. So, their ID# and Row# happen to be equal.

[SisterSue]

@jimatqsi
[IMGnothumb]http://www.techlaborat e.com/wp-content/uploads/2014/02/Screenshot.png[/IMGnothumb]
Trying image again.

Ok, got you on showing all rows.

No matter which of the 10 records you navigate to in the Main form, you want always to show 50 records in the sub-form, correct?
Yes, correct.

If you navigate to ID#5 on the main form, when you go to any record in the sub-form that has an ID=5 then you want the checkbox in the main form to be checked ? Or only if that sub-form record with the ID=5 has a checkbox that is checked? What if another record in the sub-form has an ID=5 but the checkbox is not checked on that one?
First, I only set the check box field in the sub_form Tbl. The main form has a value field, called KD_ID. This is linked to the subform table's KD_ID.

If the Project (Main form) has a KD_ID of "5" then I would like the sub-form to show a checked box on the corresponding row with the KD_ID of 5. (Also, allowing the user to change the selection from 5 to any other number (1-4) and have that data (KD_ID) update in Main form table.

The subform is just a way to show all options that can be selected. (Note subform table is where I have a check box field)

Does that help?

Thank you BOTH again. I'm so very grateful for any assistance. Makes me want to pay it forward....obvi ously that will be with a skill other than VBA ;)
[IMGNOTHUMB]http://bytes.com/attachments/attachment/7506d1392681415/screenshot.jpg[/IMGNOTHUMB]

[SisterSue]

PS. IF subform is the right way to go, I would much rather change it to a continuous view....if possible

Just thought of this...I really don't need an ID#. I simply inserted that at some point to help with relationships and some other stuff I read (that I don't remember) that made me think it was a good idea. :)

[NeoPa]

It is generally a good idea Sue. Keep with the ID I suggest.

Right. Always good to work with someone who can respond to two simple questions with two straightforward answers :-)

My advice would be not to use a subform unless you have to. Are the controls that are currently in the Header section bound to any table? If so, then that would mean a subform probably is required. Otherwise they are likely to be unbound and we can simply add another unbound control to reflect the knowledge type.

For now I'll assume that's the direction we're headed. To change a value in the Header control that then triggers the update of data in the Detail section is not too trivial, but it is possible. It would involve unsetting the previous one as well as setting the new one. Remember that this will also still be set next time the form is opened. It seems somewhat clumsy if I may say so without causing offense.

Of course, it may be that this is all over-complicating matters. Would a ComboBox control linked to the same table you currently use in the Detail section work well for you?

[SisterSue]

@NeoPa

1) Comboboxes are bound (I do know how to make them unbound). They are currently in the Detail section, not header.

2) When you discuss Unsetting and Setting - I have no idea what that means ;)

3) Would a ComboBox control linked to the same table you currently use in the Detail section work well for you?
I believe that is what I am already doing as the boxes are in the detail section (?)

Thanks Again!

[NeoPa]

1. My mistake. I assume this means there can be multiple records that these reflect?
   Whether Header or Detail doesn't matter too much, but if it's Detail then we cannot use the Header / Detail approach to replace the idea of a subform. See #3 below, but if that isn't suitable then a subform seems called for.
2. Setting and Unsetting refer to changing the CheckBox to True and False respectively.
3. Consider the question as referring to the bound controls above then. Specifically, instead of having a control to show the ID of the Knowledge Type records from a subform, have a bound ComboBox instead which shows the ID when closed, but all the data when dropped down. Would such an approach be suitable, or must we have all the records shown below as you have it displayed?

[SisterSue]

Hi NeoPa,

Yes, the drop down boxes are linked to customer and project tables with multiple choice combinations.

Unfortunately, I do not want to do a combo or list box for the bottom half. I would like the reader to see all the choices at once; and, the boxes limit my formatting options (unless I use a lot of other code that I don't know ;)

So, should we stick with the subform?

Appreciate it!

[SisterSue]

Hey there,

In working with some things, it appears to me (note that sad context) that I need to keep the second combox unbound. Otherwise, It gives me a duplication error as I have that field set to no duplicates.

Just throwing in that fodder to add to the fun! ;)

[NeoPa]

I think we do Sue.

Please do something(s) for me to allow us to continue in a way that is more understandable for both of us.

1. Update the design of your form so that it has an extra control in the detail section to reflect the knowledge type for the record. I would suggest a ComboBox that could even be populated by the table in the subform for ease. Let us know the name of this new control.
2. List the names of the record sources for both the main form and the subform.
3. Post a new picture of the form, including the subform, for us to refer to and work with.