Update table from form

[WickidWe]

In the Edit button on my form I am coming up the this error for this code.
could someone please show me where I have gone wrong and how to fix it?

[WickidWe]

I had the following for the add button and it works fine

CurrentDb.Execute "INSERT INTO tblEmpDetails(EmpId, Name, EmpNo, Roster, Shift, PermFctn)" & _
"VALUES(" & Me.txtEmpIDAdd & ", '" & Me.txtName & "', '" & _
Me.txtEmpNo & "', '" & Me.cboRoster & "', '" & Me.txtShift & "', '" & Me.cboPermFctn & "')"

I tried to exchange "INSERT INTO" for "UPDATE" but I got an error.

[June7]

Missing quote marks and apostrophes on each line:

"SET EmpID=" &

",Name='" &

...


Why do you need sql actions to insert/update record? Why not data entry/edit to record on bound form?

[WickidWe]

The reason I am not doing the bound form way is the only video I could find on how to
update, edit and delete records from a form was the one I am following. (i am a newbie)

I am now getting the this error but the whole thing highlights yellow when I click debug now.
I think it may have something to do with my last line




CurrentDb.Execute "UPDATE tblEmpDetails " & _
"SET EmpID=" & Me.cboSearchName.Column(0) & _
",Name='" & Me.txtName & "'" & _
",EmpNo='" & Me.txtEmpNo & "'" & _
",Roster='" & Me.cboRoster & "'" & _
",Shift='" & Me.txtShift & "'" & _
",PermFctn='" & Me.cboPermFctn & "'" & _
"WHERE EmpID=" & Me.cboSearchName.Column(0)

[June7]

I don't see anything wrong with the syntax. However, don't understand why allowing edit of EmpID field. Is this an autonumber type?

I suggest you search some more. Bound form for data entry/edit is basic Access functionality. What you are attempting is not fully utilizing Access capabilities and is making your life harder.

Access Help has guidelines on creating bound forms.

Access has built-in methods for finding existing records or can build code to do search/filter of form records.

An Access database can be built entirely without any code by simply using intrinsic features.

You will need to learn how to build reports and the process for binding a report object to data is essentially the same as for form. There are even wizards to do most of the work for you.

[WickidWe]

I will have to look into it,

and about the EmpId, this ID is a number but it is and needs to be a text field

[WickidWe]

is it possible that the last line is causing it to fail as the EmpID is as text

WHERE EmpID=" & Me.cboSearchName.Column(0)

[WickidWe]

I sorted that part out I just needed to put the ='" instead of just "

I now have a new problem.
this is the delete part of it

I have the following code Private Sub cmdDelete_Click()

If Not (Me.frmEmpDetailsSub.Form.Recordset.EOF And Me.frmEmpDetailsSub.Form.Recordset.BOF) Then
'confirm Delete msg
If MsgBox("Are You Sure You Want to Remove this Person From Existance?") = vbYes Then
'Delete Now
CurrentDb.Execute "DELETE FROM tblEmpDetails " & _
" WHERE EmpID='" & Me.cboSearchName.Column(0) & "'"
'refresh list
Me.frmEmpDetailsSub.Form.Requery
End If
End If
End Sub

when I click the delete button the msgbox comes up as it should and when I press yes absolutly nothing happens.
no error, just nothing?!?

[WickidWe]

never mind i forgot to put vbyesno in the msgbox
MsgBox("Are You Sure You Want to Remove this Person From Existance?") = vbYes Then