Create a field name from a recordset

Question

I have a form that displays information on a project that has 10 check boxes. The check boxes are named "chkAudience1", "chkAudience2", etc through "chkAudience10". Any combination of boxes can be checked from none to all and anything in between.

Then I have a table that links the check boxes to the project. This table contains a field called ProjectID and a field called AudienceID (both fields are defined as number). This allows me to select all audience records for a project.

The problem is that I want to loop through the records for a project and check the boxes that match a record in the table. My current code looks like:

sqlStmt = "SELECT * FROM ProjectAudience WHERE ProjectID = " & Me.ProjectID.Value
Set rs = cn.Execute(sqlStmt)
While Not rs.EOF
    'Me.chkAudience1.Value = -1
    x = "Me.chkAudience" & rs(1).Value
    x.Value = -1
    rs.MoveNext
Wend

x will be set to "Me.checkAudience1", but the next line produces an "object required" error. How do I create a field name based on recordset data and then use that field name to set a value. (This is being done is Microsoft Access 2003)

Answer 1

The correct while loop is:

While Not rs.EOF
    'Me.chkAudience1.Value = -1
    Me.Controls("chkAudience" & (rs(1).Value)).Value = -1
    rs.MoveNext
Wend

The key is the Me.Controls().