Update subform from Main form

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

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
 
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
 
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.
 
Mike said:
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
Click to expand...

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.
 
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 & ";"
 
you should put your ampersand before the
line continuation
Click to expand...

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
 
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
 
you're welcome :)


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

Mike
tina said:
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
Click to expand...
Click to expand...
 
Back
Top