Capture Form Data to Export to Excel

[ajones92]

Hello,

I have created 2 forms. The first form asks the user to either enter or select a search string. Based on the string selected, form 2 (which uses 3 tab controls) is populated. I want to take the values returned in the form and export the results to Excel. There are approximately 10 tables and about 15 fields. Given the amount of data, I would prefer to only display fields that contain data.

I've tried so many things and none have worked so I'm back to the beginning. The main problem seems to be how to note / capture the data from a form that contains filtered data. I've tried mostly to use forms!frm_Results!List4, which is what I used as WHERE criteria in each RowSource where the resulting values are returned.

Any help greatly appreciated!

[June7]

Check this thread http://forums.aspfree.com/microsoft-...el-414974.html

[ajones92]

Thank you. I took a look at the link and the issue does seem to be very similar to mine . I also need to use VBA to make the export as a user will be performing this task via a command button and not me. Mine are also unbound text boxes with each text box having a different query associated with it, all dependent on collecting the user input. However I collect user input once and populate all applicable text boxes from the one user input entry.

I have successfully exported data based on a query. However, exporting the results in the form has me stumped. The other 2 links in the other post seem to use a "regular" query like what I've gotten to work. However, I don't see how I can apply this logic to a form ... without knowing the nomenclature to use. I think that's where I'm getting hung up. Or maybe I don't understand and I'm missing something?

[ajones92]

I should have asked ...

I'm familiar with setting a recordset this way: Set rst = dbs.OpenRecordset ("qry_data") for a named query. However I don't have a named query, only select statements as the record source for the text boxes on my form.

Is there a way to set the recordset equal to what's currently displayed in the active form which contains the button that will be clicked? I know this doesn't work, but something like (forms![frm_Results]![List4]) where frm_Results is the 1st form and List4 contains the selected value that all form results are based on in the 2nd form.

Hope this made sense.

[June7]

Is the form in Single record view? Do you want to export just one record (the one that is displayed on the form)?

What do you mean each textbox has a different query associated with it? The VBA code saves the textbox values by SQL to various tables or are you pulling up data from tables based on user input? The form's RecordSource cannot be a join of these tables? Or maybe form/subform arrangement is needed. Either way would allow direct view/entry/edit of data in tables. What is the user input and what data are you populating into the textboxes?

Form's RecordSource is a property of form and its value (the table/query reference or SQL string) can be captured in VBA, as well as the Filter and OrderBy properties. And of course code can refer to any control on a form.

[ajones92]

A single record is displayed, but I have arrows that can be clicked up or down to scroll through all resulting data, but one entry at a time.

Each text box is pulling data from a different table with different attributes. Let's say the user input is "blue" on Form 1. When the user clicks "Enter" the results are shown on Form 2. One text box pulls data from a table and would have the following results. The headings are above the line and the results below.

Name Colors, Country
Old Glory Red, White, Blue USA

2nd text box result - based on the same user input of "blue"
Name Type
Blueberry Fruit

3rd text box result - based on the same user input of "blue"
Water_Color Location
Blue Jamaica

I know the context of this example is odd, but it illustrates the set up of the form.

[June7]

Not following this very well. Can you make the project available by attachment (zipped if necessary) or upload to fileshare site such as box.net and post link? Either way, Compact & Repair first.

[ajones92]

I understand. It's a work database so I can't post it. Let me see what it would take to strip it down to better illustrate what's going on.

Thank you.

[ketbdnetbp]

If all the data you want to export to Excel is in your Form2, another way to accomplish your goal would be to capture the Form2 data in an array, i.e. loop through the records of Form2 populating the array, and, then, open Excel and push the data into the worksheet from the array.

Just a thought...

All the best,

Jim