Updating a table with a command button

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to update a table via a command button on a form. The command
button opens the table and selects the particular record to update through an
SQL statement. I'm getting an error from the WHERE clause of the query due
to the fact that the field the WHERE clause accesses contains a comma. Here
is the code that I have.
..
..
..
If Not IsNull(Me!txtStaffMember) Then
SQLStmt = "SELECT * FROM RBES1 WHERE txtStaffMember = " &
Me!txtStaffMember
rst.Open SQLStmt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
..
..
..
The error states "Syntax error (comma) in query expression 'txtStaffMember =
xxxxx,x' ". I'm assumming this is from the WHERE clause as the field
txtStaffMember does contain a person's last name and first initial separated
by a comma. . Is there any way around this?
 
I'm trying to update a table via a command button on a form. The command
button opens the table and selects the particular record to update through an
SQL statement. I'm getting an error from the WHERE clause of the query due
to the fact that the field the WHERE clause accesses contains a comma. Here
is the code that I have.
.
.
.
If Not IsNull(Me!txtStaffMember) Then
SQLStmt = "SELECT * FROM RBES1 WHERE txtStaffMember = " &
Me!txtStaffMember
rst.Open SQLStmt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
.
.
.
The error states "Syntax error (comma) in query expression 'txtStaffMember =
xxxxx,x' ". I'm assumming this is from the WHERE clause as the field
txtStaffMember does contain a person's last name and first initial separated
by a comma. . Is there any way around this?
Click to expand...

Enclose the Text field in the syntactically required quote marks. If
the StaffMember field is a person's name, use " - ASCII code 34 - as
the delimiter, just in case you have someone named O'Brien on staff:

SQLStmt = "SELECT * FROM RBES1 WHERE txtStaffMember = " & Chr(34) _
& Me!txtStaffMember & Chr(34)

Do note that person's names are a dangerous choice for a unique field;
names are NOT unique, and someday you might have two staff members
both named "Smith, Jim".

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top