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
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?
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;
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.