From Post #1
Originally Posted by
WAVP375
Form that is bound to Query, from tables TblTenant and Tbl Unit.
Sub form is data entry and bound to a table, TblPayment. One text box on the subform is unbound, Balance whos Control Source is =[Parent]![Balance] . After all data has been entered the Balance field in the TblTenant will be updated, zeroed, and the subform data except Balance field will be saved to the TblPayment. I'm having problems with the sql to update the unbound table, TblTenant. My save code follows.
<snip>
code below
<snip>
I get and error that I dont understand saying Syntax error(Missing Operator in query expression '0WHERE[TenantId]='.
Looks like it should be an easy one but I have tried many versions to no avail.
Any thoughts?
Thanks
Your code:
Code:
Private Sub BtnSave_Click()
'Update the balance field in the TblTenant
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim sql As String
sql = "UPDATE [TblTenant] SET Balance=" & 0 & "WHERE [TenantId]=" & [TempId] '<<-- error is in this line
dbs.Execute sql, dbFailOnError
MsgBox "Balance updated"
'Update the TblPayment (All data in subform except Balance)
If Me.Dirty Then
Me.Dirty = False
End If
Set dbs = Nothing '<< need to destroy the reference/pointer that you created
End
You said
Originally Posted by
WAVP375
....I dont understand saying Syntax error(Missing Operator in query expression '0WHERE[TenantId]='.
The error message told you (in this case) where/what caused the error.
SQL has a WHERE clause, but is doesn't have a "0WHERE". You are missing a space between the zero and the word WHERE.
The (corrected) line should have been like
Code:
sql = "UPDATE [TblTenant] SET Balance= 0 WHERE [TenantId]=" & [TempId]
------------------------------------------------------------------------------------
Next:
from Post #4
Originally Posted by
WAVP375
Thanks guys. I have changed the sql to strsql = UPDATE [TblTenant] SET Balance=0 WHERE [TenantId]=forms!FrmFinance!TenantId and I get a highlighted TblTenant and an error of Expect end of statement.
Then I changed the sql to strsql = "UPDATE [TblTenant] SET Balance="0 "WHERE [TenantId]=" [forms]![FrmFinance]![TenantId] and I gt a highlighted 0 and the same error message.
There are a couple of things wrong with this statement.
Code:
strsql = "UPDATE [TblTenant] SET Balance="0 "WHERE [TenantId]=" [forms]![FrmFinance]![TenantId]
1) You are missing the ampersand between the 0 (zero) and the WHERE. Or you need to remove the quotes.
2) You are missing the ampersand between the "TenantId =" and the form reference to the control on the form.
The statement should look like
Code:
strsql = "UPDATE [TblTenant] SET Balance= 0 WHERE [TenantId]=" & [forms]![FrmFinance]![TenantId]
I'm with Micron, you should learn about Debug.Print. I have lots in my code. (and I comment them out after testing/debugging is complete)
------------------------------------------------------------------------------
I write my SQL a little differently.
Code:
strsql = "UPDATE [TblTenant]"
strSQL = strSQL & " SET Balance= 0"
strSQL = strSQL & " WHERE [TenantId]=" & [forms]![FrmFinance]![TenantId]
Debug.Print strSQL 'Prints the SQL to the immediate window
I place each clause on a separate line - easier to edit (for me)
Notice the spaces are before the keyword. It is easier to see if I missed a space.... not so easy if the space is at the end of the line.
Then I can copy the line in the immediate window and paste it into a query to test the statement.
Sorry this is so long. I wanted to try and help you learn how to debug SQL statements in code by explaining what I saw.
Good luck with your project...