Syntax error

[swmorin]

This is really staring to anger me. I can't seem to get the syntax correct for this query.

CurrentDb.Execute ("UPDATE tbl_courses" & _
"SET tbl_courses.COURSENUM =[Forms]![frm_createcourse_edit]![Text7]" & _
"WHERE tbl_courses.COURSEID)=[Forms]![frm_createcourse_edit]![Courseid];")

 

[vbaInet]

You need to expose those textbox references:

CurrentDb.Execute "UPDATE tbl_courses" & _
                  "SET COURSENUM = " & [Forms]![frm_createcourse_edit]![Text7] & " " & _
                  "WHERE COURSEID = " & [Forms]![frm_createcourse_edit]![Courseid] & ";")

If CourseNum or CourseID are String types you will need to wrap them in single quotes.

 

[swmorin]

Still not working here is the exact code from my db.

CurrentDb.Execute ("UPDATE tbl_courses" & _
"SET tbl_courses.COURSENUM = " & [Forms]![frm_createcourse_edit]![Text7] & " " & _
"WHERE tbl_courses.COURSEID = " & [Forms]![frm_createcourse_edit]![courseid] & ";")

 

[vbaInet]

What does "still not working" actually mean? Any errors? Does it not update any tables? Do you have error handling in place?

 

[swmorin]

Syntax error still.

Sorry about the misunderstanding

 

[vbaInet]

If CourseNum or CourseID are String types you will need to wrap them in single quotes.

I take it you didn't understand what I mentioned here? What are their data types?

 

[jdraw]

Remove the outer brackets and try again. And as vbaInet asked
If CourseNum or CourseID are String types you will need to wrap them in single quotes.

 

[swmorin]

tbl_courses.COURSENUM is a text field
tbl_courses.COURSEID is an auto number primary key

Is this what you mean?

 

[vbaInet]

Ok! So it should be this:

CurrentDb.Execute "UPDATE tbl_courses" & _
                  "SET COURSENUM = [COLOR=Red]'[/COLOR]" & [Forms]![frm_createcourse_edit]![Text7] & "[COLOR=Red]'[/COLOR] " & _
                  "WHERE COURSEID = " & [Forms]![frm_createcourse_edit]![Courseid] & ";"

Single quotes surround Text values.

 

[swmorin]

Still getting the syntax error in UPDATE statement

CurrentDb.Execute ("UPDATE tbl_courses" & _
"SET tbl_courses.COURSENUM = '" & [Forms]![frm_createcourse_edit]![Text7] & "' " & _
"WHERE tbl_courses.COURSEID = " & [Forms]![frm_createcourse_edit]![courseid] & ";")

 

[vbaInet]

Space after tbl_Courses. Final verison:

CurrentDb.Execute "UPDATE tbl_course[COLOR=Red]s "[/COLOR] & _
                  "SET COURSENUM = '" & [Forms]![frm_createcourse_edit]![Text7] & "' " & _
                  "WHERE COURSEID = " & [Forms]![frm_createcourse_edit]![Courseid] & ";"

You don't need the parentheses.

 

[swmorin]

That Worked thanks.

One more question. Do date/time fields require single quotes?

 

[vbaInet]

Date/Time fields require #.

 

[swmorin]

so it would look like this

#" & [form]![DateField] & "#

 

[vbaInet]

That's right!

But don't forget the proper reference to the control or field

#" & [Forms]![frm_createcourse_edit]![DateField] & "#

 

[swmorin]

Damnit now I am getting a missing operator error.

CurrentDb.Execute ("UPDATE tbl_courses " & _
"SET tbl_courses.COURSENUM = '" & [Forms]![frm_createcourse_edit]![Text7] & "' " & _
"tbl_courses.COURSEDATE = #" & [Forms]![frm_createcourse_edit]![Text3] & "# " & _
"tbl_courses.COURSETITLE = '" & [Forms]![frm_createcourse_edit]![Text1] & "' " & _
"tbl_courses.COURSEBLOCKS = " & [Forms]![frm_createcourse_edit]![Text5] & " " & _
"tbl_courses.COURSEDAYS = " & [Forms]![frm_createcourse_edit]![Text9] & " " & _
"tbl_courses.COURSEHOURS = " & [Forms]![frm_createcourse_edit]![Text11] & " " & _
"WHERE tbl_courses.COURSEID = " & [Forms]![frm_createcourse_edit]![courseid] & ";")

 

[jdraw]

You need to separate the fields with a comma
General format

Update Table
Set fld1 = Value1
, fld2 = value2
, fld3 = value3.......

 

[swmorin]

damn i am stupid