Query to autocomplete checklist?

[Guest]

I need to make a form with a checklist that will auotmatically be completed
What I need to do is:
I have a table for clients (PK ClientID)
and a table for Responses
I want to be able to have design the checklist to look-up if the clientID is
in the Response Table if it is it needs to cross reference the QuestionID
which is in the Response Table and on the Checklist form and then tick the
question as done in the check box.
(IF [clientID=clientID.tblResponse THEN find QuestionID in tblResponse IF
both true then mark YES])
Any ideas on how to do this?

 

[Jeff L]

It sounds like you have a single form for this. In the On Current
Event put:
Dim rst as Object, qry as String

'Set your checkboxes to False
Me.Question1 = False
Me.Question2 = False
..
..
..
Me.QuestionN = False

qry = "Select QuestionId From Responses Inner Join Clients On
Responses.ClientID = Clients.ClientID Where Clients.ClientID = " &
Me.ClientID

Set Rst = CurrentDb.OpenRecordset(qry)

If rst.eof = False then 'Questions for this client exist
rst.MoveFirst
End If

Do while rst.eof = False
'Set the appropriate checkbox to true
Select Case rst!QuestionId
Case 1
Me.Question1 = True
Case 2
Me.Question2 = True

And so on
End Select
rst.MoveNext
Loop


Hope that helps!