Access Queries and Drop Downs

[KristieLee1]

Hello. I am working on a 'report' in access that will be exported to excel. The end users do not have access, hence the excel medium. I'm trying to create as much as I can in access.

So, I have 30 columns of information. Each column will need a drop down box for the user to select an option. Each column will have different selections in the drop downs. Is there any way I can create the drop downs in Access, that will then export to excel?

I am creating a query off a table in access. I will need to create columns within the query that display the drop down choices (as the columns do not exist in the table being queried---I know I know).

Ideas? Any help is appreciated. Thank you!

 

[alvechurchdata]

The regular TransferSpreadsheet method just exports the data from the table into Excel. If you want to add dropdowns in the Excel cells then you're going to have to write some Excel VBA code and you might get a better answer in a specialist Excel forum.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[KristieLee1]

thats what I figured. I was hoping to be able to create them in access, but I'm guessing it'll have to be done in excel. Thank you!

 

[ICCIIT]

You could always create your spreadsheet along with an ODBC connection from Excel back to the Access Query, this would then allow you to have all the regular dropdowns etc in your workbook and then refresh the data as required from within Excel.

I do this with one data query in to a workbook with a dozen pivot tables and it really saves a lot of time!

 

[boblarson]

I was hoping to be able to create them in access, but I'm guessing it'll have to be done in excel.

KristieLee1:

You didn't catch what Geoff Franklin said. He said you would need some Excel VBA to do it but he never said you couldn't do it from Access. You can manipulate almost everything in an Excel Spreadsheet via code from Access but it does require you to use the Excel Common Object Model to do it.

But ICCIIT's suggestion seems to be a potentially good one for you to follow instead.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com