Results 1 to 5 of 5

Thread: Updating two tables with one submit button

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2013
    Posts
    54

    Updating two tables with one submit button

    Hi

    I have a submit button that I want it to update two tables, but what happens is it only update the first one then give me error on the second one.
    see a code below:

    Code:
     Set cnHPtest = New ADODB.Connection
          Set rsHPData = New ADODB.Recordset
          
          With cnHPtest
            .Provider = strDBProv
            .ConnectionString = strDBString
            .CommandTimeout = 1000
            .Open
          End With
    
        If Len(txtSalary.Text) = 0 Or Len(txtmCover.Text) = 0 Or Len(txtMember.Text) = 0 Or Len(txtmRate.Text) = 0 Or Len(comboRate.Text) = 0 Or Len(MaskEdDate.Text) = 0 Then
           
           MsgBox "Enter All The Fields Values"
                          
        Else
        
    '   // ------------------------------------------------------------------------//
    '  // Check if the Scheme Code exists in table Hpacc4, then update if it does //
    ' // ------------------------------------------------------------------------//
            
            With rsHPData
                .CursorLocation = adUseClient
                .LockType = adLockReadOnly
                .Open "Select * from Hpacc4 where Scheme = '" & txtScode.Text & "' AND RunMonth = '" & newDate & "' AND AccCode = '110'", cnHPtest, adOpenForwardOnly, adLockReadOnly
            End With
    
            If rsHPData.EOF And rsHPData.BOF Then
            
                MsgBox "No record found, verify Scheme Code with the date provided"
                
            Else
            
                tsql = "UPDATE Hpacc4 SET SalaryBill = '" & txtSalary.Text & "', Rate = '" & txtmRate.Text & "', RateType = '" & comboRate.Text & "', Membership = '" & txtMember.Text & "', Cover = ' " & txtmCover & " ' Where Scheme = '" & txtScode.Text & "' AND RunMonth = '" & newDate & "' AND AccCode = '110'"
                
                cnHPtest.Execute (tsql)
                MsgBox "Updated"
                
            End If
            
            
    '   // ------------------------------------------------------------------------//
    '  // Check if the Scheme Code exists in table Hpacc4, then update if it does //
    ' // ------------------------------------------------------------------------//
            
            With rsHPData
                .CursorLocation = adUseClient
                .LockType = adLockReadOnly
                .Open "Select * from hpdata where Scheme = '" & txtScode.Text & "' ", cnHPtest, adOpenForwardOnly, adLockReadOnly
            End With
    
            If rsHPData.EOF And rsHPData.BOF Then
            
                MsgBox "No record found, verify Scheme Code provided"
                
            Else
            
                tsql = "UPDATE hpdata SET AnnualSalary = '" & txtSalary.Text & "', Rate = '" & txtmRate.Text & "', RateType = '" & comboRate.Text & "', Membership = '" & txtMember.Text & "', Cover = ' " & txtmCover & " ' Where Scheme = '" & txtScode.Text & "' "
                
                cnHPtest.Execute (tsql)
                MsgBox "Updated"
                
            End If
    
        End If
    Last edited by Siddharth Rout; Nov 25th, 2013 at 04:37 AM. Reason: Added Code Tags

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: Updating two tables with one submit button

    What error do you get on which line?
    Have you stepped through the code using the debugger?

    Please use [code] [/code] tags when posting code

  3. #3
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Updating two tables with one submit button

    Moved From The Codebank (which is for sharing code rather than posting questions )

  4. #4
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: Updating two tables with one submit button

    Try closing the first recordset before opening it a second time.

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Updating two tables with one submit button

    You should not even need a select statement in there, there is an optional parameter on the .execute method that returns the number of records affected

    Using this option you can execute the update and check that result if >0 then the record was present if <1 then the record was not found
    Code:
        Dim Result as long
        Set cnHPtest = New ADODB.Connection
        With cnHPtest
            .Provider = strDBProv
            .ConnectionString = strDBString
            .CommandTimeout = 1000
            .Open
        End With
        If Len(txtSalary.Text) = 0 Or Len(txtmCover.Text) = 0 Or Len(txtMember.Text) = 0 Or Len(txtmRate.Text) = 0 Or Len(comboRate.Text) = 0 Or Len(MaskEdDate.Text) = 0 Then
            MsgBox "Enter All The Fields Values"
        Else
    '        // ------------------------------------------------------------------------//
    '        // Check if the Scheme Code exists in table Hpacc4, then update if it does //
    '        // ------------------------------------------------------------------------//
            tsql = "UPDATE Hpacc4 SET SalaryBill = '" & txtSalary.Text & "', Rate = '" & txtmRate.Text & "', RateType = '" & comboRate.Text & "', Membership = '" & txtMember.Text & "', Cover = ' " & txtmCover & " ' Where Scheme = '" & txtScode.Text & "' AND RunMonth = '" & newDate & "' AND AccCode = '110'"
            cnHPtest.Execute tsql, result
            If result > 0 Then
                MsgBox "Updated"
            Else
                MsgBox "No record found, verify Scheme Code with the date provided"
            End If
    '        // ------------------------------------------------------------------------//
    '        // Check if the Scheme Code exists in table Hpacc4, then update if it does //
    '        // ------------------------------------------------------------------------//
            tsql = "UPDATE hpdata SET AnnualSalary = '" & txtSalary.Text & "', Rate = '" & txtmRate.Text & "', RateType = '" & comboRate.Text & "', Membership = '" & txtMember.Text & "', Cover = ' " & txtmCover & " ' Where Scheme = '" & txtScode.Text & "' "
            
            cnHPtest.Execute tsql, result
            If result > 0 Then
                MsgBox "Updated"
            Else
                MsgBox "No record found, verify Scheme Code provided"
            End If
        End If

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width