Skip Option on form - how to auto update all questions with VBA

[Tman]

Hi all

I want to include an option on a survey form which will be a "Skip" button. Basically when the user selects this, I want the code to insert assigned values (responses) for questions that were on the form.

The survey contains 6 sections (each with a separate form). Within each "Section", there are 10 questions. A couple sections are optional, so if the user selects skip, that section closes, next one opens.

I know this is wrong below (although some parts work). The parts that work are the QuestionChoice value and long response values entered into the table. The part that doesn't work is the SurveyQuestionID part (it only updates for the current question, not for all questions in that section). The example below I have the SurveyQuestion value assigned as the current question on the form.

I don't know how to program it so that it auto inserts the assigned values for ALL questions in that section? I am thinking it needs the "SectionID" in there somewhere also, so the code can identify what questions are assigned to that section, and insert the assigned values for every question in that section?

appreciate any help, hopefully explained it well enough above!

Thanks
TB

Private Sub cmdSkip_Click()

Dim SurveyQuestionID
Dim QuestionChoiceID
Dim LongResponse

SurveyQuestionID = Me.SurveyQuestionID
QuestionChoiceID = 0


LongResponse = "No Additional Comments provided by assessor."

Dim strSQL As String
strSQL = "INSERT INTO tblTEMPSurveyLongResponse (SurveyQuestionID, QuestionChoiceID,LongResponse) "
strSQL = strSQL & "VALUES "
strSQL = strSQL & "(" & SurveyQuestionID & ", " & QuestionChoiceID & ", '" & Replace(LongResponse, "'", """") & "')"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.txtComments = ""


DoCmd.Close


End Sub

[Rawb]

Probably the easiest way would be to add a field to your Table that shows the section the question belongs to. Then, when someone chooses "Skip" you can run a Query "WHERE SectionID=currentSection"

That would update every question in the section.

[Tman]

Probably the easiest way would be to add a field to your Table that shows the section the question belongs to. Then, when someone chooses "Skip" you can run a Query "WHERE SectionID=currentSection"

That would update every question in the section.

yeah thanks Rawb. I do already have that, but i just am unsure of how to code that into the SQL, to insert to all questions in that section, namely assigning that value to;

SurveyQuestionID = Me.SurveyQuestionID

SurveyQuestionID = All questions where SectionID = etc

[Rawb]

Unfortunately I'm not good enough with SQL statements to resolve your issue using your existing code. It's not that using DoCmd.RunSQL won't work or that it's the "wrong" way to do what you want, it's just not how I'd do it.

I prefer to do things programmatically rather than through SQL.

That said, I'd be happy to take a look at your code and see if I can do it my way. If you're not that familiar with VBA though, it might not be as easy to update.

However, I did notice that you're using a temporary Table to store the answers in. Have you considered pre-populating the Table with all the questions at the start of the survey? Then, as the user answers them, you can just run Update Queries. It would also likely solve your problem since you could just run the following Query when the user decides to skip an entire section:

strSQL = "UPDATE tblTEMPSurveyLongResponse SET LongResponse='" & _
         LongResponse & "' WHERE SurveySectionID=" & SurveySectionID

DoCmd.RunSQL strSQL

This is assuming that SurveySectionID contains the value you've assigned to this section (and that your sections are identified by numbers, not by text).