By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,713 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,713 IT Pros & Developers. It's quick & easy.

Update recordset

P: 82
Hi, I hope someone can help me. Everything works fine with this code except it won't update the recordset. Any thoughts? I've done about everything I can think of and can't find any help on the Web.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdUpdate_SortOverall_Click()
  2.    Dim Sqlstr As String
  3.    Dim OSS As Integer, Diab As Integer, CVD As Integer, HT As Integer, new_sort As Double, old_sort As Double
  4.    Dim Num_Measures As Integer
  5.    Dim Update_Response As VbMsgBoxResult
  6.    Dim tmpSQL As String
  7.    Dim field_list As String
  8.    Dim rs As ADODB.Recordset
  9.  
  10.    Dim cmd As ADODB.Command
  11.    Dim cnn As New ADODB.Connection
  12.  
  13.     Set cnn = CurrentProject.Connection
  14.     Set cmd = New ADODB.Command
  15.  
  16.  On Error GoTo Err_cmdUpdate_SortOverall
  17.  
  18. field_list = Allfields_except_ID("PracticeTable", "PracticeID")
  19.  
  20. Screen.MousePointer = 11
  21. '01 april 2013 Practice ID and PracticeID removed as field no longer in table
  22.  
  23.      Sqlstr = "         Select PracticeTable.OPNumber," 'PracticeTable.[Practice ID], PracticeTable.PracticeID, PracticeTable.OPNumber, "
  24.      Sqlstr = Sqlstr & "  PracticeTable.PracticeName, PracticeTable.ChangeID, "
  25.      Sqlstr = Sqlstr & "  PracticeTable.OSSDisplay, PracticeTable.OSSScore, "
  26.      Sqlstr = Sqlstr & "  PracticeTable.DMDisplay , PracticeTable.DMScore, "
  27.      Sqlstr = Sqlstr & "  PracticeTable.CVDDisplay, PracticeTable.CVDScore, "
  28.       Sqlstr = Sqlstr & "  PracticeTable.Hypertension_Care, PracticeTable.Hypertension_Care_Score, "
  29.   'old code but ' out flagcurrent, isdeleted, OP0000, and order by
  30.      Sqlstr = Sqlstr & "  SortOverall "
  31.      Sqlstr = Sqlstr & "  FROM PracticeTable "
  32.      Sqlstr = Sqlstr & "    Where (FamilyMedicine=1 or InternalMedicine=1)"
  33.  
  34.  
  35.  Set rs = New ADODB.Recordset
  36. rs.Open Sqlstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  37.  
  38.      Do While Not rs.EOF
  39.           Num_Measures = 0
  40.  
  41.           OSS = AssignRatingValue(Nz(rs!OSSDisplay, ""))
  42.           If OSS > 0 Then Num_Measures = Num_Measures + 1
  43.  
  44.  
  45.           Diab = AssignRatingValue(Nz(rs!DMDisplay, ""))
  46.           If Diab > 0 Then Num_Measures = Num_Measures + 1
  47.  
  48.           CVD = AssignRatingValue(Nz(rs!CVDDisplay, ""))
  49.           If CVD > 0 Then Num_Measures = Num_Measures + 1
  50.  
  51.           'added 01 april 2013 for HT
  52.  
  53.           HT = AssignRatingValue(Nz(rs!Hypertension_Care, ""))
  54.           If HT > 0 Then Num_Measures = Num_Measures + 1
  55.  
  56.           '01 april 2013 added HT to formula and message box
  57.  
  58.           old_sort = Nz(rs!SortOverall, 0)
  59.           If Num_Measures > 0 Then new_sort = Round((OSS + Diab + CVD + HT) / Num_Measures, 2)
  60.  
  61.  
  62.  
  63.  
  64.           If old_sort <> new_sort Then
  65.              Update_Response = MsgBox(rs!PracticeName & " (" & rs!OPNumber & ")" & vbCrLf _
  66.                                       & vbCrLf _
  67.                                       & "OSS  = " & rs!OSSDisplay & "(" & rs!OSSScore & ")" & vbCrLf _
  68.                                       & "Diab = " & rs!DMDisplay & "(" & rs!DMScore & ")" & vbCrLf _
  69.                                       & "CVD  = " & rs!CVDDisplay & "(" & rs!CVDScore & ")" & vbCrLf _
  70.                                       & "HT  = " & rs!Hypertension_Care & "(" & rs!Hypertension_Care_Score & ")" & vbCrLf _
  71.                                       & vbCrLf _
  72.                                       & "Current SortOverall = " & old_sort & vbCrLf _
  73.                                       & "Calculated SortOverall: (" & OSS & " + " & Diab & " + " & CVD & " + " & HT & ") / " & Num_Measures & " = " & new_sort & vbCrLf _
  74.                                       & vbCrLf _
  75.                                       & "Would you like to update this practice's SortOverall?" _
  76.                             , vbYesNo _
  77.                             , "Update SortOverall?")
  78.  
  79.              If Update_Response = vbYes Then
  80.  
  81.  
  82.                  DoCmd.RunSQL tmpSQL, False
  83.  
  84.                  tmpSQL = "Update PracticeTable " _
  85.                        & "  Set     PracticeTable.SortOverall=" & new_sort _
  86.                        & " where PracticeTable.OPNumber=" & rs!OPNumber 
  87.  
  88.                 cnn.BeginTrans
  89.                    With cmd
  90.                       .CommandType = adCmdText
  91.                       .ActiveConnection = cnn
  92.                       .CommandText = tmpSQL
  93.                       .Execute
  94.                    End With
  95.                 cnn.CommitTrans
  96.                 tmpSQL = ""
  97.              End If
  98.  
  99.           End If
  100.  
  101.           rs.MoveNext
  102.      Loop
  103.  
  104.  
  105. Exit_cmdUpdate_SortOverall:
  106.  
  107.      Set rs = Nothing
  108.      Screen.MousePointer = 1
  109.  
  110.      Exit Sub
  111.  
  112. Err_cmdUpdate_SortOverall:
  113.     Screen.MousePointer = 1
  114.     MsgBox "Error while updating SortOverall:" & Err.Description & Err.Number
  115.     DoCmd.SetWarnings True
  116.  
  117.     Resume Exit_cmdUpdate_SortOverall
  118. End Sub
  119.  
  120.  
  121.  
  122.  
Apr 9 '13 #1
Share this Question
Share on Google+
6 Replies


10K+
P: 13,264
Write out the SQL that you are using to run the update and check that it is correct. Run it durectly against the database to see if it works there. Maybe it's not matching the values in your where clause.
Apr 9 '13 #2

P: 82
When I enter the SQL code and specify which OPNumber to update, it works fine. the problem seems to be
Expand|Select|Wrap|Line Numbers
  1. " where PracticeTable.OPNumber=" & rs!OPNumber 
. Any other suggestions? Much appreciated.
Apr 9 '13 #3

P: 82
Specifically it is saying the value in rs!OPNumber is not a valid column name. The column name is OPNumber and the rs!OPNumber is a value in the column for which I want the recordset to be updated with Line 85.
Apr 9 '13 #4

P: 82
Figured it out but new problem. Changed line 86 to be
Expand|Select|Wrap|Line Numbers
  1. & " where PracticeTable.OPNumber= '" & rs!OPNumber & "';"
but line 73 gives me unconsistent results if adding 0 + 0 +0 +0. Any thoughts anyone?
Apr 9 '13 #5

10K+
P: 13,264
Are you using the correct types? Integer vs String
Apr 9 '13 #6

P: 82
Yes, all are Integer. Figured it out. Added a new line.

Expand|Select|Wrap|Line Numbers
  1.  If Num_Measures = 0 Then new_sort = 0
those with zero number of measures now working. Thanks.
Apr 9 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.