Exporting Data to Spreadsheet

[Lim]

Good day,

I have created an Access form named "Form1".
It consists of a subform named "Subform1".
The way I displayed the subform is in a "Table" format, which means it has
numerous rows and columns.
These two forms are linked by a variable/attribute, which I named it as
"Quantity".

My question is how should I display the data in the Subform1 which looks
like a Table into a spreadsheet file accordingly, row by row.

I appreciate any comment and help very much.

 

[LarryP]

The data in your subform comes from a Rowsource which is probably either a
table or a query. If I understand correctly that you want to transfer that
same data over to an Excel file, then the VBA DoCmd.TransferSpreadsheet
action will do it -- just specify that same table or query as the "TableName"
where TransferSpreadsheet is to get the data. If your point, though, is to
transfer ONLY the part of the data that is relevant to what's currently
displayed in Form1, you'll first need to modify the query to include a filter
(criterion) saying "only show me the records where Quantity = the Quantity
currently shown in Form1's Quantity field." (In the criteria row of the
query design view, under Quantity, enter Forms!Form1!Quantity .) Once
you've done that, TransferSpreadsheet for that query will send only the
matching records to Excel.

 

[Lim]

Thanks for your reply, Larry.

I have tried your latter suggestion and successfully built up a query.
However, I am facing problem to define the parameters for
DoCmd.TransferSpreadsheet. I referred quite a few threads regarding this but
I still can't figure out properly.

Below are my coding:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PartRecord"

For info, "PartRecord" is my query name. The rest like filename, sheetname,
range, etc, I don't really know how to put in correct syntax.

I am looking for help. Thanks in advance.

 

[LarryP]

When you're working in the VBA editor it will prompt you in most instances
for what it needs from you. If in your code you simply type a comma after
"PartRecord" you'll see the prompt string for the other things it needs, some
of which are required, some of which are optional. If you need help on the
individual items it is asking for, put your cursor anywhere inside the word
TransferSpreadsheet and hit F1, and you'll get an entire page of help on
TransferSpreadsheet, including a discussion of each of the parameters.