Microsoft Access/Updating Record

Question

Can anyone help me with the below code to edit the records please?Im trying to edit but there is a Syntax error and I cant fix that. See the error that appears below. Run Time Error '3075' Syntax Error(missing operator) in query expresion 'TypeID='.

Thanks

Private Sub oshaadd_Click()
'On Error Resume Next

   If (IsNull(Me.oshaID) Or (Me.oshaID = "") And IsNull(Me.oshatype) Or              (Me.oshatype = "")) Then
     MsgBox "please fill required fields!", vbInformation, "Information"
      Exit Sub
   End If

If Me.oshaID.Tag & "" = "" Then
    CurrentDb.Execute "INSERT INTO osha(TypeID, OSHA)" & _
    "VALUES ('" & Me.oshaID & "', '" & Me.oshatype & "')"
        If MsgBox("Added", vbOKOnly) Then
            Me.osha_subform.Form.Requery
        End If
    Else

    CurrentDb.Execute "UPDATE osha " & _
    "SET TypeID =" & Me.oshaID & _
    ", OSHA = '" & Me.oshatype & "'" & _
   "WHERE TypeID =" & Me.oshatype.Tag
       MsgBox "Updated", vbInformation, "Information"
       Me.oshaadd.Caption = "Add"
       Me.oshaedit.Enabled = True
End If
Me.osha_subform.Form.Requery
End Sub



Private Sub oshaedit_Click()
On Error Resume Next
If Not (Me.osha_subform.Form.Recordset.EOF And     Me.osha_subform.Form.Recordset.BOF) Then
    With Me.osha_subform.Form.Recordset
        Me.oshaID = .Fields("TypeID")
        Me.oshatype = .Fields("OSHA")

        Me.oshaID.Tag = .Fields("TypeID")
        Me.oshaadd.Caption = "Update"
        Me.oshaedit.Enabled = False
     End With
End If
End Sub

Place the SQL query first in a string and inspect the string, e.g.`strSQL = "UPDATE osha " & ...`. If `TypeID` is the primary key, then `Me.oshatype.Tag` must be the current key before you update the key. — Paul Ogilvie, Jun 23 '15 at 22:28

score 2 · Accepted Answer · answered Jun 23 '15 at 20:23

2

You forget to put the database single quote around Me.oshaID and Me.oshatype.Tag in the UPDATE SQL statement:

CurrentDb.Execute "UPDATE osha " & _
    "SET TypeID ='" & Me.oshaID & "'" & _
    ", OSHA = '" & Me.oshatype & "'" & _
    " WHERE TypeID ='" & Me.oshatype.Tag &"';"

answered Jun 23 '15 at 20:23

Paul Ogilvie

25,048
4
23
41

Paul, now its displaying MsgBox as Updated but the record still remains the same – Key K Jun 23 '15 at 20:29

score 1 · Answer 2 · answered Jun 23 '15 at 20:19

1

CurrentDb.Execute "UPDATE osha " & _
"SET TypeID =" & Me.oshaID & _
", OSHA = '" & Me.oshatype & "'" & _
"WHERE TypeID =" & Me.oshatype.Tag
 MsgBox "Updated", vbInformation, "Information"
 Me.oshaadd.Caption = "Add"
 Me.oshaedit.Enabled = True

In this block of your code where you have "SET TypeID =" and "WHERE TypeID =" try adding a space after the equals sign so it reads "TypeID = ". If that doesn't resolve the error try adding single quotes around the value you are assigning to TypeID (TypeID = '" & Me.oshaID & _"',)

answered Jun 23 '15 at 20:19

Alex B.

58
7

If you put a break on the CurrentDb.Execute line are the values of Me.oshaID, Me.oshatype, and Me.oshatype.Tag what you are expecting to see? – Alex B. Jun 23 '15 at 20:52
can i send you in e mail if you can check it please, Im realy pissed off all day with this – Key K Jun 23 '15 at 20:59

Microsoft Access/Updating Record

Thanks

2 Answers2