Recordset Help (1 Viewer)

[selenau837]

I'm a bit daft when it comes to recordsets to update a table.

I've searched the forum and can't find what I need. I was hoping I could find help here.

Below is my code I'm trying to use to update two tables. I use a query to get the SQL I needed, but it still isn't working.

I'm updating the tables from fields on an unbound form.

Thanks in advance.

Private Sub cmdAddEntry_Click()
On Error GoTo Err_cmdAddEntry_Click
Dim strSQL As String

Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

Dim rsRecordset As ADODB.Recordset
Set rsRecordset = New ADODB.Recordset


rsRecordset.ActiveConnection = CurrentProject.Connection
rsRecordset.LockType = adLockOptimistic
rsRecordset.CursorType = adOpenForwardOnly



strSQL = "UPDATE tblClaims INNER JOIN tblRCN ON tblClaims.intClaimID = tblRCN.intClaimID" & _
          "SET tblClaims.strClaimNumber = [frmLogEntry].[txtClaimNumber], " & _
            "tblClaims.strPatientID = [frmLogEntry].[txtPatientID], " & _
            "tblClaims.strClaimType = [frmLogEntry].[txtClaimType], " & _
            "tblClaims.dtmCancelDate = [frmLogEntry].[txtCancelDate], " & _
            "tblClaims.dtmProcessDate = [frmLogEntry].[txtProcessDate], " & _
            "tblClaims.strGroupName = [frmEntryLog].[txtGroupName], " & _
            "tblRCN.curPaymentExpected = [frmEntryLog].[txtPaymentExpected], " & _
            "tblRCN.curPaymentReceived = [frmEntryLog].[txtPaymentReceived], " & _
            "tblRCN.strRCNNumber = [frmEntryLog].[txtRCN];"
            
            
   rsRecordset.AddNew strSQL
   
   rsRecordset.Update
   rsRecordset.Close
   
            
            



Exit_cmdAddEntry_Click:
    Exit Sub

Err_cmdAddEntry_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEntry_Click
    
End Sub

 

[workmad3]

hmm... first thing I notice is that that update query will overwrite everything in the joined table. You need to put a WHERE clause in so it only updates specific records.

Secondly, I would probably suggest doing the update in 2 parts, one for each table. It makes things look a bit clearer. If you want it to be atomic (so if the prog crashes between the updates) then start a transaction so that the entire thing needs to be completed before the data is committed. Thats what I do in my update, I have about 8 tables updated seperately rather than trying to update them all at once.

The reason it might not work is that the inner join you have created may not be updateable. I can't remember exactly what is needed to make a view (i.e. a table constructed from a query) updateable, but not every view is. It could be thats why it isnt working.

Hope this helps

 

[selenau837]

I am trying to add it as a new record. So what where clause would I use?


Updating different tables at differnt times. That makes sense, but I am not sure how to do that.

Recordset, and I don't get along. I try to avoid useing them like the plauge, but this time I can't work around it.

 

[selenau837]

I updated my SQL statment and broke it into two.

strSQLClaims = "UPDATE tblClaims " & _
            "SET tblClaims.strClaimNumber = [frmLogEntry].[txtClaimNumber], " & _
            "tblClaims.strPatientID = [frmLogEntry].[txtPatientID], " & _
            "tblClaims.strClaimType = [frmLogEntry].[txtClaimType], " & _
            "tblClaims.dtmCancelDate = [frmLogEntry].[txtCancelDate], " & _
            "tblClaims.dtmProcessDate = [frmLogEntry].[txtProcessDate], " & _
            "tblClaims.strGroupName = [frmEntryLog].[txtGroupName], "
            
            
strSQLRCN = "Update tblRCN " & _
            "tblRCN.curPaymentExpected = [frmEntryLog].[txtPaymentExpected], " & _
            "tblRCN.curPaymentReceived = [frmEntryLog].[txtPaymentReceived], " & _
            "tblRCN.strRCNNumber = [frmEntryLog].[txtRCN]" & _
            "Where tblClaims.intClaimID = tblRCN.intClaimID;"            
            
   rsRecordset.AddNew strSQLClaims
   rsRecordset.AddNew strSQLRCN

 

[KeithG]

To add a new record with using rsRecordset.addnew you need to supply to parameters after the method. First a Field name or an array of field names and then the values.

 

[selenau837]

Here's a copy of my database.

See the entrylog form.

 

[Matty]

Sel,

There's two ways you can go about doing this -- one with a recordset and one without.

Using recordsets, you could do it this way:

Private Sub cmdAddEntry_Click()
On Error GoTo Err_cmdAddEntry_Click
 Dim strSQL As String

Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

Dim rsRecordset As ADODB.Recordset
Set rsRecordset = New ADODB.Recordset

strSQL = "SELECT * FROM tblClaims"
rstRecordset.Open strSQL, cnCurrent, adOpenForwardOnly
            
   with rsRecordset         
       .AddNew
       .Fields("strClaimNumber") = [frmLogEntry].[txtClaimNumber]
       .Fields("strPatientID") = [frmLogEntry].[txtPatientID]
       .
       .
       . (continue the pattern to fill the other fields in)
       .
       .
       .Fields("strGroupName") = [frmEntryLog].[txtGroupName]
       .Update
       .Close
   end with

Exit_cmdAddEntry_Click:
    Exit Sub

Err_cmdAddEntry_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEntry_Click
    
End Sub

Or you could code a straight SQL statement and run it:

strSQLClaims = "UPDATE tblClaims " & _
            "SET tblClaims.strClaimNumber = [frmLogEntry].[txtClaimNumber], " & _
            "tblClaims.strPatientID = [frmLogEntry].[txtPatientID], " & _
            "tblClaims.strClaimType = [frmLogEntry].[txtClaimType], " & _
            "tblClaims.dtmCancelDate = [frmLogEntry].[txtCancelDate], " & _
            "tblClaims.dtmProcessDate = [frmLogEntry].[txtProcessDate], " & _
            "tblClaims.strGroupName = [frmEntryLog].[txtGroupName], "

DoCmd.RunSQL strSQLClaims

 

[selenau837]

I gotcha, but how can I do that with joined tables. Use the correct joine statement in the SQL statment and use the addnew fuction of the recordset?


Thank you very much BTW.

 

[Matty]

I gotcha, but how can I do that with joined tables. Use the correct joine statement in the SQL statment and use the addnew fuction of the recordset?


Thank you very much BTW.

Yeah, rather than just typing in "SELECT * FROM

" as the recordset source, you should be able to use a SELECT statement with joins. Just make sure that the primary key from both tables are included in the SELECT statement, and that should make your recordset updatable.

Is there a reason why you want to do two tables at once? I've always found that doing them individually makes it easier to understand when I look back at the code.

 

[selenau837]

Yeah, rather than just typing in "SELECT * FROM

" as the recordset source, you should be able to use a SELECT statement with joins. Just make sure that the primary key from both tables are included in the SELECT statement, and that should make your recordset updatable.

Is there a reason why you want to do two tables at once? I've always found that doing them individually makes it easier to understand when I look back at the code.

If you look at my tables in my DB i attached you will see why. Unless I am doing it all wrong. It wouldnt' be the first time.

 

[Matty]

Is is because you need the intClaimID field to be filled into tblRCN?

 

[selenau837]

Is is because you need the intClaimID field to be filled into tblRCN?

Yes, sorry had to look. I'm trying to work on two dif DB at the same time.

 

[Matty]

If you're using the recordset example you can do it this way:

1. Make your claims recordset and add the new record like my example shows.
2. Make a second recordset for the RCN table and add your new record to that one. When you get to adding the intClaimID field, you can do something like this:

.Fields("intClaimID") = rsClaims("intClaimID")

since the current record in the Claims recordset is that new one you just made. Since it's been updated (the .Update line), that primary key field should be in the record.

3. Update your RCN table.
4. Close both recordsets.

 

[selenau837]

Below is my final code.
Thank you alot Matty, you're my hero!!

Private Sub cmdAddEntry_Click()
On Error GoTo Err_cmdAddEntry_Click
Dim strSQLClaims As String
Dim strSQLRCN As String


If txtGroupName.value = "" Or IsNull(txtGroupName.value) Then
        MsgBox "You must enter the Group Name."
        txtGroupName.SetFocus
     ElseIf txtRCN.value = " " Or IsNull(txtRCN.value) Then
        MsgBox "You must enter the RCN Number, if no RCN then enter 'None'"
        txtRCN.SetFocus
    ElseIf txtProcessDate.value = " " Or IsNull(txtProcessDate.value) Then
             MsgBox "You must enter a process date"
             txtProcessDate.SetFocus
    ElseIf txtClaimType = " " Or IsNull(txtClaimType.value) Then
        MsgBox "You must select ITs or Local for the Claim Type"
        txtClaimType.SetFocus
    ElseIf txtPatientID = " " Or IsNull(txtPatientID.value) Then
        MsgBox "You must enter the Patient ID Number"
        txtPatientID.SetFocus
End If

Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

Dim rsRecordset As ADODB.Recordset
Set rsRecordset = New ADODB.Recordset



strSQLClaims = "SELECT * FROM tblClaims"
rsRecordset.Open strSQLClaims, cnCurrent, adOpenForwardOnly, adLockOptimistic


            
   With rsRecordset
       .AddNew
       .Fields("strClaimNumber") = Me.txtClaimNumber.value
       .Fields("strPatientID") = Me.txtPatientID.value
       .Fields("strClaimType") = Me.txtClaimType.value
       .Fields("dtmCancelDate") = Me.txtCancelDate.value
       .Fields("dtmProcessDate") = Me.txtProcessDate.value
       .Fields("strGroupName") = Me.txtGroupName.value
       .Update
          End With
 
Set rsRecordset2 = New ADODB.Recordset

strSQLRCN = "SELECT * FROM tblRCN"
rsRecordset2.Open strSQLRCN, cnCurrent, adOpenForwardOnly, adLockOptimistic

With rsRecordset2
        .AddNew
        .Fields("intClaimID") = rsRecordset("intClaimID")
        .Fields("curPaymentExpected") = Me.txtPaymentExpected.value
        .Fields("curPaymentReceived") = Me.txtPaymentReceived.value
        .Fields("strRCNNumber") = Me.txtRCN.value
        .Update
 End With

rsRecordset.Close
rsRecordset2.Close


      Me.txtClaimNumber.value = " "
      Me.txtPatientID.value = " "
      Me.txtCancelDate.value = " "
      Me.txtProcessDate.value = " "
      Me.txtPaymentExpected.value = 0#
      Me.txtPaymentReceived.value = 0#
      Me.txtGroupName.value = " "
      Me.txtRCN.value = " "
       



        


Exit_cmdAddEntry_Click:
    Exit Sub

Err_cmdAddEntry_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEntry_Click
    
End Sub

 

[Matty]

Shouldn't you skip the updating of the table if you issue an error to the user?

I'm thinking you need to wrap an If statement around your recordset code:

Private Sub cmdAddEntry_Click()
On Error GoTo Err_cmdAddEntry_Click
Dim strSQLClaims As String
Dim strSQLRCN As String
dim blnError as boolean


If txtGroupName.value = "" Or IsNull(txtGroupName.value) Then
        blnError=True
        MsgBox "You must enter the Group Name."
        txtGroupName.SetFocus
     ElseIf txtRCN.value = " " Or IsNull(txtRCN.value) Then
        blnError=True
        MsgBox "You must enter the RCN Number, if no RCN then enter 'None'"
        txtRCN.SetFocus
    ElseIf txtProcessDate.value = " " Or IsNull(txtProcessDate.value) Then
             blnError=True
             MsgBox "You must enter a process date"
             txtProcessDate.SetFocus
    ElseIf txtClaimType = " " Or IsNull(txtClaimType.value) Then
        blnError=True
        MsgBox "You must select ITs or Local for the Claim Type"
        txtClaimType.SetFocus
    ElseIf txtPatientID = " " Or IsNull(txtPatientID.value) Then
        blnError=True
        MsgBox "You must enter the Patient ID Number"
        txtPatientID.SetFocus
End If

if blnError = False then

   Dim cnCurrent As ADODB.Connection
   Set cnCurrent = CurrentProject.Connection

   Dim rsRecordset As ADODB.Recordset
   Set rsRecordset = New ADODB.Recordset



   strSQLClaims = "SELECT * FROM tblClaims"
   rsRecordset.Open strSQLClaims, cnCurrent, adOpenForwardOnly,     adLockOptimistic


            
   With rsRecordset
       .AddNew
       .Fields("strClaimNumber") = Me.txtClaimNumber.value
       .Fields("strPatientID") = Me.txtPatientID.value
       .Fields("strClaimType") = Me.txtClaimType.value
       .Fields("dtmCancelDate") = Me.txtCancelDate.value
       .Fields("dtmProcessDate") = Me.txtProcessDate.value
       .Fields("strGroupName") = Me.txtGroupName.value
       .Update
          End With
 
   Set rsRecordset2 = New ADODB.Recordset

   strSQLRCN = "SELECT * FROM tblRCN"
   rsRecordset2.Open strSQLRCN, cnCurrent, adOpenForwardOnly,    adLockOptimistic

   With rsRecordset2
        .AddNew
        .Fields("intClaimID") = rsRecordset("intClaimID")
        .Fields("curPaymentExpected") = Me.txtPaymentExpected.value
        .Fields("curPaymentReceived") = Me.txtPaymentReceived.value
        .Fields("strRCNNumber") = Me.txtRCN.value
        .Update
   End With

   rsRecordset.Close
   rsRecordset2.Close


      Me.txtClaimNumber.value = " "
      Me.txtPatientID.value = " "
      Me.txtCancelDate.value = " "
      Me.txtProcessDate.value = " "
      Me.txtPaymentExpected.value = 0#
      Me.txtPaymentReceived.value = 0#
      Me.txtGroupName.value = " "
      Me.txtRCN.value = " "

End If       



        


Exit_cmdAddEntry_Click:
    Exit Sub

Err_cmdAddEntry_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEntry_Click
    
End Sub

This way you won't be updating your table until those checks are passed.

 

[selenau837]

Shouldn't you skip the updating of the table if you issue an error to the user?

I'm thinking you need to wrap an If statement around your recordset code:

Private Sub cmdAddEntry_Click()
On Error GoTo Err_cmdAddEntry_Click
Dim strSQLClaims As String
Dim strSQLRCN As String
dim blnError as boolean


If txtGroupName.value = "" Or IsNull(txtGroupName.value) Then
        blnError=True
        MsgBox "You must enter the Group Name."
        txtGroupName.SetFocus
     ElseIf txtRCN.value = " " Or IsNull(txtRCN.value) Then
        blnError=True
        MsgBox "You must enter the RCN Number, if no RCN then enter 'None'"
        txtRCN.SetFocus
    ElseIf txtProcessDate.value = " " Or IsNull(txtProcessDate.value) Then
             blnError=True
             MsgBox "You must enter a process date"
             txtProcessDate.SetFocus
    ElseIf txtClaimType = " " Or IsNull(txtClaimType.value) Then
        blnError=True
        MsgBox "You must select ITs or Local for the Claim Type"
        txtClaimType.SetFocus
    ElseIf txtPatientID = " " Or IsNull(txtPatientID.value) Then
        blnError=True
        MsgBox "You must enter the Patient ID Number"
        txtPatientID.SetFocus
End If

if blnError = False then

   Dim cnCurrent As ADODB.Connection
   Set cnCurrent = CurrentProject.Connection

   Dim rsRecordset As ADODB.Recordset
   Set rsRecordset = New ADODB.Recordset



   strSQLClaims = "SELECT * FROM tblClaims"
   rsRecordset.Open strSQLClaims, cnCurrent, adOpenForwardOnly,     adLockOptimistic


            
   With rsRecordset
       .AddNew
       .Fields("strClaimNumber") = Me.txtClaimNumber.value
       .Fields("strPatientID") = Me.txtPatientID.value
       .Fields("strClaimType") = Me.txtClaimType.value
       .Fields("dtmCancelDate") = Me.txtCancelDate.value
       .Fields("dtmProcessDate") = Me.txtProcessDate.value
       .Fields("strGroupName") = Me.txtGroupName.value
       .Update
          End With
 
   Set rsRecordset2 = New ADODB.Recordset

   strSQLRCN = "SELECT * FROM tblRCN"
   rsRecordset2.Open strSQLRCN, cnCurrent, adOpenForwardOnly,    adLockOptimistic

   With rsRecordset2
        .AddNew
        .Fields("intClaimID") = rsRecordset("intClaimID")
        .Fields("curPaymentExpected") = Me.txtPaymentExpected.value
        .Fields("curPaymentReceived") = Me.txtPaymentReceived.value
        .Fields("strRCNNumber") = Me.txtRCN.value
        .Update
   End With

   rsRecordset.Close
   rsRecordset2.Close


      Me.txtClaimNumber.value = " "
      Me.txtPatientID.value = " "
      Me.txtCancelDate.value = " "
      Me.txtProcessDate.value = " "
      Me.txtPaymentExpected.value = 0#
      Me.txtPaymentReceived.value = 0#
      Me.txtGroupName.value = " "
      Me.txtRCN.value = " "

End If       



        


Exit_cmdAddEntry_Click:
    Exit Sub

Err_cmdAddEntry_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEntry_Click
    
End Sub

This way you won't be updating your table until those checks are passed.

Good point....I'll update it now.

 

[selenau837]

Completed that...thanks. Always a step ahead. You know what...you keep this up, I'll have to create a shrine in your honor.

 

[Matty]

Make sure to take a photo of the shrine -- I'll keep that in my office.

 

[selenau837]

Make sure to take a photo of the shrine -- I'll keep that in my office.

Okie dokie