Extracting data in a subform to a table

[Kichen]

Hello, I'm building a database to keep track of bought products and sales in our company. I have an invoice form in which I can calculate the average product cost when it comes in, and then I need to use that value to calculate total warehouse value and also the profit margins in the sales form. However, there are several factors that influence this average cost, for example shipment costs can vary depending on the purpose and the final customer of an article, etc, so TLDR: it would be way easier to snapshot the current BoxID and AvgCost values in my invoice subforms and save them to a table for later use.



I have seen an option in the Macro generator that lets you save the current state of a subform to Excel, but not to another Access object

I have also tried this

INSERT INTO AvgCostByID ( IDCaja, PVPUNITARIO )
SELECT [Formularios]![FACTURA_AEREO]![Subformulario FACTURA_AEREO_sub]![IDCaja] AS IDCaja, [Formularios]![FACTURA_AEREO]![Subformulario FACTURA_AEREO_sub]![AvgCost] AS PVPUNITARIO;

but it only retrieves the currently active record. I could use a macro to keep moving forward until I run out of records I guess, but I wonder if there's an easier way to grab everything in those 2 columns and create/annex/update a table with it.

Thanks in advance for any pointers!

[ranman256]

the query already exists in the sub form, use it in your append query.
insert into table (xxxxx) select * from qsQryInSubform

[Kichen]

Sorry if I have misunderstood, but that field I'm interested in isn't anywhere in the record source query of my subform. It's a calculated text box taking values from the main form, other subforms and other calculated values several times for its calculation. Maybe I looked in the wrong place?

[Kichen]

Okay, I feel like the slowest murderer with the most inefficient weapon for doing this, but the method I used is a macro consisting of:

- Export the current subform to a xlsx file.
- Import it back to a temp table.
- Execute two queries that transfer that value to the permanent table and clear temp, respectively.

Now I have to find out how to remove the overwrite confirmation message that pops up if you try to create an xlsx file that's already there, or fiddle with VBA to remove the file after the macro's done. I'll mark this as resolved I guess.