runsql syntax

[Guest]

I'm trying to copy fields from a current form to a table not associated with
the form.
All the fields are Text and i'm getting a syntax error on UPDATE command
My code is as follows fired on a onClick button:

Private Sub Go_Click()
On Error GoTo Err_Go_Click
Dim sql As String
If Me.ConfirmCopy = True Then
'Copy fields from Prospect Form to Client Tables
sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _
"WHERE [ClientNum] = " & Me![ClientPK]

CurrentDb.Execute sql, dbFailOnError
MsgBox db.RecordsAffected & "Fields Copied"

'MsgBox "Tables Updated", vbOKOnly
Else
MsgBox "Box not checked", vbOKOnly
End If
Err_Go_Click:
MsgBox Err.Description

End Sub

 

[Ken Snell \(MVP\)]

Assuming that Initial_Call_Date is a date/time field:

sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = #" & _
Format(Me![Initial_Call_Date], "mm\/dd\/yyyy") & "#, " & _
"WHERE [ClientNum] = " & Me![ClientPK]

 

[Stuart McCall]

I'm trying to copy fields from a current form to a table not associated
with
the form.
All the fields are Text and i'm getting a syntax error on UPDATE command
My code is as follows fired on a onClick button:

Private Sub Go_Click()
On Error GoTo Err_Go_Click
Dim sql As String
If Me.ConfirmCopy = True Then
'Copy fields from Prospect Form to Client Tables
sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _
"WHERE [ClientNum] = " & Me![ClientPK]

CurrentDb.Execute sql, dbFailOnError
MsgBox db.RecordsAffected & "Fields Copied"

'MsgBox "Tables Updated", vbOKOnly
Else
MsgBox "Box not checked", vbOKOnly
End If
Err_Go_Click:
MsgBox Err.Description

End Sub

In addition to Ken's sage advice, you have a surplus comma, near the end of
this line:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _

IOW the line ought to read:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "' " & _

(then make Ken's alterations if needed)

 

[Ken Snell \(MVP\)]

In addition to Ken's sage advice, you have a surplus comma, near the end
of this line:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _

IOW the line ought to read:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "' " & _

(then make Ken's alterations if needed)

Good catch, Stuart. Thanks.

 

[Guest]

Ken and Stuart together have solved your immediate problem; however, your
post suggests database design improvements could be made.
To have one table for prospects and another for clients is not a really good
design. A better design is one table with a field designating the entity's
status. Then your sales people can keep track better because you could
identify them as:

Contacted
Prospect
Top Ten Prospect
Client
Closed
or whatever.

 

[Guest]

Thanks for all your comments. I mistakenly said the field was text when it
was date so good catch. I have the need to copy Date, Text and Memo fields to
one other table (tblclients). Do i use other SET statements or just run a
seperate sql statement for the other table. Me![ClientNum] is always in the
WHERE statement

I understand your comment on poor DB design but user does not want to
clutter Prospect details with Client info. Not every Prospect becomes a
client. just need to copy a few fields to avoid retyping.

Also, if the receiving field has text in it (it shouldn't, but if it does)
will this statement produce an error or advise that fileds are being copied
(replaced)

Ken and Stuart together have solved your immediate problem; however, your
post suggests database design improvements could be made.
To have one table for prospects and another for clients is not a really good
design. A better design is one table with a field designating the entity's
status. Then your sales people can keep track better because you could
identify them as:

Contacted
Prospect
Top Ten Prospect
Client
Closed
or whatever.
--
Dave Hargis, Microsoft Access MVP

I'm trying to copy fields from a current form to a table not associated with
the form.
All the fields are Text and i'm getting a syntax error on UPDATE command
My code is as follows fired on a onClick button:

Private Sub Go_Click()
On Error GoTo Err_Go_Click
Dim sql As String
If Me.ConfirmCopy = True Then
'Copy fields from Prospect Form to Client Tables
sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _
"WHERE [ClientNum] = " & Me![ClientPK]

CurrentDb.Execute sql, dbFailOnError
MsgBox db.RecordsAffected & "Fields Copied"

'MsgBox "Tables Updated", vbOKOnly
Else
MsgBox "Box not checked", vbOKOnly
End If
Err_Go_Click:
MsgBox Err.Description

End Sub

 

[Guest]

run a separate query.
As to mixing prospects and clients. That is what queries and filters are for.
--
Dave Hargis, Microsoft Access MVP

Thanks for all your comments. I mistakenly said the field was text when it
was date so good catch. I have the need to copy Date, Text and Memo fields to
one other table (tblclients). Do i use other SET statements or just run a
seperate sql statement for the other table. Me![ClientNum] is always in the
WHERE statement

I understand your comment on poor DB design but user does not want to
clutter Prospect details with Client info. Not every Prospect becomes a
client. just need to copy a few fields to avoid retyping.

Also, if the receiving field has text in it (it shouldn't, but if it does)
will this statement produce an error or advise that fileds are being copied
(replaced)

Ken and Stuart together have solved your immediate problem; however, your
post suggests database design improvements could be made.
To have one table for prospects and another for clients is not a really good
design. A better design is one table with a field designating the entity's
status. Then your sales people can keep track better because you could
identify them as:

Contacted
Prospect
Top Ten Prospect
Client
Closed
or whatever.
--
Dave Hargis, Microsoft Access MVP

I'm trying to copy fields from a current form to a table not associated with
the form.
All the fields are Text and i'm getting a syntax error on UPDATE command
My code is as follows fired on a onClick button:

Private Sub Go_Click()
On Error GoTo Err_Go_Click
Dim sql As String
If Me.ConfirmCopy = True Then
'Copy fields from Prospect Form to Client Tables
sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _
"WHERE [ClientNum] = " & Me![ClientPK]

CurrentDb.Execute sql, dbFailOnError
MsgBox db.RecordsAffected & "Fields Copied"

'MsgBox "Tables Updated", vbOKOnly
Else
MsgBox "Box not checked", vbOKOnly
End If
Err_Go_Click:
MsgBox Err.Description

End Sub

 

[Guest]

I'm getting the following error message
Syntax error (missing operator) in query expression "10Sep2007' 10Sep2007#'.

I'm using the following code:

Private Sub Go_Click()
On Error GoTo Err_Go_Click
Dim sql As String
If Me.ConfirmCopy = True Then
'Copy fields from Prospect Form to Client Tables

sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "' " & _
Format(Me![Initial_Call_Date], "dd-mmm-yyyy") & "#, " & _
"WHERE [ClientNum] = " & Me![ClientPK]

CurrentDb.Execute sql, dbFailOnError
MsgBox db.RecordsAffected & "Fields Copied"

'MsgBox "Tables Updated", vbOKOnly
Else
MsgBox "Box not checked", vbOKOnly
End If
Err_Go_Click:
MsgBox Err.Description

End Sub

I would like the date format to be 10-Sep-2007

In addition to Ken's sage advice, you have a surplus comma, near the end
of this line:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _

IOW the line ought to read:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "' " & _

(then make Ken's alterations if needed)

Good catch, Stuart. Thanks.

 

[Ken Snell \(MVP\)]

The data type for Initial_Call_Date field is text? Are you sure it's not
date/time?

Assuming that you indeed mean that it's text:

sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = '" & _
Format(Me![Initial_Call_Date], "dd-mmm-yyyy") & "' & _
" WHERE [ClientNum] = " & Me![ClientPK]


But, if it's date/time:

sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = #" & _
Format(Me![Initial_Call_Date], "mm\/dd\/yyyy") & "# & _
" WHERE [ClientNum] = " & Me![ClientPK]

--

Ken Snell
<MS ACCESS MVP>

I'm getting the following error message
Syntax error (missing operator) in query expression "10Sep2007'
10Sep2007#'.

I'm using the following code:

Private Sub Go_Click()
On Error GoTo Err_Go_Click
Dim sql As String
If Me.ConfirmCopy = True Then
'Copy fields from Prospect Form to Client Tables

sql = "UPDATE [tblSales_Log] " & _
"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "' " & _
Format(Me![Initial_Call_Date], "dd-mmm-yyyy") & "#, " & _
"WHERE [ClientNum] = " & Me![ClientPK]

CurrentDb.Execute sql, dbFailOnError
MsgBox db.RecordsAffected & "Fields Copied"

'MsgBox "Tables Updated", vbOKOnly
Else
MsgBox "Box not checked", vbOKOnly
End If
Err_Go_Click:
MsgBox Err.Description

End Sub

I would like the date format to be 10-Sep-2007

In addition to Ken's sage advice, you have a surplus comma, near the
end
of this line:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "', " & _

IOW the line ought to read:

"SET [Initial_Call_Date] = '" & Me![Initial_Call_Date] & "' " & _

(then make Ken's alterations if needed)

Good catch, Stuart. Thanks.