-
Nov 25th, 2013, 03:04 AM
#1
Thread Starter
Member
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
-
Nov 25th, 2013, 03:46 AM
#2
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
-
Nov 25th, 2013, 07:12 AM
#3
Re: Updating two tables with one submit button
Moved From The Codebank (which is for sharing code rather than posting questions )
-
Nov 25th, 2013, 07:19 AM
#4
Re: Updating two tables with one submit button
Try closing the first recordset before opening it a second time.
-
Nov 25th, 2013, 08:50 AM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|