checklist

[slimjen]

All, i am working in a 2010 database. I want to recreate a paper checklist on a form in access. A processor calls different clients during the day. The auditor double checks that the processor has gotten all the information from the clients ex.

Did the processor document the clients phone num check box for yes/ uncheck box for no


Did the processor document the address yes/no
etc.

I ve read that its not wise to put the questions in a table with a datatype of yes/no but I would like to have the checkbox next to the questions since all the questions will be yes/no(check or uncheck) I have a table with clients info with the ssn as the unique key. I need to store the answers.
How would be the best way to approach this design.
thanks

[June7]

Normalized structure would be a table where each record is a response.

ClientID	ProcessorID	QuestionID	Answer
234	167	1	Y
234	167	2	Y
234	167	3	N
234	167	4	Y
567	167	1	Y
567	167	2	N
567	167	3	N
567	167	4	Y

[slimjen]

So looking at this; I should have the following tables?:

]tblClient clientid fname lname ...
tblProcessor processorid name
tblQuestions qid question
rblAns ansID response y/n

Would the response be data type y/n? So on the form; I would have the questions as labels or in a dropdown and the y/n as checkboxes? I'll try this over the weekend. Seems simple enough if I grasp the concept. I know I want to create the form to look just like the paper copy with client info at the top and questions w/ checkbox in the middle body. Thanks

[June7]

That would be normalized table structure. The data type can be Y/N or text.

The data entry form will probably not look like the paper version. Probably need a form/subform arrangement. Main form bound to tblClient and subform bound to tblAns with comboboxes to select processor and question. Use of code can help automate this data entry. For instance, code can create a set of response records for a client with the processorID and questionID fields already populated. All the processor will have to do is input the answers.

Another approach would be to create records in tblAns only for positive responses.

ClientID	ProcessorID	QuestionID	DateEnter
234	167	1	6/1/2013
234	167	4	6/1/2013
234	754	7	6/2/2013

[slimjen]

Ok. I created the tables each for ClientInfo, Questions, processors. It will only be yes or no responses but the user wants to just tick the box; not choose yes or no. An they want the questions on the form next to the tick box; not have to select the questions. Just like the form. What do I do to make this happen the way they want.

[June7]

Options, all involve VBA code:

1. Create complete set of response records in tblAns for the specific client. Display the records in subform set to Datasheet or Continuous view. Displaying the question text can be done by joining tblAns to tblQuestions. Code to create records would be like:

Sub btnCreate_Click()
CurrentDb.Execute "INSERT INTO tblAns SELECT " & Me.tbxClientID & " AS Client, " & Me.tbxProcessorID & " AS Processor, QuestionID FROM tblQuestions;"
Me.Requery
End Sub

2. Use an unbound form. This will allow building a form that actually looks like the paper version. Code to save data would be like (example for 10 questions):

Sub btnSave_Click()
For i = 1 to 10
CurrentDb.Execute "INSERT INTO tblAns(ClientID, ProcessorID, QuestionID, Answer) VALUES(" & Me.tbxClientID & ", " & Me.tbxProcessorID & ", " & Me("tbxQuestionID" & i) & ", " & Me("tbxAns" & i) & ")"
Next
Docmd.Close acForm, Me.Name, acSaveNo
End Sub

[slimjen]

GM. I found a database that had simularities to what I need. I modified the form because it had a subform to show what I was refering to that the user need. My form is called "frmSample". Attached is the sample database. I am working in 2010 so I published it in 2003 because I realize some on the forum can not open accdb. Thanks for your help

[June7]

Now that you have a working db, what is the issue? Have you attempted to implement suggested code?

[slimjen]

I needed to read your post again. I put everything in place and I don't know how to link the questions with the checkbox for the answers before applying the code. Could you take a look please? I've attached a new one. Thank you

[June7]

With this arrangement the checkboxes cannot be linked to anything. Use option 2 for saving data to tblSCSMedAns. Need a combobox in the form header for agents. Change Agent field in tblSCSMedAns to number to save AgentID. Agent name parts should be in separate fields.

You have 21 questions so name them to correspond with question ID like: Check1, Check2, Check3, etc. Then code to save data:

Sub btnSaveAnswers_Click()
If Not IsNull(Me.SSNum) And Not IsNull(Me.cbxAgent) Then
For i = 1 to 21
CurrentDb.Execute "INSERT INTO tblSCSMedAns(SSNum, Agent, QID, Answer, AuditDte) VALUES('" & Me.SSNum & "', " & Me.cbxAgent & ", " & i & ", " & Me("Check" & i) & ", #" & Date() & "#)"
Next
Docmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

[slimjen]

Ok. I follow for the most part. But I still can’t get where I am linking with code the answers to the questions. In my question table; I have qid and the question(datatype text) qid 1-21. On the form; I put the question in the label and a check box next to it. When I code it; it would enter yes/no despending on where the box is checked or not in the answer field? For what question? How do I determine this field? Rather not a field but a label Qid?

[slimjen]

I am getting a sytax error:

Private Sub cmdSaveAnswers_Click()
Dim i As Integer
If Not IsNull(Me.SSNum) And Not IsNull(Me.cboAgent) Then
For i = 1 To 21
CurrentDb.Execute "INSERT INTO tblSCSMedAns(SSNum, Agent, QID, Answer, AuditDte) VALUES('" &
Me.SSNum & "', " & Me.cboAgent & ", " & i & ", " & Me("Check" & i) & ", #" & Date() & "#)"
Next
DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

I named all the check boxes accordingly. Switch the agent to agentid number. Declared the integer. Syntax error???

[June7]

Since you have the questions in label controls there is no need to link the questions table with the form and couldn't with this form structure anyway. The code 'links' answers to the questions by using the looping i variable. Just make sure the checkbox control names include the question ID number that corresponds to the question in associated label.

[slimjen]

I have done this. Each checkbox is named check1, check2, check3.......check21. I am getting a syntax error when I try to debug. The line that begins with CurrentDB.execute turns red. I can't seem to get this thing going! SMH.

[June7]

Try a putting a line continuation character (_) at the end of the first line of the multi-line sql code. Use the line continuation character whenever you want to break an expression and continue it on another line, so lines aren't so long - can't just hit Enter key to break line.


CurrentDb.Execute "INSERT INTO tblSCSMedAns(SSNum, Agent, QID, Answer, AuditDte) VALUES('" & _
Me.SSNum & "', " & Me.cboAgent & ", " & i & ", " & Me("Check" & i) & ", #" & Date() & "#)"