Access 2007 checkboxes to be added to Form based on a query

By pstanger ·
Hi All,

Before I start I'll explain the basic Table structure.
Table:Contacts (basic contact information)
Table:Interests (contains a list of interests)
Table:ContactInterests (joining table for the other two)

Table Relationships
Contacts.ID (1 to many) ContactInterests.ContactID
Interests.ID (1 to many) ContactInterests.ContactID

I have a contacts Form that displays the information in an editable format, in this there is a subform that based on the Contact.ID being viewed presents a combobox to allow the interests to be changed/added and a further subform showing the interests currently associated with that Contact.

I think that this is very confusing set up for end-users. What I would like to do is create the subform in the Contacts Form based on the contents of the Table Interests.
Ideally I would present a Checkbox for each item in Table Interests which is already checked if contained in ContactInterests.

However I am having difficulty in coding this in VBA linked to Microsoft Access 2007.
1. What code is used to create a Checkbox on the Form?
2. How do a loop through a table/query adding a checkbox for each result?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Try this

by demosthanese In reply to Access 2007 checkboxes to ...

Well i havnt started learning DB access yet, but im pretty sure you would want to creat ehe checkboxes before hand and just enable/disable or hide/show them.

So the code would look something like:

DIM tablread() as (DB ACCESS STUFF HERE)
DIM interests

For Each intersts in table read
if tableread = interests
intersts & "checkbox.enabled" = true
end if

again im pretty new, but its worth a shot.

Collapse -

Close but not quite

by pstanger In reply to Try this

The problem with the approach of creating the checkboxes in advance is the fact that new interests can be added to the Table:Interests.<br/>
Therefore there wouldn't be enough checkboxes on the actual form to show all the interests.<br/>
The peusdo code for what I would do would be something like this.<br/>
Qry1 = SELECT InterestID FROM ContactInterests WHERE ContactID=Contacts.ID <br/>
Qry2 = SELECT * FROM Interest ORDER BY InterestName ASC <br/>
While not Qry2.EOF<br/>
ID2 = Qry2.ID<br/>
InterestName = Qry2.InterestName<br/>
checkboxID = "checkbox"+ID2<br/>
While not Qry1.EOF<br/>
If ID2 = Qry1.InterestID then<br/>
vChecked = " checked "<br/>
vChecked = " "<br/>
end if<br/>
end While<br/>
<%=InterestName%><input type="checkbox" id="checkboxID" name="checkboxID" value="<%=ID2%>" <%=vChecked%> /><br /><br/>
end While<br/>
This is loosely based on ASP website code however I cannot seem to translate this into usable code Access VBA.<br/>
Thank you for your suggestion.

Related Discussions

Related Forums