Please go thru this code,,,,,UPDATE QUERY....

[Vbbeginner07]

Private Sub cmdEdit_Click()
mName = Trim(ListView1.SelectedItem)
mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
'=MsgBox "You have selected the employee" & mName & "Continue", vbOKCancel, "PAYROLL"
If mesge = vbOK Then
    rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
    Form1.txtid.Text = rs!id
    Form1.txtname.Text = rs!Name
    Form1.txtwhr.Text = rs!whours
    Form1.txtrate.Text = rs!Rate
    Form1.txtorate.Text = rs!otrate
    txtid.Enabled = False
    'txtname.SetFocus
    Form1.txtname.SetFocus
    rs.Close
Set rs = Nothing
 
   Else
conn.Execute "update empdetail set Name= '" & txtname & "',whours='" & txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "'"
MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
   End If
 

the code above is to update the fields other than id in aparticular form,please go thru it,that is not working???

[QVeen72]

Hi,

Not very clear as what you want to do here...
You ask a "Want to update", If user clicks OK, then You are displaying the records, or else, You are Running an Update Query..

What you are wanting to do here...?

REgards
Veena

[Vbbeginner07]

Hi,
Not very clear as what you want to do here...
You ask a "Want to update", If user clicks OK, then You are displaying the records, or else, You are Running an Update Query..
What you are wanting to do here...?
REgards
Veena

i click one data from a listview then clicks ok,after that i can change all fields except id,which is a diasbled textbox,then after that i click update...then i need the message "updated"

[debasisdas]

You need to add the WHERE clause to the update query and pass the id to it.

[QVeen72]

Hi,

OK, in that case, Keep 2 seperate buttons: "Edit" and "Update"

Wtite this code in Edit_Click Event:

Private Sub cmdEdit_Click()
    mName = Trim(ListView1.SelectedItem)
    rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
    Form1.txtid.Text = rs!id
    Form1.txtname.Text = rs!Name
    Form1.txtwhr.Text = rs!whours
    Form1.txtrate.Text = rs!Rate
    Form1.txtorate.Text = rs!otrate
    txtid.Enabled = False
    Form1.txtname.SetFocus
    rs.Close
    Set rs = Nothing
End Sub
 

And Now Write This Code in Update_Click event:

Private Sub cmdUpdate_Click()
    mName = Trim(ListView1.SelectedItem)
    mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
    If mesge = vbOK Then
        conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &  txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = '" &     trim(txtID.Text) & "' "
    MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
    End If
End Sub
 

Both can be Clubed and done in Single Command button, depending on the Caption, but this will be easy to debug for beginner..


Regards
Veena

[Vbbeginner07]

Hi,

Both can be Clubed and done in Single Command button, depending on the Caption, but this will be easy to debug for beginner..
Regards
Veena

veena,
that means we can combine both codes as i have done in #1,if so ny bugs occurs or should have to add enough codings to for that being under a single command button,nythng wrong please explain........regarding that...Nyway Thanks in advance for ur reply

[QVeen72]

Hi,

Yes, you can Club Both Functions in One Command button . In Form Load or design time make the caption of cmdEdit = "Edit"
and change your code this way :

Private Sub cmdEdit_Click()
mName = Trim(ListView1.SelectedItem)
If cmdEdit.Caption = "Edit" Then
    rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
    Form1.txtid.Text = rs!id
    Form1.txtname.Text = rs!Name
    Form1.txtwhr.Text = rs!whours
    Form1.txtrate.Text = rs!Rate
    Form1.txtorate.Text = rs!otrate
    txtid.Enabled = False
    Form1.txtname.SetFocus
    rs.Close
    Set rs = Nothing
    cmdEdit.Caption = "Update"
Else
    mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
    If mesge = vbOK Then
        conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = '" & txtID.Text & "'"
        MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
   End If
   cmdEdit.Caption = "Edit"
    Form1.txtid.Text = ""
    Form1.txtname.Text =""
    Form1.txtwhr.Text = ""
    Form1.txtrate.Text = ""
    Form1.txtorate.Text = ""
End If
 

Regards
Veena

[Vbbeginner07]

Hi,

Yes, you can Club Both Functions in One Command button . In Form Load or design time make the caption of cmdEdit = "Edit"
and change your code this way :

Private Sub cmdEdit_Click()
mName = Trim(ListView1.SelectedItem)
 If cmdEdit.Caption = "Edit" Then    rs.Open "select * from empdetail where name = '" & mName & "'", conn, adOpenStatic, adLockOptimistic
    Form1.txtid.Text = rs!id
    Form1.txtname.Text = rs!Name
    Form1.txtwhr.Text = rs!whours
    Form1.txtrate.Text = rs!Rate
    Form1.txtorate.Text = rs!otrate
    txtid.Enabled = False
    Form1.txtname.SetFocus
    rs.Close
    Set rs = Nothing
    cmdEdit.Caption = "Update"
Else
    mesge = MsgBox("Are you Sure you Want to Update " & mName & "?", vbOKCancel, "ERROR")
    If mesge = vbOK Then
        conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = '" & txtID.Text & "'"        MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS"
   End If
   cmdEdit.Caption = "Edit"
    Form1.txtid.Text = ""
    Form1.txtname.Text =""
    Form1.txtwhr.Text = ""
    Form1.txtrate.Text = ""
    Form1.txtorate.Text = ""
End If
 

Regards
Veena

the control moves from,the first cmdedit.caption =edit to the update statement
where i have kept the caption in design time to edit,error showing cannot convert varchar to numeric,

[QVeen72]

Hi,

Well, You must be having ID as numeric field, change the update statement to :

conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = " _
&  Val(txtID.Text)
MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS
 

Regards
Veena

[Vbbeginner07]

Hi,

Well, You must be having ID as numeric field, change the update statement to :

conn.Execute "update empdetail set Name= '" & txtname & "',whours='" &     txtwhrs & "',rate ='" & txtrate & "',otrate ='" & txtorate & "' Where ID = " _
&  Val(txtID.Text)
MsgBox "UPDATED NEW DETAILS", vbOKOnly, "UPDATED RECORDS
 

Regards
Veena

Thanx VEENA ,now its working!!!