Update subform from Main form

[Guest]

Trying to use command button on main form
to update subform.
I get Syntax error (Missing operator) in query expression
DoCmd.SetWarnings False
strSql = "UPDATE RegSaleSelect SET TAX1_SALES = " &
Int(CDec(Me.txtSubTotal.Value + 0.005) * 100) / 100 _
& "WHERE TAX1_SALES > 0;"

DoCmd.RunSQL strSql
Me.Requery
DoCmd.SetWarnings True

 

[tina]

if the posted code is an *exact* paste from your module, you're missing a
space in front of the WHERE clause. try replacing

& "WHERE TAX1_SALES > 0;"

with

& " WHERE TAX1_SALES > 0;"

hth

 

[Guest]

Looks like you have a couple of problems. First, you have a runtogether
sentence where your WHERE starts, you should put your ampersand before the
line continuation, I believe your calculation should be enclosed in quotes,
and you don't need the semi-colon at the end.

strSql = "UPDATE RegSaleSelect SET TAX1_SALES = " &
"Int(CDec(" & Me.txtSubTotal.Value & " + 0.005) * 100) / 100 " & _
"WHERE TAX1_SALES > 0"

I may be off as far as the calculation in quotes goes, as I can't remember
if that has to be in the string or outside of the string, so if the above
doesn't work try this:

strSql = "UPDATE RegSaleSelect SET TAX1_SALES = " & _
Int(CDec(Me.txtSubTotal.Value + 0.005) * 100) / 100 & _
" WHERE TAX1_SALES > 0"

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

 

[Stuart McCall]

Trying to use command button on main form
to update subform.
I get Syntax error (Missing operator) in query expression
DoCmd.SetWarnings False
strSql = "UPDATE RegSaleSelect SET TAX1_SALES = " &
Int(CDec(Me.txtSubTotal.Value + 0.005) * 100) / 100 _
& "WHERE TAX1_SALES > 0;"

DoCmd.RunSQL strSql
Me.Requery
DoCmd.SetWarnings True

Make it easy on yourself by doing the calculation first:

calc = Int(CDec(Me.txtSubTotal.Value + 0.005) * 100) / 100
strSql = "UPDATE RegSaleSelect SET TAX1_SALES = " & calc & " WHERE
TAX1_SALES > 0;"

Much more readable, wouldn't you agree?

Also, you'd be better off using CurrentDb.Execute rather than RunSQL:

CurrentDb.Execute strSql, dbFailOnError
Me.ReQuery

No need for SetWarnings.

 

[Guest]

Not sure where I'm missing the Missing Operator
Dim strGetTime as Variant

strGetDateTime = Me.txtDateTime2.Value
strTimeStampSQL = "UPDATE TendSaleMoveToReceipt SET DATE_TIME = " &
strGetDateTime & " " _
& " Where DATE_TIME <> " & strGetDateTime & ";"

 

[tina]

you should put your ampersand before the

line continuation

that has no effect on the concatenation of the strings; it's a personal
developer style choice. personally, i always put my ampersands at the
beginning of the next line of string, not at the end before the continuation
line.

hth

 

[tina]

is txtDateTime2 in a date/time format? if so, i'd put it in a Date variable,
not Variant - why make Access guess the data type? and you'll need to
surround the update value with # signs, as

Dim dtGetDateTime as Date

dtGetDateTime = Me!txtDateTime2
strTimeStampSQL = "UPDATE TendSaleMoveToReceipt " _
& "SET DATE_TIME = #" & dtGetDateTime _
& "# Where DATE_TIME <> #" & dtGetDateTime & "#"

hth

 

[Guest]

Ty Tina
I new that [#] don't know why I over looked it Just forgot I guess

Mike

 

[tina]

you're welcome

Ty Tina
I new that [#] don't know why I over looked it Just forgot I guess

Mike

is txtDateTime2 in a date/time format? if so, i'd put it in a Date variable,
not Variant - why make Access guess the data type? and you'll need to
surround the update value with # signs, as

Dim dtGetDateTime as Date

dtGetDateTime = Me!txtDateTime2
strTimeStampSQL = "UPDATE TendSaleMoveToReceipt " _
& "SET DATE_TIME = #" & dtGetDateTime _
& "# Where DATE_TIME <> #" & dtGetDateTime & "#"

hth