Variable included in Exported Report Name?

[nka]

Hi all,

I am looking to export a query (into Excel) and include a field value as part of the filename.

Example of what I am trying to achieve:

DoCmd.TransferSpreadsheet acExport, 8, "TEMP_SupplierItems", "C:/Data Downloads/EXP_SupplierItems_" & [COLOR=Red][B]SuppName[/B][/COLOR] & ".xls", True, ""
        DoCmd.Close acForm, "frm_sel_supplierstock"

The SuppName needs to look at the Supplier_Name field in TEMP_SupplierItems.

I can't for the life of me get my head around what to do to get it there! I think I need to DIM something and perhaps run SQL somewhere - but I am struggling big time!

Can anyone help please?

 

[KenHigg]

How are you selecting which customer to export? From A combo box on a from?

 

[nka]

Hi Ken,

Yes (in a roundabout way).

A form has the combo box CBO_Supplier which looks at Supplier_ID and Supplier_Name. Supplier_ID is the bound field, but Supplier_Name is displayed.

However, both values are stored in the temporary table as there is a Make Table action once the Supplier has been selected.

This is where I am struggling! Perhaps I am just making it hard for myself...

 

[KenHigg]

Maybe something like (Plug in your form name and cbo box name):

DoCmd.TransferSpreadsheet acExport, 8, "TEMP_SupplierItems", "C:/Data Downloads/EXP_SupplierItems_" & forms!myFormName!mycomboBoxName & ".xls", True, ""

 

[nka]

Getting there!

That gives me the supplier_id (which is the bound column on the combo box and is hidden).

Is there anyway I can expand on that to select column 2 (being Supplier_Name)?

 

[KenHigg]

Hum...

forms!myFormName!mycomboBoxName.column(1)

 

[nka]

You beauty!!!

Works an absolute dream! I knew it could be done - just didn't think it was so simple!

Thanks for your help.

 

[KenHigg]

Cool!

Glad it's working!

 

[nka]

So while I have your attention....

If I had a situation where a value was entered in a query parameter to filter records and then export - how would I get that value into the filename?

The query is a Make Query (makes temporary table), so the value is stored in the table... back to my original question really!!!

 

[KenHigg]

Again, it depends on how you get the parameter into the query. Is it hard coded or does it come from a form?

 

[nka]

I guess it's hard coded...

When the query is run/ran (grammar!) - a parameter box comes up asking for the stock number.

 

[KenHigg]

I think the easier way would be to do a new small popup form that prompts yhe user for the value. Then simply ref the text box on the form in the query as the parameter and also in the file name as before...