Export data from a form

[Access_Help]

Hi,

I have a filter on a form using a combobox.

I want to export the data displayed to an Excel Spreadsheet. I have used the Exportwithformatting Macro but it seems to export everything.

Thanks

 

[Isskint]

With the Exportwithformatting Macro, what data source are you using for the ObjectName?

 

[Access_Help]

From a query.

I have started looking at this VBA code, but it exports everything.

DoCmd.OutputTo acOutputQuery, "qry_mark_grid", acFormatXLS, "MarkGrid(Class).xls", True

 

[Isskint]

Using a query, good start.

Does qry_mark_grid use the same criteria (filtering) as the form OR is it the base query the form uses BEFORE filtering?

 

[Access_Help]

qry_mark_grid uses criteria (filtering) from a combobox from the previous form.

So I am selecting a values from a comobox on form 1. On the click event I am using VBA code:

DoCmd.OpenForm "Mark Grid", , , "[Form] = '" & Me![Combo28] & "' AND [Unit]= '" & Me![Unit] & "'"



Form 2 displays the related records using the filter from Form 1.

I want Form 2 to export the displayed records into Excel.

Thanks

 

[Isskint]

From what you have posted I think you have a query (qry_mark_grid) that is the record source for form 2 BUT the specific data displayed is determined by the DoCmd.OpenForm.

Can you just clarify please, the data displayed on form 2 is determined by;

(1) the WHERE condition of the code

DoCmd.OpenForm "Mark Grid", , , "[Form] = '" & Me![Combo28] & "' AND [Unit]= '" & Me![Unit] & "'"

OR

(2) criteria within qry_mark_grid referencing fields/controls on form1

 

[Access_Help]

The data displayed on form 2 is determined by;

(1) the WHERE condition of the code

 

[Isskint]

thank you for that.

You would need to change qry_mark_grid to include the criteria from form 1. So in the criteria section under the field [Form] type =Forms.Form1.Combo28 In the criteria section under the field [Unit] type =Forms.Form1.Unit this should give you an SQL for the query something like

SELECT ..listoffielfds FROM tablename WHERE (tablename.Form = Forms.Form1.Combo28 AND tablename.Unit = Forms.Form1.Unit)

You can then remove the conditions in the DoCmd.OpenForm expression. Now when you click Export, the query is selecting the same data as seen on screen.

 

[Access_Help]

Hi,

Thanks. I'm not very good with VBA, are you suggesting something like this:

DoCmd.OutputTo acOutputQuery, "SELECT FROM MarkGrid Full_Name, Form, CW Effort, HW Effort, KS3GRADE, Test Mark, Course_ID, Class_Teacher, Unit WHERE (Mark Grid.Form = Forms.Courses.Combo28 AND MarkGrid.Unit = Forms.Unit)", "MarkGrid(Class).xls", True

I tried this but it's giving me an error.

 

[Isskint]

Getting there.

You need to create the query and call that rather than writing the query at the time of outputting.

The SQL you wrote was close but not quite right.
It should be something like;

SELECT MarkGrid.Full_Name, MarkGrid.Form, MarkGrid.[CW Effort], MarkGrid.[HW Effort], MarkGrid.KS3GRADE, MarkGrid.[Test Mark], MarkGrid.Course_ID, MarkGrid.Class_Teacher, MarkGrid.Unit FROM MarkGrid WHERE ((MarkGrid.Form = Forms.Courses.Combo28) AND (MarkGrid.Unit = Forms.Courses.Unit))

The quickest way to set this up now is to Create a new query but dont add any tables. Select SQL view and paste this SQL. Save the query and you would use that name in the DoCmd.OutputTo. Also, as mentioned previously, you can use this query as the RecordSource of the form [Mark Grid]

 

[Access_Help]

Hi,

I tried that but it didn't work

I created the SQL query and set it as the record source for the form Mark Grid and when I ran it brought up the parameters boxes saying it couldn't locate some of the fields.


My programming knowledge is limited

Kinda stuck, is there another way around it? My forms work perfectly and don't want to mess the record source, just need a way of exporting the records displayed.

 

[Isskint]

Can you attach a copy of your DB (remove the data if it is sensitive)?

 

[Access_Help]

Hi, I have managed to find a way to export the data using an example
from this site, but would prefer to do it using the method I described above.


Thanks

 

[Isskint]

Access_Help, thanks for posting the DB

The SQL you need is

SELECT Students.Full_Name, Students.Form, MarkGrid.[CW Effort], MarkGrid.[HW Effort], MarkGrid.KS3GRADE, MarkGrid.[Test Mark], MarkGrid.Course_ID, Students.Class_Teacher, Courses.Unit
FROM Students INNER JOIN (Courses INNER JOIN MarkGrid ON (Courses.Course_ID = MarkGrid.Course_ID) AND (Courses.Course_ID = MarkGrid.Course_ID)) ON Students.Student_ID = MarkGrid.Student_ID
WHERE (((Students.Form)=[Forms].[Courses].[Combo28]) AND ((Courses.Unit)=[Forms].[Courses].[Unit]));

From your earlier posts i did not know there were 3 different tables at work. I have applied this and it works. However it only works because i have created a query and call the query from the OutputTo. As i mentioned previously, it needs to be a query. You cannot put the SQL in the OutputTo (as you have in the attacahed)

 

[Access_Help]

Many thanks for this.

I will give it a try now. How do I call the query from the OutputTo?

 

[Isskint]

DoCmd.OutputTo acOutputQuery,ExportQueryName,......

 

[Access_Help]

I have created the query in SQL

and then added the following VBA onto the on click of the command button:

DoCmd.OutputTo acOutputQuery, "qry_export_from_class_view", "MarkGrid(Class).xls", True

it's throwing up an error message?

 

[Access_Help]

DoCmd.OutputTo acOutputQuery,ExportQueryName,......

DoCmd.OutputTo acOutputQuery, qry_export_from_class_view, "MarkGrid(Class).xls", True

doesn't seem to work

Thanks for your help so far.

 

[Isskint]

What is the error message?

 

[Minty]

Try transfer to spreadsheet with the query as the output object source

DoCmd.TransferSpreadsheet, acExport, acSpreasheetTypeExcel19, "qry_export_from_class_view", "MarkGrid_Class.xls", True

The last part True overwrites any existing version.