denverkeith
asked on
MS Access Checkbox Values
Greetings. I have a many-to-many relationship, where one table contains the parent records (say contractors) and a second contains related child records (say services). The child table is a single table, with a single field listing all the services available. These two tables are joined by the many-to-many table. I have a form with page tabs. I want to insert the services as a sub form, but rather than a continuous form that lists all the services and additions being made via a combo box, I would like to know if it is possible to have check boxes instead? I can then create a new contractor, click the tab and begin checking all the boxes for the corresponding services they offer. These selections will then need to be written to by join table that contains the unique IDs for each contractor and service. Thank you.
ASKER
I didn't think I would be using a continuous form though? So I have my join table that contains two records. ContractorID 1 = Service ID 1, and the second of ContractorID 1 = Service ID 3. So when i open the contractor form and click the services tab, I would see 20 check boxes (representing all the services offered from the services table), but only two would be checked as this person only does two things. If I then checked a third box, say service 5, it would write a record to the join table of ContractorID 1 = Service ID 5. If I unchecked a box, it would remove its corresponding record as well. I was imaging that there would have to be some on-change code as it would have to ReQuery the join table and populate the check boxes correctly each time a box is modified. Make sense?
This is the layout I have.
T_Contractors
ContractorID (Autonumber), Primary Key Field
ContractorName (Text)
T_Services
ServiceID (Autonumber), Primary Key Field
ServiceName (Text)
T_ServicesJoin
ContractorID (Number) Linked to T_Contractors, Primary Key Field
ServiceID (Number) - Linked to T_Services, Primary Key Field
THANK YOU!
This is the layout I have.
T_Contractors
ContractorID (Autonumber), Primary Key Field
ContractorName (Text)
T_Services
ServiceID (Autonumber), Primary Key Field
ServiceName (Text)
T_ServicesJoin
ContractorID (Number) Linked to T_Contractors, Primary Key Field
ServiceID (Number) - Linked to T_Services, Primary Key Field
THANK YOU!
I understand what you want to get.
You would still need to use a continuous form to list all the available services from the services table. That's the continuous form I was talking about.
You can have check boxes next to the services names in this form but the check boxes would have to be unbound. And there is the problem I was addressing in my previous post - you can't change values of individual instances of the checkbox. If you set it to something (either true or false) you are setting it for ALL the records (all services). So that's that's where the need for another subform comes from.
And then yes, you could write some code which would set the values of the checkboxes.
Give me some time and I might be able to produce a sample form with all the code and upload it.
You would still need to use a continuous form to list all the available services from the services table. That's the continuous form I was talking about.
You can have check boxes next to the services names in this form but the check boxes would have to be unbound. And there is the problem I was addressing in my previous post - you can't change values of individual instances of the checkbox. If you set it to something (either true or false) you are setting it for ALL the records (all services). So that's that's where the need for another subform comes from.
And then yes, you could write some code which would set the values of the checkboxes.
Give me some time and I might be able to produce a sample form with all the code and upload it.
ASKER
OK, Thank you so much. I have never had a need (that I know of) for two levels of subforms. I'l await your sample, thanks!
OK, it turns out my idea of using another subform doesn't work. It is not possible to use a subform in a form which is displayed in continuous view. I somehow assumed that it would be the same as with reports but it is not possible.
So I have come with another idea which is not perhaps so neat but I have tested it out and it seems to be working OK. You can look at the example in the uploaded files.
The idea is that you put as many checkboxes in the services subform at design time as you think there might be ever needed. Then through the code you only set the required number of the checkboxes to be actually visible. The code also sets the values when you switch between contractors, and there is another piece of code which writes the changes you make back to the db.
contractors.mdb
So I have come with another idea which is not perhaps so neat but I have tested it out and it seems to be working OK. You can look at the example in the uploaded files.
The idea is that you put as many checkboxes in the services subform at design time as you think there might be ever needed. Then through the code you only set the required number of the checkboxes to be actually visible. The code also sets the values when you switch between contractors, and there is another piece of code which writes the changes you make back to the db.
contractors.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Sorry for teh delay in getting back to this!
You can't have unbound controls showing different values for individual records in a continuous form. So what you would have to do is create another subform (3rd level) which would contain one unbound check box. Then you would need to set the value for the checkbox in the on current event of the subsubform, and modify the tables when the value of the check box changes.
I could help you with the coding if you are OK to follow this approach.
Maybe some experts will have a better idea but this is only way I can think of.