Can't figure out why this record won't update

[MyGreyGoose]

I have this function that updates a record. I can't get it to update 1 certain field, "SPR". It works fine when, a) after I close my application and re-open it, or b) when I substitute a hard value for the variable (aka, instead of SPRTemp, I use .5). Can anyone please take a look at this function and see what might be going wrong?

Private Sub txtSPRDiscount_Change()
Dim SPRTemp As Single ' for temporary storage of SPRDiscount
 
     lstProgram.Enabled = False
    If txtSPRDiscount <> "" Then SPRTemp = rPlace(txtSPRDiscount)
    
    If Not IsNull(SPRTemp) Then
        If IsNumeric(SPRTemp) Then
            If SPRTemp > 0 And SPRTemp < 100 Then
                SPRChange = True
                SPR2 = Val(Replace(txtSPRDiscount, "%", "")) / 100
                
                If Not performComputeData Then ' Compute Data based from the SPR input
                    Debug.Print "Error Computing Data"
                
                Else ' if there's no error lstProgram will be enabled and function performComputeData will be initiated
                    
                    SPRTemp = SPRTemp / 100
                    
                    performComputeData
                 '---- Update out_03_DealConfigs  based from SPR changes ----
                    
                    performMoveData
                    
                    editSPRTruckNotes txtNumber, ModelID, currConfigID, _
                    "SPR=" & SPRTemp & _
                    ", DealerNet=" & FinalDealerNet & _
                    ", FinalCost=" & FinalCost & _
                    ", FinalCurrentCost=" & FinalCurrentCost & _
                    ", FinalGoingToCost=" & FinalGoingToCost & _
                    ", Margin=" & FinalMargin & _
                    ", CurrentMargin=" & FinalCurrentMargin & _
                    ", GoingToMargin=" & FinalGoingToMargin & _
                    ", MarginPercent=" & AdjustMarginPercent & _
                    ", CurrentMarginPercent=" & AdjustCurrentMarginPercent & _
                    ", GoingToMarginPercent=" & AdjustGoingToMarginPercent
 
                 SPR2 = SPRTemp
                 lstProgram.Enabled = True
                 End If
                 
            Else
                txtSPR = 0
                
            End If
        End If
    End If
    MsgBox Val(SPRTemp)
End Sub
 
Function editSPRTruckNotes(ByVal DealNumber As String, ByVal ModelID As Long, _
                    ByVal ConfigId As Long, ByVal editString As String)
    
 '---- Function that will edit SPR in out_03_DealConfigs -----
    Dim rs As Recordset
    Dim dbObj As New dbFunctions
    Dim i As Integer
    dbObj.dbProp = strConn
    mSQL = "SELECT * FROM out_03_DealConfigs WHERE DealNo = '" & DealNumber & "' AND " & _
           " ModelID = " & ModelID & " AND ConfigurationID = " & ConfigId
    Set rs = dbObj.getRS(mSQL, isException, errDesc)
    If isException = 0 Then
        If Not rs.EOF Then
            If Not dbObj.editRecord("out_03_DealConfigs", editString, "DealNo = '" & DealNumber & "' AND " & _
           " ModelID = " & ModelID & " AND ConfigurationID = " & ConfigId, errDesc) Then
                Debug.Print "Error Editing SPR"
            End If
        End If
    End If
    Set rs = Nothing
    Set dbObj = Nothing
End Function

[Hack]

I assume the performComputeData is where your UPDATE query is being run, but I don't see the code for that function.

[MyGreyGoose]

Actually, performcomputeData simply updates some calculations. The update part is in the editSPRTruckNotes function.

[LaVolpe]

It does appear you are using some DLL or OCX, whatever allows you to create the dbFunctions object. What is that?

Assuming we don't have access to that class (not sure what it is). Does it have a commit property/function or something similar that allows you to update the recordset after the change is made?

[MyGreyGoose]

Good question. To be honest, I'm tweaking someone else's class module, dbFunctions, so I'm not exactly sure. I'm kinda a newbie when it comes to this. Here's the module I'm using. If this gets to complicated for a forum, I understand!

(abbreviated):

Option Explicit
 
Dim udlConn As String
 
Private Function setConn(ByRef errConn As Boolean, ByRef errDesc As String, ByRef errNumber As Long) As ADODB.Connection
    On Error GoTo errSetConn
    Dim cn As New ADODB.Connection
    Dim ConnString As String
    Dim pass As String
    pass = getDBPassword
    
    '----- Initialize the Connection String and retrieve the Password from dbAccess.ini -----
    ConnString = "Provider=MSDataShape;" & _
                 "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & udlConn & ";Jet OLEDB:Database Password=" & pass
 
    If errConn = False Then
        cn.CursorLocation = adUseClient
        cn.ConnectionString = ConnString
        cn.Open
        Set setConn = cn
    Else
        Err.Description = errDesc
        GoTo errSetConn
    End If
    Set cn = Nothing
    Exit Function
errSetConn:
    Set cn = Nothing
    errDesc = Err.Description
    errNumber = Err
    errConn = True
End Function
 
Public Function getRS(ByVal strSQL As String, _
                      ByRef isException As Long, ByRef errDesc As String) As ADODB.Recordset
                      
  '--- Initialize the recordset---
    On Error GoTo errGetRS
    Dim rs As New ADODB.Recordset
    Dim errConn As Boolean
    Dim errNumber As Long
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenDynamic
    rs.ActiveConnection = setConn(errConn, errDesc, errNumber)
    If errConn = True Then
        Err.Description = errDesc
 
        GoTo errGetRS
    Else
        rs.Open strSQL
        Set getRS = rs
    End If
    Set rs = Nothing
    Exit Function
errGetRS:
    errDesc = Err.Description
    isException = errNumber
End Function
 
Public Function addRecord(ByVal tableName As String, _
                          ByVal fieldList As String, _
                          ByVal valueList As String, _
                          ByRef errDesc As String) As Boolean
                          
 '--- Function adding Records in the Database ----
    On Error GoTo errAddRecord
    Dim isException As Long
    Dim strSQL As String
    strSQL = "INSERT INTO " & Trim(tableName) & "(" & Trim(fieldList) & ") VALUES(" & Trim(valueList) & ")"
    getRS strSQL, isException, errDesc
    If isException = 0 Then
        addRecord = True
    Else
        Err.Description = errDesc
        GoTo errAddRecord
    End If
    Exit Function
errAddRecord:
    errDesc = Err.Description
    addRecord = False
End Function
 
Public Function editRecord(ByVal tableName As String, _
                          ByVal setList As String, _
                          ByVal cond As String, ByRef errDesc As String) As Boolean
    On Error GoTo errEditRecord
    Dim isException As Long
    Dim strSQL As String
    
  '---- Function to Edit Records ----
    strSQL = "UPDATE " & Trim(tableName) & " SET " & Trim(setList) & " WHERE " & Trim(cond)
    getRS strSQL, isException, errDesc
    
    Debug.Print strSQL
    If isException = 0 Then
        editRecord = True
    Else
         Debug.Print errDesc
        Err.Description = errDesc
        GoTo errEditRecord
    End If
    Exit Function
errEditRecord:
    errDesc = Err.Description
    editRecord = False
End Function
 
Public Function deleteRecord(ByRef errDesc As String, ByVal tableName As String, _
                          Optional ByVal cond) As Boolean
    On Error GoTo errDelRecord
    Dim isException As Long
    Dim strSQL As String
    
   '----- Function to Delete a set/ certain Recordset-------
    
    If IsMissing(cond) Then
        strSQL = "DELETE FROM " & Trim(tableName)
    Else
        strSQL = "DELETE FROM " & Trim(tableName) & " WHERE " & Trim(cond)
    End If
    getRS strSQL, isException, errDesc
    If isException = 0 Then
        deleteRecord = True
    Else
        Err.Description = errDesc
        GoTo errDelRecord
    End If
    Exit Function
errDelRecord:
    errDesc = Err.Description
    deleteRecord = False
End Function
 
Public Property Let dbProp(ByVal dbCn As String)
    If dbCn <> "" Then
        udlConn = dbCn
    Else
        udlConn = ""
    End If
End Property

[LaVolpe]

I'm not the db guru on this site.
But I do question one thing that a db guru can answer.
Your EditRecord and DeleteRecord functions are calling getRS which does the "updating". In the getRS function, rs.Open strSQL is called. I always thought that rs.Execute should be called to modify the database, not rs.Open... rs.Open used to return a query whereas rs.Execute used to return/update a recordset.

Unless someone with more knowledge offers a solution, you might want to change rs.Open to rs.Execute and see how it affects your project.
edited: rs doesn't have an .Execute method. The connection does, so...
change rs.Open strSQL to Set rs = setConn(errConn, errDesc, errNumber).Execute(strSQL)
If the change works, other code in getRS should be modified. Here is what it might look like after tweaking:

Public Function getRS(ByVal strSQL As String, _
                      ByRef isException As Long, ByRef errDesc As String) As ADODB.Recordset
                      
  '--- Initialize the recordset---
    On Error GoTo errGetRS
    Dim rs As ADODB.Recordset
    Dim conn As ADODB.Connection
    Dim errConn As Boolean
    Dim errNumber As Long
    Set conn = setConn(errConn, errDesc, errNumber)
    If errConn = True Then
        Err.Description = errDesc

        GoTo errGetRS
    Else
        Set rs=conn.Execute(strSQL)
        Set getRS = rs
    End If
    Set rs = Nothing
    Exit Function
errGetRS:
    errDesc = Err.Description
    isException = errNumber
End Function

... It is not a good idea to use the Source argument of the Open method to perform an action query that does not return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed.

Quote source


P.S. see the edited note above.
Also, IMO the class is far from efficient. Every time you add, edit or delete a record a new connection is opened to the database. This is a performance hit since establishing the connection takes time. Adding/deleteing/editing records in a loop would be much faster if the connection was not created in each call. Also, many will tell you that explcitly setting ADODB objects to Nothing is preferred when the object is no longer needed. Every time you add/edit/delete a record, the connection and recordsets are not explicitly destroyed. The class can be modified easily to make it more efficient, by simply creating/storing the connection when dbProp is set and then destroying/releasing the connection during the class' Terminate event. Of course the indivdual routines would need to be tweaked so that they do not call setConn, but use/re-use the stored connection. And additional checks should be then applied: For instance a connection may terminate for various reasons: timed-out, server error, etc. Knowing this, the getRS function would validate the connection is still alive and reestablish if needed.

Realize this is not your project as you stated. You might want to inform the owner of comments made.

[techgnome]

Correct... .Open should be used to OPEN the data.... once you have the data you can manuipulate it, then send the changes back to the database. This can be done using the .UpdateBatch of the recordset, or by looping and using .Execute to call "action queries" ... BUT, if I remember right, the execute is on the COMMAND object, not the recordset itself.

-tg

[LaVolpe]

... If I remember right, the execute is on the COMMAND object, not the recordset itself.-tg

Yepper, caught that too. I modified my statement before you posted

[MyGreyGoose]

Guys, you totally pointed it out. I knew there was something along those lines that I was missing. .Execute makes so much more sense than .open... I knew something wasn't right there, but I had no clue what. Thanks for revising that code... so much better than what I had. This forum rocks!