[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1271
  • Last Modified:

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.
  • 4
  • 3
1 Solution
I think it might be possible to do if you are prepared to write some VBA code.

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.
denverkeithAuthor Commented:
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.
ContractorID (Autonumber), Primary Key Field
ContractorName (Text)

ServiceID (Autonumber), Primary Key Field
ServiceName (Text)

ContractorID (Number)  Linked to T_Contractors, Primary Key Field
ServiceID (Number) - Linked to T_Services, Primary Key Field

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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

denverkeithAuthor Commented:
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.
Sorry, I was a bit sloppy. There was a typo in the name of the table in one of the SQL statements.
I have reuploaded the file. I also added a condition to check whether there is a valid record in the parent form, otherwise you would getting some errors when moving on to a new record.
denverkeithAuthor Commented:
Thank you.  Sorry for teh delay in getting back to this!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now