Results 1 to 3 of 3
  1. #1
    alliandrina is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    12

    Queries and codes

    I am trying to fix a database that I didn't design and really don't have the knowledge. I am hoping for a bit of help.

    The organization I am doing this with has a specific format for data entry that I need to use. All the questions for the surveys are on one sheet/form. All the questions on the form are in option boxes. I wonder if there is query I can use that will allow me to use the form and send the answers to the a normarlized table. Another thread on another site said it might be possible. I copied the code but I am not sure where to put it.

    Private Sub btnAppendResult_Click()
    On Error GoTo Err_btnAppendResult_Click
    Dim SqlString As String 'This it the sql Statement to be run each time
    Dim NumRecords As Integer 'This is the number of records to be appended
    Dim Count As Integer 'This is the count of each loop action
    Dim QStart As String 'This is the start of the Question control on the form the query will look for
    Dim SStart As String 'This is the start of the Score Control on the form the query will look for
    Dim txtQuestion As String 'This is the sql variable Question txt box control
    Dim txtScore As String 'This is the sql variable score txt box control


    QStart = "txtQ" 'Set the fixed part of the form Question Control Name
    SStart = "txtS" 'Set the fixed part of the form score control name
    Count = 1 'Set Count to 1 to Start
    NumRecords = Me.QuestionNumber 'Set NumRecords to be the value in the control QuestionNumber on your form
    Do Until Count = NumRecords 'Stop Loop when count reaches number of records on Form
    txtQuestion = QStart & Count 'Set Control Name to be the full name for this loop
    txtScore = SStart & Count 'Set Control Name to be the full name for this loop

    DoCmd.SetWarnings False 'Stops any warnings
    'Collect Data and append record to tblSurveyResults
    SqlString = "INSERT INTO tblSurveyResults (StudentID, SurveyDate, CounselorID, SchoolID, TeacherID, QuestionResponse) " & _
    "SELECT " & txtStudentID & " , Date(), " & txtCounselorID & ", " & SchoolID & ", " & TeacherID & ", " & QuestionResponse & "; "
    DoCmd.RunSQL SqlString 'Append Response to the tblSurveyResults
    DoCmd.SetWarnings True 'Start Warnings
    Count = Count + 1 'Add 1 to count
    Loop
    Exit_btnAppendResult_Click:
    Exit Sub
    Err_btnAppendResult_Click:
    MsgBox Err.Description
    Resume Exit_btnAppendResult_Click




    2nd questions- I know that there might be a way to take the table itself and wright a query that will move the data into a more normalized look/design. Is there a way to automate it though- having the data go through the non normalized table and then to the normarlized?

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Here is another bit of code you may wish to look at

    http://www.datawright.com.au/access_..._using_VBA.htm

  3. #3
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Looking at the code, it appears that it won't do what you want as it is written now.

    The insert SQL string syntax is incorrect. And it doesn't reference any control with a name like txtS1, txtS2 or txtQuestion or txtScore.

    Without seeing your dB or knowing more about the structure/forms, it is hard to suggest modifications to the code.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating distance between 2 zip codes
    By crowegreg in forum Programming
    Replies: 7
    Last Post: 06-13-2016, 07:20 PM
  2. Consolidation codes from different table.
    By suverman in forum Queries
    Replies: 3
    Last Post: 05-13-2011, 10:39 AM
  3. Zip Codes
    By Laurie B. in forum Access
    Replies: 6
    Last Post: 02-25-2011, 02:38 PM
  4. Visual Mapping Using Zip Codes?
    By kalisti in forum Reports
    Replies: 1
    Last Post: 01-03-2011, 07:26 PM
  5. basic query codes
    By joms222 in forum Queries
    Replies: 1
    Last Post: 03-20-2009, 11:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums