Question Locked

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

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

3 replies

by demosthanese

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
next

again im pretty new, but its worth a shot.

by pstanger

The problem with the approach of creating the checkboxes in advance is the fact that new interests can be added to the Table:Interests.
Therefore there wouldn't be enough checkboxes on the actual form to show all the interests.

The peusdo code for what I would do would be something like this.
<%
Qry1 = SELECT InterestID FROM ContactInterests WHERE ContactID=Contacts.ID
Qry2 = SELECT * FROM Interest ORDER BY InterestName ASC

While not Qry2.EOF
ID2 = Qry2.ID
InterestName = Qry2.InterestName
checkboxID = "checkbox"+ID2
While not Qry1.EOF
If ID2 = Qry1.InterestID then
vChecked = " checked "
else
vChecked = " "
end if
.moveNext
end While
%>

<%=InterestName%> />

<%
.moveNext
end While
%>

This is loosely based on ASP website code however I cannot seem to translate this into usable code Access VBA.

Thank you for your suggestion.

This discussion is closed to new replies.