Complicated Qry Problems

[Mr. Hero]

I have a form with 26 questions. Next to each question I have a drop down menu that has: YES, NO, N/A

My form pulls the ID from the main form, also the date does the same.
My problem is kind of complicated. I tried to create a Append qry for each drop down and a master qry to run all of them. In the Event procedure for the "OnClick" I am calling a append qry to record each quesiton as a new record. Below is a examle:
Q1: A1 - Yes
Q2: A2 - N/A
Q3: B1 - NO
Q4: B2 - Yes
and so forth for 26 total questions.

I have a table that has 3 Fields: ID, Question(Which stores the A1, A2, etc.), Answers(Which stores the YES, NO, N/A).

The cycle which I would like to accomplish is as follows:
From User Input(i.e. Drop down) -> QRY into a table(where each question is a record.)

I would greatly appreciate any assistance.

 

[highandwild]

Please see attached example of what you could do.

 

[Mr. Hero]

highandwild,

Wow, that is perfect! Thank you so much. I spent most of the weekend researching in how to create it straight into VBA and failed...

I have a question, I took a look at the VBA code, and I was wondering if I could assign the ID from another form instead of a drop down.

Thanks a million in advance for your amazing help!

 

[highandwild]

I'm glad that you like it.

If you change the reference to Me.txtID in the code under the submit button then it can take the ID from anywhere.

This link may help http://www.mvps.org/access/forms/frm0031.htm
or you could pass the ID in the openargs of the form.

Maybe best to prefix the ID field with something that tells you what the ID ia an ID of e.g CustomerID as too many ID fields is confusing.

 

[Mr. Hero]

Thank you for the advise. If is okay with you, I may have a few more questions as I continue.

Thanks!

 

[highandwild]

Ask away.

I just wish that there had been a forum like this when I was learning all of those years ago pre-internet days.

 

[Mr. Hero]

Ok,
I am stumped..
I made a change to a line of code.
strCriteria = "[ID] = " & Chr(34) & Me.txtID & Chr(34) & " and [Question] = " & Chr(34) & ctl.Tag & Chr(34) & ""
Also, I changed the Table [question] to TEXT from NUMBER. And in the form I change the Tags 1, 2, 3, 4, 5 --> A1, A2, B1, B2, C1.

When I ran the form I recieved a Enter Parater Value Dialog.

When I manually enter the A1, A2, etc. in, the table stores the values properly. But how do I get the form to enter the info without a dialog prompt?

 

[highandwild]

Highlighted lines need changing.

strSQL = "INSERT INTO tbl_Answers ( ID,Question,Answer)"
strSQL = strSQL & " SELECT " & Chr(34) & Me.txtID & Chr(34) & ","
strSQL = strSQL & Chr(34) & ctl.Tag & Chr(34) & ","
strSQL = strSQL & Chr(34) & Nz(ctl.Value, "") & Chr(34)

strCriteria = "[ID] = " & Chr(34) & Me.txtID & Chr(34) & " and [Question] = " & Chr(34) & ctl.Tag & Chr(34)

Although I would save the "A" and the question number in different fields as they have different meanings and ordering will be easier.

 

[Mr. Hero]

I think I have solved my problem. I have left the code to the original. When I changed the tags and the table datatype, I forgot to add double quotes around the new tag name, i.e. "A1"

 

[Mr. Hero]

I wanted to say that you have been extremely helpful. I am learning alot!

I still have one more question. I would like add a [Date] column to my table.
strSQL = "INSERT INTO tbl_Answers ( ID,Date,Question,Answer)"
strSQL = strSQL & " SELECT " & Chr(34) & Me.txtID & Chr(34) & ","
strSQL = strSQL & Me.txtDate & ","
strSQL = strSQL & ctl.Tag & ","
strSQL = strSQL & Chr(34) & Nz(ctl.Value, "") & Chr(34)
Would the Changes that I have added above in blue be correct?

 

[highandwild]

Is the date required the date that the answers are recorded?

Is it allowed to have the questions answered by the same ID but on a different date.

 

[Mr. Hero]

The Date will automatically be pulled from another input, like the ID. I have the ID and Date already working and being pulled when the Form opens so there is no need for any input because the fields are already populated. Below is what I have managed add to the code..

strSQL = "INSERT INTO tbl_Answers (ID,Date,Question,Answer)"
strSQL = strSQL & " SELECT " & Chr(34) & Me.txtID & Chr(34) & ","
strSQL = strSQL & Chr(34) & Me.txtDate & Chr(34) & ","
strSQL = strSQL & ctl.Tag & ","
strSQL = strSQL & Chr(34) & Nz(ctl.Value, "") & Chr(34)

strCriteria = "[ID] = " & Chr(34) & Me.txtID & Chr(34) & Chr(34) & Me.txtDate & Chr(34) & " And [Question] = " & ctl.Tag

But I can't seem to record it properly. I am recieving a INPUT INTO syntax error.

 

[boblarson]

If you aren't selecting anything for the values (from a table, you don't need SELECT...)

And dates get delimited by # and numbers by nothing. So, if ID is a number you would need to make these changes:

strSQL = "INSERT INTO tbl_Answers (ID,Date,Question,Answer)"
strSQL = strSQL & " Values (" & Me.txtID & ","
strSQL = strSQL & "#" & Me.txtDate & "#,"
strSQL = strSQL & Chr(34) & ctl.Tag & Chr(34) & ","
strSQL = strSQL & Chr(34) & Nz(ctl.Value, "") & Chr(34) & ")"

 

[Mr. Hero]

That is interesting,
How would I then properly re-write this
strCriteria = "[ID] = " & Chr(34) & Me.txtID & Chr(34) & [Date] = Me.txtDate & " And [Question] = " & ctl.Tag

Thanks in advance for you help.

 

[boblarson]

If ID is a number then:

strCriteria = "[ID] = " & Me.txtID & " AND [Date] =#" & Me.txtDate & "# And [Question] = " Chr(34) & ctl.Tag & Chr(34)

 

[Mr. Hero]

Okay, Let me try this again. Below is what is currently working; I would to add another field to my table with a Date/Time Datatype. But I am not sure how to write the strCriteria properly. Any assistant you can offer, I would greatly appreciate it!

Private Sub cmdCreateRecord_Click()
Dim ctl As Control
Dim strSQL As String
Dim strCriteria As String
DoCmd.SetWarnings False
For Each ctl In Me.Controls

If TypeOf ctl Is ComboBox And Mid(ctl.Name, 4, 1) = "A" Then

If Nz(ctl.Value, "") = "" Then
MsgBox "Please answer all questions", vbInformation, "Warning"
GoTo Exit_Handler
End If

strSQL = "INSERT INTO tbl_Answers ( ID,Question,Answer)"
strSQL = strSQL & " SELECT " & Chr(34) & Me.txtID & Chr(34) & ","
strSQL = strSQL & ctl.Tag & ","
strSQL = strSQL & Chr(34) & Nz(ctl.Value, "") & Chr(34)

strCriteria = "[ID] = " & Chr(34) & Me.txtID & Chr(34) & " and [Question] = " & ctl.Tag

If Nz(DLookup("[ID]", "tbl_Answers", strCriteria), "") = "" Then
DoCmd.RunSQL strSQL
Else
MsgBox "Answers have already been submitted", vbInformation, "Warning"
GoTo Exit_Handler
End If

End If

Next ctl

MsgBox "Answers submitted", vbInformation, "Information"
Exit_Handler:
DoCmd.SetWarnings True
End Sub

 

[boblarson]

First question -

Does the date field exist in your table?

Also, it should not be named DATE as that is an Access Reserved Word.

 

[Mr. Hero]

Bob,

Yes the field name now is InputDate.

 

[Mr. Hero]

I think I have got it solved. I tried this line that you provided earlier
strSQL & "#" & Me.txtDate & "#,"
and the info went into the table properly. Thank YOU Bob and highandwild for the amazing help!!!!!!!

 

[highandwild]

If you aren't selecting anything for the values (from a table, you don't need SELECT..

Is it all working as you want it now Mr Hero? I've just been for a little sleep but I'm back now.

That makes sense Bob but it doesn't seem to matter, are there any down sides of using SELECT, not that I will now.