Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2008
    Posts
    7

    Unanswered: Update table with data from query

    I have a table that tracks statistics about trips run by a travel club. After a trip is complete, I would like to have an automated process to take the statistics (gathered in a query) and put them in the table. I related the query to the table, created a query showing the relevant fields, and tried some VBA code:

    DIM varStat1 as Double
    varStat1=qryStat1.value
    tblStat1.value=varStat1

    The assignment to varStat1 works fine, but trying to assign the variable value to the table field gives the Recordset Not Updatable error.

    Any time I create a query tying the table to the statistics, the query becomes an un-updatable recordset. I tried to create an update query using only the table, and using the expression builder to insert an Update To value from the query with the statistics. Access was unable to find the query value, presenting me with a dialogue box asking for the value when I ran the update query.

    This seems like a rather straightforward problem to me. Can anyone supply me with VBA code to update my table, or point me to a solution that would allow the user to save the statistics with a mouse click?

    Thanks
    Steve

  2. #2
    Join Date
    Oct 2008
    Posts
    7
    Here's the SQL for my query:
    SELECT Destinations.DestName, qryT10BalTotal.[Sum Of DDABal], qryT10BalTotal.[Sum Of SAVBal], qryT10BalTotal.[Sum Of CODBal], qryT10BalTotal.[Sum Of IRABal], qryT10BalTotal.[Sum Of NoteBal], Destinations.DDABal, Destinations.SAVBal, Destinations.CODBal, Destinations.IRABal, Destinations.NOTEBal
    FROM Destinations INNER JOIN qryT10BalTotal ON Destinations.TripID = qryT10BalTotal.FirstOfDestID;

  3. #3
    Join Date
    Oct 2008
    Posts
    7
    Queries based on summaries are not updatable. When I combined a query containing a summary with a table to produce another query, the result was updatable query. To do what I want, I need to first create a table with the summary information.

    Steve

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can also build a dynamic UPDATE query and use the Execute method of a database object (or CurrentDB), something like this:
    Code:
    Dim strSQL as String
    strSQL = "UPDATE <Table> SET <Table>.<Field> = " & varStat1 & "WHERE ...
    CurrentDB.Execute strSQL
    Have a nice day!

  5. #5
    Join Date
    Oct 2008
    Posts
    7
    Thanks! That looks a whole lot simpler than the work-around that I was thinking about with temporary tables. I'll give it a try.

    Steve

  6. #6
    Join Date
    Oct 2008
    Posts
    7
    Sinndho,

    That was exactly the solution I was looking for. It works great. Thanks for your help.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •