none
only one record in table is updated from a form with multiple records RRS feed

  • Question

  • I can’t figure out how to update multiple fields in a table from a form which bound to a query which can have one or more records.  The following VBA code updates the correct field in the first record  in the table but not the additional records in the form.

        If (Expr3 > 0) Then

    CurrentDb.Execute "UPDATE tblTransactions SET IntBilledThru = Now()" & "WHERE ID = " & Forms.frmAccountsReceivableStatement.ID, dbFailOnError

         End If

    Initially the user has to select the ProjectID from a combo box. The following filter is applied at that point: AccountsReceivableStatementInterest Query]![ProjectID]=[Forms]![frmAccountsReceivableStatement]![Combo25]

    AccountsReceivableStatementInterest Query is the query to which the form, frmAccountsReceivableStatement is bound.  The table is tblTransactions.

    I hope someone can help me out since I can seem to find the answer from the various sites(I am probably asking the wrong questions.  Bob


    • Edited by BobAubry Friday, September 13, 2019 1:28 AM
    Friday, September 13, 2019 1:27 AM

Answers

  • Hi Ken,

    I think he wants a "UPDATE ......SELECT .......

    Select a list to update into "tblTransactions"

    Just a guess.

    • Marked as answer by BobAubry Friday, September 20, 2019 9:32 PM
    Monday, September 16, 2019 4:51 AM

All replies

  • Just take it simple

    In table tblTransactions  how many ProjectIDs you have for  a ProjectID you have selected...

    Friday, September 13, 2019 5:36 AM
  • CurrentDb.Execute "UPDATE tblTransactions SET IntBilledThru = Now()" & "WHERE ID = " & Forms.frmAccountsReceivableStatement.ID,

    hi Bob,

    Would it help if you put Now() outside the quoted string, and add a space before the WHERE part, as in:

        CurrentDb.Execute "UPDATE tblTransactions SET IntBilledThru = " & Now() & " WHERE ID = " & Forms.frmAccountsReceivableStatement.ID

    Imb.

    Friday, September 13, 2019 5:49 AM
  • The following VBA code updates the correct field in the first record  in the table but not the additional records in the form.


    If ID is the primary key of the tblTransactions table, as its name suggests, then the UPDATE statement will inevitably update only one row.  If you wish to update multiple rows then the query needs to be restricted not on the primary key, but on some other column(s) whose value(s) will determine the rows to be updated and only those rows.

    Ken Sheridan, Stafford, England

    Friday, September 13, 2019 11:18 AM
  • John, ProjectID can be used numerous times in the tblTransaction table.
    Friday, September 13, 2019 2:16 PM
  • I could not get your UPDATE statement to run.  I think I have spacing issues. The following is what I tired and I put % where I have a space.

      CurrentDb.Execute%"UPDATE%tblTransactions%SET%IntBilledThru%= %" %& %Now() %&%" %WHERE%ID = %"%&Forms.frmAccountsReceivableStatement.ID

    I get the following error message:

    Syntax error(missing operator) in query expression ‘& Now()

    Thanks, Bob

    Friday, September 13, 2019 2:20 PM
  • Ken, I changed UPDATE statement to from the Primary Key,ID, to InvoiceNumber and it still only updates one field in tblTranactions.  It is now set to:

    If (Expr3 > 0) Then
        CurrentDb.Execute "UPDATE tblTransactions SET IntBilledThru = Now()" & "WHERE ID = " & Forms.frmAccountsReceivableStatement.ID, dbFailOnError
           
        End If

    This application is  an Accounts Receivable statement generator and InvoiceNumber occurs in both charges and payments transactions.

    Thanks, Bob

    Friday, September 13, 2019 2:30 PM
  • Imb - No need to bring Now() out of the SQL statement. The Access engine understands that intrinsic function.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, September 13, 2019 3:12 PM
  • Ken, I changed UPDATE statement to from the Primary Key,ID, to InvoiceNumber and it still only updates one field in tblTranactions.  It is now set to:

    If (Expr3 > 0) Then
        CurrentDb.Execute "UPDATE tblTransactions SET IntBilledThru = Now()" & "WHERE ID = " & Forms.frmAccountsReceivableStatement.ID, dbFailOnError
           
        End If

    The statement is still restricted on the ID primary key, so it can still only update one row.  Are you saying that a subset of rows comprising all rows with a specific invoice number should be updated?  If so the restriction should be on the invoice number column, and it should be an invoice number control in the form which is referenced.


    Ken Sheridan, Stafford, England

    Friday, September 13, 2019 3:40 PM
  • I could not get your UPDATE statement to run.  I think I have spacing issues. The following is what I tired and I put % where I have a space.

    CurrentDb.Execute%"UPDATE%tblTransactions%SET%IntBilledThru%= %" %& %Now() %&%" %WHERE%ID = %"%&Forms.frmAccountsReceivableStatement.ID

    I get the following error message:

    Syntax error(missing operator) in query expression ‘& Now()

    Thanks, Bob

    As corrected, the line should be:

    CurrentDb.Execute "UPDATE tblTransactions SET IntBilledThru =  Now() WHERE ID =  " & Forms.frmAccountsReceivableStatement.ID

    But, as I said in my other reply, this can only update one row.


    Ken Sheridan, Stafford, England

    Friday, September 13, 2019 3:50 PM
  • No need to bring Now() out of the SQL statement. The Access engine understands that intrinsic function.

    Hi Bill,

    Thank you for this clarification. It is a consequence of my off-Access work.

    Personally, I make no difference between intrinsic functions, home-made functions or variables, and I always use them outside the quoted strings, At least that makes my programming easier!

    Enjoy, Imb.

    Friday, September 13, 2019 4:35 PM
  • Ken,  messed up my reply to you.  You had stated "If ID is the primary key of the tblTransactions table, as its name suggests, then the UPDATE statement will inevitably update only one row.  If you wish to update multiple rows then the query needs to be restricted not on the primary key, but on some other column(s) whose value(s) will determine the rows to be updated and only those rows."

    I should have replied, "

    Ken, I changed UPDATE statement to from the Primary Key,ID, to InvoiceNumber and it still only updates one field in tblTranactions.  It is now set to:

    If (Expr3 > 0) Then
        CurrentDb.Execute "UPDATE tblTransactions SET IntBilledThru = Now()" & "WHERE InvoiceNumber = " & Forms.frmAccountsReceivableStatement.InvoiceNumber, dbFailOnError
           
        End If

    Friday, September 13, 2019 9:29 PM
  • You may need to use the Open Recordset methodology to edit multiple fields in multiple records at one time. You can do that while the input form is open but you will want to turn off the echo while it is working.
    Friday, September 13, 2019 9:57 PM
  • You say 'it still only updates one field in tblTranactions'.  Previously you've said it updates only one record.  Which is it?

    If the former then you need to include further columns in the SET clause.

    If the latter it means that the subset of rows to be updated is not determined by the invoice number, so you'll need to find some other criterion to do so.  That's not something I can do for you on the basis of the information you've provided.

    Ken Sheridan, Stafford, England

    Friday, September 13, 2019 10:16 PM
  • Hi Ken,

    I think he wants a "UPDATE ......SELECT .......

    Select a list to update into "tblTransactions"

    Just a guess.

    • Marked as answer by BobAubry Friday, September 20, 2019 9:32 PM
    Monday, September 16, 2019 4:51 AM
  • It finally works.  Thanks to all of you who contributed to the solution.  The solution used the RecordSetClone property.  Bob
    Friday, September 20, 2019 9:32 PM