Results 1 to 12 of 12
  1. #1
    WAVP375 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171

    Sql to update unbound table

    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.


    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]
    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
    End



    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run an update query:
    docmd.openquery "quUpdMyTable"

    The criteria in the query would look at the ID"
    UPDATE [TblTenant] SET Balance=0 WHERE [TenantId]=forms!myForm!txtID


  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Always output constructed sql to the immediate window: Debug.Print sql
    and check it. I see a problem with your concatenation that may or may not be the problem and it has been eliminated in the example given. If if looks good in the immediate window, copy/paste into a new query in sql view and switch to datasheet view. If there's a problem, the offending part is usually highlighted. That is basic troubleshooting for vba constructed sql. Sql is a reserved word. A better variable name would be strSql. If you 'type cast' your variables that way you will almost never run into this issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    WAVP375 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    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.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This is highlighted where - in a query sql view?
    In your first example you show no quotes thus your concatenation is wrong. In the second, your concatenation is wrong. If 0 is going into a number field, then no quotes needed around just that so the first is closer. If it is text, then it needs to be within its own quotes.
    If a number, try
    "UPDATE [TblTenant] SET Balance = 0 WHERE [TenantId] = " & forms!FrmFinance!TenantId

    If 0 is text then try
    "UPDATE [TblTenant] SET Balance = '0' WHERE [TenantId] = " & forms!FrmFinance!TenantId

    Your biggest mistake is passing the form reference in a literal fashion. If you output this to the immediate window, you should have been looking at something like
    UPDATE [TblTenant] SET Balance= 0 WHERE [TenantId]= 52
    not
    UPDATE [TblTenant] SET Balance = 0 WHERE [TenantId]= forms!FrmFinance!TenantId
    because there is no field that contains that value, is there.

    speaking of which - if the form reference retrieves a number, again no quotes. If the ID is text, then we still don't have it right.

    Last but not least, if you still get the same error and the sql is correct I do believe that the same error can be raised if a field in your query contains apostrophes such as O'Brien. Fix the syntax and look at table fields that are in this sql statement to see if there are any such values.
    Last edited by Micron; 11-24-2020 at 10:55 AM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From Post #1

    Quote Originally Posted by WAVP375 View Post
    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
    Quote Originally Posted by WAVP375 View Post
    ....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
    Quote Originally Posted by WAVP375 View Post
    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...

  7. #7
    WAVP375 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    Thanks guys, I changed as you suggested with mixed results.
    This code works fine
    strSQL1 = "UPDATE [TblTenant] SET Balance = 0 WHERE [TenantId] = " & Forms!FrmFinance!TenantId

    This code fails
    strSQL2 = "UPDATE [TblTenant] SET Balance = NewBalance WHERE [TenantId] =" & Forms!FrmFinance!TenantId

    I get error saying Too few parameters. i looked up the error it suggested that the fiels were not same type, misspelled or missing. Field are same type, currency and there and spelled properly. NewBalance is a calculated field wirh the calculation in the control source.

    Thanks

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    added info

    Research concatenation as it pertains to vba because you're not getting it and repeatedly fixing it for you doesn't seem to be teaching very effectively.

    Variables must be outside of the quoted sql. Variables for string values must be delimited with some form of quotation (there are 2 or 3 ways, depending on your preference). Variables for dates and time must be delimited with #'s
    Look at your 2nd statement - you are trying to update Balance field to the word NewBalance.

    "UPDATE [TblTenant] SET Balance = " & NewBalance & " WHERE [TenantId] =" & Forms!FrmFinance!TenantId
    Note that I have a space before WHERE.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    WAVP375 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    It's tough to teach old dogs new tricks! The &'s around NewBalance to me indicate a string value, where the textbox NewBalance is currency and TblTenant.Balance field is also currency.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not in vba; that's why you need to study it. They are concatenation operators. If the value is text then (one method)
    Balance = '" & NewBalance & "' WHERE

    if it was a date
    Balance = #" & NewBalance & "# WHERE
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    WAVP375 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    Thanks again. I have done a little research and think I understand most of it.
    I am now getting the results I need..

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It's tough to teach old dogs new tricks!
    I am now getting the results I need..
    Perseverence and Tenacity-- good stuff!
    Congrats.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-22-2019, 02:26 PM
  2. Replies: 19
    Last Post: 04-27-2015, 06:57 AM
  3. Replies: 1
    Last Post: 04-10-2013, 12:47 PM
  4. Immediate Update of Unbound Field
    By FormerJarHead in forum Forms
    Replies: 8
    Last Post: 10-08-2012, 04:39 PM
  5. Unbound checkbox's that update
    By Madmax in forum Forms
    Replies: 5
    Last Post: 06-20-2011, 02:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums