Visual Basic Forum for Visual Basic Programmers VB

[janbrane]

Please do help me in my problem. its urgent.
How will i update my existing records in VB?
im using ms-access as back-end.

my problem is, i was able to edit the 1st record but when i get to the 2nd record i want to update, it wont update. it says "it wont update coz it may have duplicates"

what will i do?


THIS IS THE CODE:

Private Sub cmd_save_Click()
Dim ans As Integer
ans = MsgBox("Do You Really Want To SAVE The Record?", vbYesNoCancel + vbQuestion, "ALERT!")
If ans = 6 Then
If (cbo_month.Text = "January") And (cbo_period.Text = "1: (01-15)") And (cbo_dept.Text = "HouseKeeping") Then
Adodc1.RecordSource = "Select * From house01jan"
Adodc1.Refresh
Adodc1.Recordset.AddNew
Adodc1.Recordset.Fields("IDNumber") = Trim(txt_idnumber.Text)
Adodc1.Recordset.Fields("Lastname") = Trim(txt_lastname.Text)
Adodc1.Recordset.Fields("Firstname") = Trim(txt_firstname.Text)
'Adodc1.Recordset.Fields("Location") = Trim(cbo_location.Text)
Adodc1.Recordset.Fields("Dept") = Trim(cbo_dept.Text)
Adodc1.Recordset.Fields("Rate") = Trim(txt_rate.Text)
Adodc1.Recordset.Fields("Days") = Trim(txt_regdays.Text)
Adodc1.Recordset.Fields("Hours") = Trim(txt_reghours.Text)
Adodc1.Recordset.Fields("OvertimeHours") = Trim(txt_othours.Text)
Adodc1.Recordset.Fields("OvertimePay") = Trim(txt_otpay.Text)
Adodc1.Recordset.Fields("SSS") = Trim(txt_sss.Text)
Adodc1.Recordset.Fields("Medicare") = Trim(txt_medicare.Text)
Adodc1.Recordset.Fields("Pag-ibig") = Trim(txt_pagibig.Text)
Adodc1.Recordset.Fields("Uniform") = Trim(txt_uniform.Text)
Adodc1.Recordset.Fields("CashAdvanceDed") = Trim(txt_cashadvance.Text)
Adodc1.Recordset.Fields("PreviousCashAdvance") = Trim(txt_previous.Text)
Adodc1.Recordset.Fields("PresentCashAdvance") = Trim(txt_present.Text)
Adodc1.Recordset.Fields("TotalDeduction") = Trim(txt_totalded.Text)
Adodc1.Recordset.Fields("TotalSalary") = Trim(txt_totalsalary.Text)
Adodc1.Recordset.Fields("NetSalary") = Trim(txt_netsalary.Text)
Adodc1.Recordset.Update



Adodc1.RecordSource = "Select * From CashAdvance"
Adodc1.Refresh
Adodc1.Recordset.Update
Adodc1.Recordset.Fields("IDNumber") = Trim(txt_idnumber.Text)
Adodc1.Recordset.Fields("Lastname") = Trim(txt_lastname.Text)
Adodc1.Recordset.Fields("Firstname") = Trim(txt_firstname.Text)
'Adodc1.Recordset.Fields("Location") = Trim(txt_location.Text)
'Adodc1.Recordset.Fields("Department") = Trim(txt_department.Text)
Adodc1.Recordset.Fields("Temp") = Trim(txt_previous.Text)
Adodc1.Recordset.Fields("PreviousCashAdvance") = Trim(txt_present.Text)
Adodc1.Recordset.Fields("PresentCashAdvance") = "0.00"
Adodc1.Recordset.Fields("Temp") = "0.00"
Adodc1.Recordset.Update <<<IM HAVING PROBLEMS HERE ON THE 2ND RECORD




MsgBox "Records has been saved!", vbOKOnly + vbInformation, "System Message"

txt_lastname.Text = ""
txt_firstname.Text = ""
txt_rate.Text = ""
txt_ratepday.Text = ""
txt_ratepmin.Text = ""
txt_ratepday.Text = ""
txt_reghours.Text = ""
txt_regdays.Text = ""
txt_othours.Text = "0"
txt_otpay.Text = "0.00"
txt_sss.Text = ""
txt_medicare.Text = ""
txt_pagibig.Text = ""
txt_uniform.Text = ""
txt_others.Text = "0.00"
txt_cashadvance.Text = ""
txt_present.Text = ""
txt_previous.Text = ""
txt_lateded.Text = ""
txt_latemins.Text = ""
txt_totalded.Text = ""
txt_totalsalary.Text = ""
txt_netsalary.Text = ""


With rsEmp
.MoveNext
If (.EOF) Then
MsgBox "Reached Last Record", vbOKOnly + vbInformation, "System Message"
.MoveLast
End If
Display
End With

With rsCAdv
If (.EOF) Then
.MoveLast
Else
.MoveNext
If (.EOF) Then
.MoveLast
End If
End If
DisplayCA
If Not txt_previous.Text = "0" Then
txt_cashadvance.Text = (Val(txt_previous.Text) + ((Val(txt_previous.Text) - 300) * 0.1))
Else
txt_cashadvance.Text = "0"
End If
End With
End If


ElseIf ans = 7 Then
MsgBox "Records was not saved!", vbOKOnly + vbInformation, "System Message"
txt_lastname.Text = ""
txt_firstname.Text = ""
txt_rate.Text = ""
txt_ratepday.Text = ""
txt_ratepmin.Text = ""
txt_ratepday.Text = ""
txt_reghours.Text = ""
txt_regdays.Text = ""
txt_othours.Text = "0"
txt_otpay.Text = "0.00"
txt_sss.Text = ""
txt_medicare.Text = ""
txt_pagibig.Text = ""
txt_uniform.Text = ""
txt_others.Text = "0.00"
txt_cashadvance.Text = ""
txt_present.Text = ""
txt_previous.Text = ""
txt_lateded.Text = ""
txt_latemins.Text = ""
txt_totalded.Text = ""
txt_totalsalary.Text = ""
txt_netsalary.Text = ""

End If
End Sub :confused: :confused:

[dougthomas]

Hi Jan,

Think it may be something to do with the "Select * from CashAdvance"
Bearing in mind you want to UPDATE (not insert) you need to find the record you want to update. The Select * will populate the Recordset with every record from the Table. As written your code will update the record at wherever the RecordSet pointer is - probably the last record in the set. NOT the one you want to update. You have an index on the table - is it perhaps id_number ?

If you change your SELECT to something like

SELECT * FROM CashAdvance WHERE IDNumber = "& "'" & trim(txt_idnumber.text) & "'"

(That is Double Quote Single Quote Double Quote before and after the &trim(txt_idnumber.text))

this should select just the record corresponding to txt_idnumber
you can then set the RecordSet fields and update.

By the way you don't need the "Adodc1.Recordset.Update" after the "Adodc1.Refresh" - all that is doing is updating every record with exactly the same contents they had before !
(Update is not like Add. When you INSERT (add) a record, you need to "make a space" for it in the RecordSet, that's why you have to do the .AddNew. With Update the record is already there and you are changing it in situ)

Hope that all makes sense

Regards
Doug