Microsoft Access 308: Import, Export, Specification, TransferText, TransferSpreadsheet, Split Text

Student Interaction: Microsoft Access 308

Richard on 1/1/2007: External Data, Importing & Exporting Tables, from Excel, Text Files, Linking Tables, Spreadsheets, Splitting Names

Shirley on 4/24/2008: Export to a user-specified location: Access 308 is a great tutorial for automating the export of data to a txt or alternate file, however, it only provides the ability to export to a statically specified file. Is there a way to have Access export to a location that is specified on a field on another form. I have been playing around with this, both in vba and macros and can't get it to work. For example, if the form that also contains the button for executing the macro or vba code also had an unbound field that the user could specify the location and file name to export to, I would like to reference that field as the file location. I have tired just about every syntax I can think of and can't get it to work. This would allow the user to save the file in any location or file name. Also, by doing this, I could set it up so that the user could use the file browser to specify the location. Thanks for your advice and I love your tutorials.

Richard Rost on 4/24/2008: Shirley, EXCELLENT question.

Yes, it is possible to have the user specify the filename for the export. There are two EASY ways to do it. The first is to simply place a textbox on the form where your export button is, and have the filename in that.

Let's say that you create a textbox called ExportFilename on your SecondaryMenuF form right below your button. Now, in the FILENAME parameter for the macro that performs the export, just put:

=[Forms]![SecondaryMenuF]![ExportFilename]

We learned about this technique in Access 202, lesson 2, where we shared values between two forms. You can use this same notation in macros, queries, VBA code, etc.

The other way you could do it, would be to use the InputBox command to prompt the user for the filename when the macro runs. Here's the syntax:

=InputBox(Prompt,WindowTitle,DefaultValue)

So in this case, you would say:

=InputBox("Export Filename","Export","C:\ExportFile.xls")

And that would prompt the user for the filename, giving them a default vallue of C:\ExportFile.xls. We learned about InputBox in Access 306.

There is another way you could do it... but that would involve introducing the FILE and FOLDER controls to your database. I personally don't like pulling in extra controls into my database unless absolutely necessary, but Microsoft does make nice controls that you can browse for files and folders with. I cover them in my Visual Basic 106 class, and the same controls work in Access too.

You're still going to have to refer to the filename you select in your macro though - so you'll still need the Forms!FormName!Field notation.

ibad on 1/20/2009: i have form in this form i subform1 and subform2 if i click subform1 the related value in subform2 should be shown . it can be done with requrey but i need mdb file with example

Richard Rost on 1/21/2009: Ibad, you need to link the child and master properties correctly in the subform, and everything should work automatically. I cover this in Access 202.

Marina �Stein on 3/25/2009: Hi Richard,
I just ordered Access 3008 course. I really need workbook for this class. Please let me know why don't I see this book in your file.
Thank you, Marina

Richard Rost on 4/1/2009: Marina, I don't have handbooks available for ALL of my classes. It's based mostly on demand. If I don't sell a lot of handbooks for Access 307, I won't bother writing one for 308. If anyone else is interested in an Access 308 handbook, please post a note here and I'll consider it. Thanks.

Add Your Questions or Comments