Results 1 to 9 of 9
  1. #1
    Canadiangal is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    81

    Code working for all questions but question 2 on upgraded database


    I have upgraded this survey database from Access 95 to Access 2010. Everything works fine with the exception of Question 2 of the survey doesn't tally correctly. I did not write this code (and yes I know I need to make certain corrections to update it) and am unsure of what certain parts mean. If anyone has any suggestions, I would certainly appreciate it. I have commented where I could use further explanation. Perhaps that area is not the problem and someone else can see something more obvious. I can upload the database if necessary as well.
    Code:
    Private Sub Command20_Click()
      Dim db As Database
      Dim rsdata As Recordset, rstally As Recordset
      Dim rskey As Recordset, rstext As Recordset
      Dim Bdate As Date
      Dim Edate As Date
      Dim tst As String
      Dim txt As String, cnt As Integer
      Dim ans As Integer, j As Integer, k As Integer
      Dim xkey As String, xmonth As Integer, xquarter As Integer, xyear As Integer
      Dim xtype As Integer
      Dim keywant As String, qtrwant As Integer, yerwant As Integer
      Dim survey_form As Integer 'indicates which question set to use
      Dim qcvt(12) As Integer
      Dim btch As String
      Dim S(20) As Long
      Dim scores(20, 6) As Long
      Dim v1 As Long, v2 As Long
      Dim flipit As Boolean, refcode As String
      Dim msg, style, response
      
      Set db = CurrentDb()
      Set rsdata = db.OpenRecordset("Data", dbOpenTable)
      rsdata.Index = "IDNmb"
      Set rstally = db.OpenRecordset("Tally", dbOpenTable)
      Set rskey = db.OpenRecordset("Clinics", dbOpenTable)
      rskey.Index = "RecNmb"
      Set rstext = db.OpenRecordset("QText", dbOpenTable)
      rstext.Index = "RCode"
      qcvt(1) = 1
      qcvt(2) = 1
      qcvt(3) = 1
      qcvt(4) = 2
      qcvt(5) = 2
      qcvt(6) = 2
      qcvt(7) = 3
      qcvt(8) = 3
      qcvt(9) = 3
      qcvt(10) = 4
      qcvt(11) = 4
      qcvt(12) = 4
    '--------------------
      rskey.MoveFirst
      Do While Not rskey.EOF
        keywant = rskey!Key
        survey_form = 2 'Clinic Survey Form
        qtrwant = Forms!PreRep!TheQuarter
        yerwant = Forms!PreRep!TheYear
        Forms!Main!RpTitle = "Clinic Survey - " & rskey!Name
        GoSub DoTally
        msg = rskey!Name & " - Print ?"
        style = vbYesNo + vbCritical + vbDefaultButton2
        response = MsgBox(msg, style)
        If response = vbYes Then    ' User chose Yes.
          DoCmd.OpenReport "Report1", acViewPreview
          DoCmd.OpenReport "Report2", acViewPreview
        End If
        rskey.MoveNext
      Loop
    '--------------------
      rsdata.Close
      rstally.Close
      rskey.Close
      rstext.Close
    Exit Sub
    '=================================================
    DoTally:
        DoCmd.SetWarnings False
          DoCmd.RunSQL "delete * from Tally;"
          For j = 1 To 20
          For k = 1 To 6
            scores(j, k) = 0
          Next k
          Next j
        DoCmd.SetWarnings True
        rsdata.MoveFirst
        Do While Not rsdata.EOF
          If Len(rsdata!Batch) = 7 Then
            xmonth = Val(Left(rsdata!Batch, 2))
            xquarter = qcvt(xmonth)
            xyear = Val(Mid(rsdata!Batch, 4, 4))
            xtype = Nz(rsdata!Type, " ")
            xkey = Nz(rsdata!Clinic, " ")
          Else
            xquarter = 0
          End If
          If (xtype = 2) And (xkey = keywant) And (xquarter = qtrwant) And (xyear = yerwant) Then
            S(1) = Nz(rsdata!Q1)
            S(2) = Nz(rsdata!Q2)
            '---------
            If S(2) > 9 Then    'This If section might be the cause of the problem, but I am unsure of what is happening here. Can anyone explain?
            v1 = S(2)
            v2 = (v1 / 10)
            S(2) = v1 - (v2 * 10)
             End If
            '---------
            S(3) = Nz(rsdata!Q3)
            S(4) = Nz(rsdata!Q4)
            S(5) = Nz(rsdata!Q5)
            S(6) = Nz(rsdata!Q6)
            S(7) = Nz(rsdata!Q7)
            S(8) = Nz(rsdata!Q8)
            S(9) = Nz(rsdata!Q9)
            S(10) = Nz(rsdata!Q10)
            S(11) = Nz(rsdata!Q11)
            S(12) = Nz(rsdata!Q12)
            S(13) = Nz(rsdata!Q13)
            S(14) = Nz(rsdata!Q14)
            S(15) = Nz(rsdata!Q15)
            S(16) = Nz(rsdata!Q16)
            S(17) = Nz(rsdata!Q17)
            S(18) = Nz(rsdata!Q18)
            S(19) = Nz(rsdata!Q19)
            S(20) = Nz(rsdata!Q20)
            For k = 1 To 20
              If Nz(S(k), 0) > 0 Then
              scores(k, S(k)) = scores(k, S(k)) + 1
              scores(k, 6) = scores(k, 6) + 1
              End If
            Next k
          End If
        rsdata.MoveNext
        Loop
        '--------------
        For k = 1 To 20
          rstally.AddNew
          rstally!SurveyID = 1
          rstally!QNmb = k
          refcode = Format(survey_form, "00") & Format(k, "00")
          rstally!RCode = refcode
          rstext.Seek "=", refcode
          If rstext.NoMatch Then
            flipit = False
          Else
            flipit = rstext!Flip
          End If
          If flipit = True Then
            rstally!VeryGood = scores(k, 1)
            rstally!Good = scores(k, 2)
            rstally!Fair = scores(k, 3)
            rstally!Poor = scores(k, 4)
            rstally!Excellent = scores(k, 5)
            rstally!Total = scores(k, 6)
            If scores(k, 6) > 0 Then
              rstally!VeryGoodPct = scores(k, 1) / scores(k, 6)
              rstally!GoodPct = scores(k, 2) / scores(k, 6)
              rstally!FairPct = scores(k, 3) / scores(k, 6)
              rstally!PoorPct = scores(k, 4) / scores(k, 6)
              rstally!ExcellentPct = scores(k, 5) / scores(k, 6)
            End If
          Else
            rstally!Poor = scores(k, 1)
            rstally!Fair = scores(k, 2)
            rstally!Good = scores(k, 3)
            rstally!VeryGood = scores(k, 4)
            rstally!Excellent = scores(k, 5)
            rstally!Total = scores(k, 6)
            If scores(k, 6) > 0 Then
              rstally!PoorPct = scores(k, 1) / scores(k, 6)
              rstally!FairPct = scores(k, 2) / scores(k, 6)
              rstally!GoodPct = scores(k, 3) / scores(k, 6)
              rstally!VeryGoodPct = scores(k, 4) / scores(k, 6)
              rstally!ExcellentPct = scores(k, 5) / scores(k, 6)
            End If
          End If
          rstally.Update
        Next k
    '--------------
      Return
    End Sub!

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I can upload the database if necessary as well.
    Yes, please. Without data there is no way to know where the error is. Could be as simple as missing data.

  3. #3
    Canadiangal is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    81
    Where would the most secure place to upload the database be?

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Secure?? Depends. You could use Box (box.com) to get a free account. Then you would have to PM a link. The problem with that is only 1 person gets to see the dB.

    You could up load it here - thousands would be able to look at the dB. So you would need to change/delete any sensitive data (what most do).


    Before uploading, do a "Compact & Repair", then Zip it.

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Maybe From FAQ:
    How do I attach a file to a post?To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
    On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.
    To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.
    Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.
    What files types can I use? How large can attachments be?
    In the attachment window you will find a list of the allowed file types and their maximum sizes. Files that are larger than these sizes will be rejected. There may also be an overall quota limit to the number of attachments you can post to the board.
    How do I add an image to a post?
    If you have uploaded an image as an attachment, you can click the arrow next to the 'Attachment Icon' and select it from the list. This will be inserted into your post and can be located where you want it displayed.
    To include an image that is not uploaded as an attachment and is located on another website, you can do so by copying the full URL to the image, (not the page on which the image is located), and either pressing the 'Insert Image' icon or by typing [img] before the URL and [/img] after it, ensuring that you do not have any spaces before or after the URL of the image. You can insert photos from your albums (?) in this way too.



    Smilies

    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    Canadiangal is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    81
    Thanks, in an area for the long weekend with sketchy Internet. Will upload it when I am back on the 5th.

  7. #7
    Canadiangal is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    81

    Error found? Perhaps

    Quote Originally Posted by ssanfu View Post
    Yes, please. Without data there is no way to know where the error is. Could be as simple as missing data.
    You were correct in the regard of the data. It was wrong; however, I don't believe it was entered wrong since only question 2 had bad data. The options for data entry were 1, 2, 3, 4, or 5. From the 20 survey questions that were entered, only question 2 had bad data. I couldn't find anything wrong with the field so I thought perhaps it was corrupted, plus I couldn't duplicate the error after entering multiple surveys. I deleted out the field and put it back in again and set the appropriate restraints for the field. Now it can only have 1 character and must be either empty or 1, 2, 3, 4, or 5. I am hoping this will solve the issues. That being said, I want to clear out the code that I believe was put in to take bad data and reduce it to 0 However when I take it out I get a run error. This is the code I commented out.
    Code:
    If S(2) > 9 Then   
            v1 = S(2)
            v2 = (v1 / 10)
            S(2) = v1 - (v2 * 10)
             End If
    So in other words if the answer to question to is larger than 9, then create the variable v1 which would equal S(2), create another variable v2 and take v1 and divide by 10. So if S(2) is 10, then we would have the answer of 1 for v2. 10 - 10= 0, thus throwing out the question and messing up the tallies of the surveys. The complete code is listed above. Can anyone tell me what else I have to delete out here?

    Thank you in advance for any insight you can give.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Canadiangal View Post
    <snip> I want to clear out the code that I believe was put in to take bad data and reduce it to 0 However when I take it out I get a run error. <snip>
    What is the error code/description??

    Commenting out that bit of code shouldn't cause an error. At least it didn't for me when I executed this
    Code:
    Sub testIF()
        Dim v1 As Long, v2 As Long, S As Long
        v1 = 11
        v2 = (v1 / 10)
        S = v1 - (v2 * 10)
    End Sub
    You've made several changes at the same time. How do you know or why do you think it is the IF function code that is causing the error?



    It would really help if you posted your dB...........

  9. #9
    Canadiangal is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    81

    Thank you

    tI did error out, but all is fixed now. Thank you all for you help and suggestions. Part of the errors were variables declared as integers instead of strings, other errors that occurred that I thought were related to the code were actually set in place to make the end user aware of bad data. The later was non-helpful to the end user since they didn't really know how to look for the bad data. Once again I am forever grateful to this forum and to all of you that have the patience and the willingness to help a new user out.



    Quote Originally Posted by ssanfu View Post
    What is the error code/description??

    Commenting out that bit of code shouldn't cause an error. At least it didn't for me when I executed this
    Code:
    Sub testIF()
        Dim v1 As Long, v2 As Long, S As Long
        v1 = 11
        v2 = (v1 / 10)
        S = v1 - (v2 * 10)
    End Sub
    You've made several changes at the same time. How do you know or why do you think it is the IF function code that is causing the error?



    It would really help if you posted your dB...........

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

Similar Threads

  1. Upgraded database needs a print preview prior to printing
    By Canadiangal in forum Programming
    Replies: 7
    Last Post: 06-28-2017, 03:23 PM
  2. Replies: 2
    Last Post: 05-14-2017, 10:07 AM
  3. Replies: 2
    Last Post: 07-29-2014, 06:10 PM
  4. Replies: 4
    Last Post: 09-18-2012, 08:49 AM
  5. Bill of Materials Code Questions
    By SALPBE in forum Programming
    Replies: 4
    Last Post: 01-31-2011, 04:29 PM

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