MS Access Checkbox Values

Posted on 2009-04-16
Last Modified: 2013-12-20
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.
Question by:denverkeith
    LVL 12

    Expert Comment

    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.

    Author Comment

    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

    LVL 12

    Expert Comment

    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.

    Author Comment

    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!
    LVL 12

    Expert Comment

    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.
    LVL 12

    Accepted Solution

    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.

    Author Closing Comment

    Thank you.  Sorry for teh delay in getting back to this!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now