Syntax Error ( missing operator)

[lwallis]

Can anyone figure out why I am getting this error? I have attached a piece of my DB to this post.
I compared it to other like forms but I am not experienced enough to figure this out..

Thank you for your help
Lynda

 

[sensetech]

I can't even open the unzipped database! Access tells me that it's an unrecognised database format, even though it has a .accdb suffix.

 

[lwallis]

I uploaded it again, hopefully this works... I don't think I mentioned it but the problem occurs when I try to edit a record.

Thank you
Lynda

 

[pbaldy]

I would use a variable for the SQL and use this method to see what the finished SQL is. It's a lot easier to debug that way:

http://www.baldyweb.com/ImmediateWindow.htm

 

[lwallis]

Where would I insert the code to see the finished SQL? I apologize for this code. I have looked a videos and examples on the internet so it's a mix of things I have figured out over the last month. The is my one and only attempt at creating a database. Thanks for any help...

Private Sub cmdCAdd_Click()
'add data to table
If Me.txtCID.Tag & "" = "" Then
CurrentDb.Execute "INSERT INTO COURSE (BranchID, Course, CourseDescription, CourseLimitations) " & _
"VALUES (" & Me.cboBranch & ",'" & Me.txtCourse & "', '" & Me.txtCourseDescription & "'," ' & Me.txtCourseLimitations & "') "
Else
CurrentDb.Execute "UPDATE COURSE " & _
" SET CourseID=" & Me.txtCID & _
", Course='" & Me.txtCourse & "'" & _
", CourseDescription='" & Me.txtCourseDescription & "'" & _
", CourseLimitations='" & Me.txtCourseLimitations & "'" & _
"WHERE CourseID=" & Me.txtCID.Tag
End If
cmdCClear_Click

'refresh data in list on form
subCourse.Form.Requery

End Sub


Private Sub cmdCClear_Click()
Me.txtCID = ""
Me.txtCourse = ""
Me.txtCourseDescription = ""
Me.txtCourseLimitations = ""

Me.txtCourse.SetFocus
'set button edit to enable
Me.cmdCUpdate.Enabled = True
'change caption of button add to Add
Me.cmdCAdd.Caption = "Add"
'clear tag on txtID for reset new
Me.txtCID.Tag = ""

End Sub
Private Sub cmdCUpdate_Click()
'check whether there exists data in list
If Not (Me.subCourse.Form.Recordset.EOF And Me.subCourse.Form.Recordset.BOF) Then
'get data to text box control
With Me.subCourse.Form.Recordset
Me.txtCourse = .Fields("Course")
Me.txtCourseDescription = .Fields("CourseDescription")
Me.txtCourseLimitations = .Fields("CourseLimitations")
'store id of course in Tag of txtCID in case id is modified
Me.txtCID.Tag = .Fields("CourseID")
'change caption of button add to Update
Me.cmdCAdd.Caption = "Update"
'disable button edit
Me.cmdCUpdate.Enabled = False
End With
End If

 

[pbaldy]

At the beginning of the sub add:

Dim strSQL as String

before an Execute line you move the SQL to the variable:

strSQL = "INSERT INTO..."

then add this right after:

Debug.Print strSQL

and your Execute line changes to:

CurrentDb.Execute strSQL

 

[nanscombe]

I couldn't unzip the file but ... I did spot one error in the SQL

...
If Me.txtCID.Tag & "" = "" Then
CurrentDb.Execute "INSERT INTO COURSE (BranchID, Course, CourseDescription, CourseLimitations) " & _
"VALUES (" & Me.cboBranch & ",'" & Me.txtCourse & "', '" & Me.txtCourseDescription & [COLOR="Red"]"'," '[/COLOR] & Me.txtCourseLimitations & "') "
Else
...

You've got a ' and a " transposed. It should probably be ...

...
If Me.txtCID.Tag & "" = "" Then
CurrentDb.Execute "INSERT INTO COURSE (BranchID, Course, CourseDescription, CourseLimitations) " & _
"VALUES (" & Me.cboBranch & ",'" & Me.txtCourse & "', '" & Me.txtCourseDescription & [COLOR="Purple"]"','"[/COLOR] & Me.txtCourseLimitations & "') "
Else
...

The errant ' effectively comments out the rest of the line.