export filter data in selected fields to excel

[Accesspaz]

First off I apoligise if this is a clear answer. I have looked on the internet for the last two days and can't seem to find this anywhere, either that or I am just entereing the search parameters in wrong

I have filtered selection in a form which I want to export to excel.

Simple enough : Created a macro with the export to command. This dus everything I want to do.

Well not quite.

How do I select the fields I want to export.Something like Select Id, name, adres from query soandso

Hopefully there is a simple solution to this. If there any existing posts. Could you post the link for me.

Thank your for your time,
Kind regards,

 

[Jibbadiah]

http://www.access-programmers.co.uk/forums/showthread.php?p=387231#post387231

 

[KenHigg]

The way I do this is to always try to export a query - In other words build a query that is used exclusivley for the export. So then you build the query, grabbing parameters from a form or where ever, and when you get the query to return the results you need, save it and do the macro to export the query...

Hope this makes sense, it's really simple

 

[Accesspaz]

Hej Jibbadiah, thanx for the reply

The only problem I run into when I run a query export is that access loses the applied filter.

I thought of running some kind of update query in the macro (yes my vb is atrocious)

But whenever I run anything of a query through the system it loses the filter.
The selection is based from different search menu's in the form which select the records. So the filter names are different, which would mean, in my current deduction that I would need 4 or 5 export buttons

 

[Accesspaz]

So if I could somehow pass the filter to the query it would work then.

O.k. how do I do that generically.

So something like : just pass on filter to that query.

Process then being on.click button by user

macro or vb

gotoquery
apply current filter to query
output to

Sounds simple enough...

 

[KenHigg]

How are applying the filter? (Where is it getting the value to use as the criteria?)

 

[KenHigg]

How are you applying the filter? (Where is it getting the value to use as the criteria?)

 

[Accesspaz]

Private Sub surname_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[name] = '" & Me![surname] & "'"
Me.Bookmark = rs.Bookmark


' Filter the recordset

Me.Filter = "[name] = '" & Me![surname] & "'"
Me.FilterOn = True
End Sub

Which there are then four different ones for each selection menu.

 

[KenHigg]

So when you have the form open there is a value in Me![surname].(?)

Can't you do a query that contains all the fields you need an uses Me![surname] as a parameter (or criteria)? Then export these results?

 

[Accesspaz]

Yeah the form allows the user to select the value in this case surname,
then by filtering it it only shows those records with the same surname.

It works great!

I think adding an updat query command to the vb I just opsted would do the trick.

Something like:

Pass/apply Me.Filter = "[name] = '" & Me![surname] & "'" to Exportquery

then you could run the export button from that query.

How do I build that though

 

[Accesspaz]

So far I have something like this added in the sub after apply filter:

DoCmd.OpenQuery (Query_name, [View As AcView = acViewNormal], [DataMode As AcOpenDataMode = acEdit])

DoCmd.ApplyFilter ([name])

However this isn't valid vb syntax (even though it is directly from the vb window )

 

[KenHigg]

Sorry to be short sighted on this - But one of us appears to be missing something...

Let's start from the beginning...

1. You have a form with Me![surname] on it.

2. You have pre-built a simple select query that uses Me![surname] as a criteria.

3. The user clicks an export button that exports the query to a .xls file.

4. The user never actually sees the query or its results, it kind of runs and exports in the background...

Am I missing something here?

 

[Accesspaz]

KenHigg, Thanx for your time

From the top (because I agree, this shouldn't be this difficult)

1. You have a form with Me![surname] on it.

Correct

2. You have pre-built a simple select query that uses Me![surname] as a criteria.

Correct: This is where the form gets it data from

3. The user clicks an export button that exports the query to a .xls file.

The user clicks an export button which exports the form to a .xls file.
Problem with this it adds the "surname"and other search fields to the .xls file.
So solution seemed to select which fields to export to .xls.

4. The user never actually sees the query or its results, it kind of runs and exports in the background...

n.a. unless we use a query to export the data.

 

[KenHigg]

KenHigg, Thanx for your time

From the top (because I agree, this shouldn't be this difficult)

1. You have a form with Me![surname] on it.

Correct

2. You have pre-built a simple select query that uses Me![surname] as a criteria.

Correct: This is where the form gets it data from
3. The user clicks an export button that exports the query to a .xls file.

The user clicks an export button which exports the form to a .xls file.
Problem with this it adds the "surname"and other search fields to the .xls file.
So solution seemed to select which fields to export to .xls.

4. The user never actually sees the query or its results, it kind of runs and exports in the background...

n.a. unless we use a query to export the data.

This is not the same query as where the form gets it's data. It should be a seperate query. Used only to export the desired data!

???

 

[Jibbadiah]

Sorry... was away for lunch

Input a macro button that calls a query.
Put this in the query as the criteria based on your form.

Forms![Form_Name]![Field_Name]

This will mean whatever is selected in your form will be used as the basis for the query.

YOu can do the same in VB... just make that part of your criteria... i.e.

Variable = Forms![Form_Name]![Field_Name]

Then use the variable in your select statement...

Dim strSQL

Variable = Forms![Form_Name]![Field_Name]

strSQL = "Select from <Table_Name> where <Field> = '" & Variable & "')"

DoCmd.RunSQL (strSQL)

Does this help?

 

[Accesspaz]

This is not the same query as where the form gets it's data. It should be a seperate query. Used only to export the desired data!

???

The form is running on one main query.

 

[KenHigg]

At this point, it don't matter where the form gets it's data...

(This is going to click in a minute )

Edit: I need to bug out for a while - don't think I'm ignoring anyone

 

[Accesspaz]

Input a macro button that calls a query.
Put this in the query as the criteria based on your form.

Forms![Form_Name]![Field_Name]

This will mean whatever is selected in your form will be used as the basis for the query.

YOu can do the same in VB... just make that part of your criteria... i.e.

Variable = Forms![Form_Name]![Field_Name]

Then use the variable in your select statement...

Dim strSQL

Variable = Forms![Form_Name]![Field_Name]

strSQL = "Select from <Table_Name> where <Field> = '" & Variable & "')"

DoCmd.RunSQL (strSQL)

Does this help?

In plain English

 

[Jibbadiah]

Simply

Copy the query that you use for the basis of the form.
In the surname field put the following criteria...

Forms![<Enter the name of your form here>]![Surname]

This should produce the same results as what you see on your form... am I correct?

J

 

[Accesspaz]

Thanks Jibbadiah

Forms![<Enter the name of your form here>]![Surname]

This should produce the same results as what you see on your form... am I correct?

Correct. Although I have to edit the value manually when I open the query, and it doesn't (yet) update automatically from the form.
So then all I have to do is write a code in the selection menu which
automatically updates the [surname] value in the query, and replaces the [surname] criteria with [city] [state] etc.

Is that possible?