Export specific fields in form to Excel form

[lpopa]

I have the form (PROJECT DETAILS) that displays a specific record's information, one record at a time.

I would like to click a button to export 5 specific fields (name, phone, address) of that form ("Project Details"), based on the PK "AssignNr", and then insert those fields in an already created Excel form that has those fields blank, but other fields filled in.

And then, as part of the same macro, I want to have the Print pop-up come up in the Excel form to select where/how that file will get printed.

Is this possible? How would I go about doing this?

I've tried exporting from the Project Details form, but it exports all the records. And then I am not sure how / where to code the vlookup formula (if that's even necessary) to take the data from the exported file, into the existing Excel form that needs to be filled in.

Can this even be done in one macro, or do I need to create two - one to export data from Access to Excel. And then another in Excel to vlookup the details from the exported file, into the existing Excel file.

It would be nice if I didn't have to put in what the vlookup criteria is. (i.e. AssignNr (which is the PK)) - if it could just be taken from the PROJECT DETAILS form's current record selection.

Could somebody at least point me in the direction I need to head?

 

[EternalMyrtle]

I would probably make a query with the 5 fields you need to export. Then I would set a filter on the query with a control on the form (probably the PK). So, in the query you would select the field you are filtering and put =Forms!YourFormName!YourControlName into the criteria box.

Then you would have to make a button do the export and write the code to insert the record into the Excel file.

This would be the general process I would use. The specifics would have to be worked out. I took a look at your database and it is not clear which form it is (I would have to open all of them and I am not going to do that)

I am sure there are other ways to do this, too, but this is the first method that I thought of.

 

[EternalMyrtle]

I should have been clearer: in your button's code, you would output the filtered query (i.e. the current record in the form) to your excel file.

 

[lpopa]

Thank you so much for taking the time to look it over and reply!

Sorry it wasn't clear from my wall of text: I am talking specifically about the PROJECT DETAILS form - the form that automatically opens when you load the database.

I can't figure out how to query a form (only a table or query). Any pointers?
The PK is the AssignNr field.

Also, any tips on how I can create the code in Access to insert the query results (aka record selected data) into the Excel file?

Thanks again!

 

[EternalMyrtle]

Honestly, I am running 2007 and got a message that certain features were not compatible. The startup form did not display and I am getting an error when I try to open project details (cannot read the data in jobs)

To get back to the question at hand, you would not be querying a form, just the table the form is based on. You would just enter the filter criteria (so the form's control name) into your field in the query.

So if you are using the PK to filter, make a query based on your ProjectDetails table (or whatever it is called) and add your five fields (including the field from the form that you want to filter, so AssignNr) then go to the criteria of your AssignNr field in your query and write: =Forms!ProjectDetails!AssignNrControlName (whatever you have named the AssignNr control). Then you write the code to output to your excel file from the OnClick event in your button control.

I have never outputted to an existing file before and attempted to add data to it so I am not sure how that would work. That would be a separate question to ask on here. It is best to try to separate the process into discrete steps and ask focused questions. Otherwise it is overwhelming for all involved. So, hopefully I can help you with the first part of your problem.