Results 1 to 15 of 15
  1. #1
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79

    How to get Checkbox to populate an associative table

    A little relevant background: I have 3 tables, two of which are connected to each other by an associative table. I currently have a form with tabs, and on each tab I have a subform linked under master fields with a field titled "InternalIncidentID". My subforms currently have combo boxes that are pulling in the InternalIncidentID and whatever value is selected in the combo box and placing both in the associative table as a composite primary key. Instead of this approach (which does work), the user wants me to remove the combo boxes and use check boxes instead. This is a bit of a challenge for me, as I am still somewhat of a beginner in Access. Do I need to use unbound check boxes with VBA behind this? Or is there a better way to alter the form to get the same results? If someone could help me out with the best approach as to how to use checkboxes, pull the checkbox value and the InternalIncidentID out of the form and into the associative table, that would be much appreciated!

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Do I need to use unbound check boxes with VBA behind this?
    I would probably use VBA. However, I do not understand how changing from one control type to another is creating an issue. In other words, why not use whatever technique is currently being employed by the combo?

    If the issue is a checkbox and the fact it is of type Boolean, you can also create an option group and have the option group return hardcoded integer values. You can use the option group's control source to automatically update a field in the form's RecordSource.

  3. #3
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Thanks for the guidance! Unfortunately, I don't know how to get the checkbox to use the same technique as the combo, I think mostly because of what you pointed out in the fact that it's Boolean. The field that the checkboxes are for are PKs, so instead I would use an option group, but I need to be able to select more than one option at a time. As far as using VBA, I could use some suggestions as to how to code it so if I needed to use unbound check boxes and had no other choice, if a box was checked it would populate into the associative table and pull in the InternalIncidentID field.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If you need to select multiple controls (checkboxes) you might entertain the idea of giving the controls a specific Tag property. I do not know how many options you have. If there are many options (controls) more elaborate code might be in order.

    For instance, if there are a dozen or so options, use the corresponding PK value as the control's Tag property value. Then use VBA to loop through the form's control collection. Have your code identify the Tag property and pass that value to an update or append query.

    Here is some air code ...
    Code:
    dim ctl as control
    dim lngPkValue as long
    
    for each ctl in Me.Controls
    
    if len(ctl.tag) > 0 then
    
    lngPkValue = ctl.tag
    
    ...
    'create logic to do something with PK value
    
    end if
    
    next ctl

  5. #5
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Unfortunately there are 20-25 controls (checkboxes) needed on the form, so I agree that I'd probably need to do some sort of more elaborate code, which I'm not really sure how to go about doing.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Well, you could still loop through the collection of controls. However, this will not be very flexible when it comes time to expand functionality of the app in the future.

    A temp table might be the way to go. So, your temp table would replace any collection you might otherwise create. Give your temp table a couple of columns. The first column would be the PK and the second column would be the value of the checkbox (type Yes/No). I would default the checkbox column as 0 (False).

    From here, there are a couple of ways to go. Let's say there are 32 options. You can manually create and name the 32 controls or you can use code to create the controls.

    Lets use the former as an explanation. We can hardcode the table records with PK's from the table that is the Rowsource of your original combo. Maybe use an append query to appen those records to the temp table. Now create a form and use the temp table as its recordsource.

    Execute some SQL in the Form's OnLoad event to set the Booleans back to 0. In other words, be sure to reset the Yes/No fields via an SQL action every time the form is opened by the user. Drag the 32 controls onto the form's design surface. Now, each control is bound to a field in the table. Use this form as a subform, where needed.

    After the form opens and the user interacts with the controls, an SQL action can use the values in the temp table to manage your original task.

    Using the temp table adds another option where code can edit the form as the list of options changes. For instance, the number of options changes from 32 to 37. Code can execute when the form opens and add the necessary controls. There are caveats about editing form's though. In order to do this, you cannot be in Run Time. It needs to be done during Design Time. So the form would need to be opened in Design View before automation of editing the controls can be performed.

  7. #7
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79

    Code for using Checkbox value to write to associative table

    A little relevant background: I have 3 tables (tblncident, tblClassification, and an associative tblIncidentClass). I currently have a form with tabs, and on each tab I have a subform linked under master fields with a field titled "InternalIncidentID" so that as I go through InternalIncidentIDs the subforms on the tabs change accordingly.

    One of my tabs with a subform currently has a combo box where the user can select ClassificationName (a field in tblClassification), and unseen in the background the InternalIncidentId (PK of tblIncident) and the ClassificationID (PK of tblClassification) are written to the associative table (and are the table's composite PKs).

    Instead of this approach (which does work), the user wants me to remove the combo boxes and use check boxes to present all 20-25 ClassificationName options instead. This is a bit of a challenge for me, as I am still somewhat of a beginner in Access. I need help with the programming, as I need to somehow use the checkbox Yes/No value and assign the appropriate ClassificationID and InternalIncidentID to the table in the background. The user also needs to be able to check as many of the boxes as possible. I would really, really appreciate some help and guidance on the code since I am a relative newbie when it comes to VBA. Thanks in advance!
    Attached Thumbnails Attached Thumbnails DB Table Layout.PNG  

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Duplicate threads on same topic by same poster is not considered good forum etiquette.

    I merged threads.

    How does selection of ClassificationName determine which InternalIncidentID value to associate with? This makes no sense.

    Conventional options for data entry:

    1. single form bound to tblIncidentClass with 2 comboboxes for selecting incident and classification

    2. main form bound to tblIncident and subform bound to tblIncidentClass with combobox to select classification

    3. main form bound to tblClassification and subform bound to tblIncidentClass with combobox to select incident
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    June7, I looked closer and the subform has a combo box that displays the ClassificationName, with the control source pointing to ClassificationID in the middle/associative table. When I select the name, it populates the associative table with the InternalIncidentID from the link master field and it grabs the ClassificationID from the combo box.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So you want to use UNBOUND checkboxes to allow user to create multiple records at one time? Select multiple classifications and create a record for each that are all associated with the same incident?

    So how to convert the checkbox selection to associated ClassificationID?

    One approach is to give each checkbox similar names: Chk1, Chk2, Chk3, etc. Make sure the ClassificationID is sequential 1 to 20 (or 25).

    Then looping code can address those specific controls:

    Dim x As Integer
    For x = 1 to 20
    If Me.Controls("Chk" & x) = True Then
    CurrentDb.Execute "INSERT INTO tblIncidentClass(InternalIncidentID, ClassificationID) VALUES(" & Me.IncidentID & ", " & x & ")"
    End If
    Next
    Me.Requery

    This is simple example. Could be more complicated to include checks against record duplication and setting all the UNBOUND checkboxes back to False or Null (unbound checkbox can have triple state).

    The real trick is figuring out what event to put code into.
    Last edited by June7; 12-31-2015 at 12:02 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Thanks! The code works except it gives me an error and can't find the Me.IncidentID value. Should I put the unbound check boxes on the subform? At the moment, I have created a new tab and placed the unbound check boxes on it, but how to change things so that the code can find the Me.IncidentID value is what I'm not sure of now.

  12. #12
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    Oops, I changed the Me.IncidentID to Me.InternalIncidentID, but now I am getting a Next without For error.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    My bad, I forgot the End If line. Edited post to correct.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    tbbrown32 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Posts
    79
    The code is working well! Thanks for the assistance!

  15. #15
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Duplicate threads on same topic by same poster is not considered good forum etiquette.
    duplicated here as well

    http://www.access-programmers.co.uk/...=283515&page=4

    looks like OP is playing suggestions from one forum against the other

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Updating checkbox in table with query
    By BatmanMR287 in forum Access
    Replies: 2
    Last Post: 01-10-2015, 04:27 PM
  2. Replies: 7
    Last Post: 03-02-2014, 03:16 PM
  3. Replies: 11
    Last Post: 03-16-2012, 11:46 AM
  4. Checkbox to populate one table to another
    By glasgowlad1999 in forum Forms
    Replies: 1
    Last Post: 02-09-2011, 07:47 PM
  5. Populate checkbox
    By Ashe in forum Forms
    Replies: 9
    Last Post: 01-03-2011, 09:43 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums