View Spreadsheet Via Form

[odun]

Hello all,

Thanks for your kind responses.

I have another question and hope you can help with.

On my form which gets informaiton from a query, I have among other fields an Account field. I have a query, Details, that provides details on the account field, the fields of the query include Account, invoices #s, invoice amounts e.t.c.

I would like to create a button on the form that would
1) take the account # showing on the current form and run the details query based on that account #
2) display the results of the query in a spreadsheet.

Thanks again for your kind help.

 

[ansentry]

Your wrote:

display the results of the query in a spreadsheet.

Do you mean:

display the results using an Access Query

 

[odun]

What I mean is, when I click the button, it runs the Access Query, but then dumps the result in an Excel Spreadsheet.

 

[ansentry]

Have a look at the attached sample, I have used post codes from an old db of mine but you can change to what ever you like.

 

[odun]

Thanks John.

I check the db you attached but it's not quite what I need.
The query button on the form in your db ran fine, but the export to excel was giving a debug error or soemthing like that.

On my form, there will be an account #. I don't want to have to create another form with a dropdown menu. I think access should be able to take the account # on the present form. Then run and dump the query results (all in one step) in excel.
All ideas welcome.

Thanks again

 

[alastair69]

If you can post an example i will have a look for you.

Alastair

 

[ansentry]

Your write:

the export to excel was giving a debug error or something like that.

What does the error say?
Have you set your reference to Microsoft Excel 11.00 Object Library?

You Continue:

I check the db you attached but it's not quite what I need.

Well if you don't want the combo box that’s fine it was only a sample, so that you can adapt it to suit what your requirements

Have you tried to help yourself by looking at how mine works?

Have you had a look at the query to see how the data if filtered?

Just change the criteria in the query to suit you text box name on your form. Then add a command button to make it run.

 

[odun]

Thanks again John,

I don't know a whole lot in access but I will look at the query and the code to see if I can change the names e.t.c.

The error I am getting with the export to excel is:
Microsoft Access can't save the output data to the file you've selected, any idea how to resolve this?

I will let you know as I progress with the other.

Thanks much again.

 

[ansentry]

You ask:

The error I am getting with the export to excel is:
Microsoft Access can't save the output data to the file you've selected, any idea how to resolve this?

Yes, Have a look at the code behind the button "Export to Excel"

You will see this:

' Change the name and location of your excel file, you don't have to call it by the access query name.
DoCmd.OutputTo acQuery, "qryFilterPostCodes", "MicrosoftExcel(*.xls)", "c:\Temp\PostCodes.xls", True, ""

This error will occur if you don't have a folder by the same name ( e.g c:\Temp)

Try and get that working so that you know that mine works on your system. Then we can move the the next problem ( if you don't solve them yourself)

It is 3:17 am here in Melbourne and I am going to bed......zzzzzz

 

[alastair69]

Odun,
You need to create a folder called C:\temp, this should get it working for you.
I have look at the database and it looks like it will do want you need.

Alastair

 

[odun]

Hello,

Thanks John and Alastair, the view code is working well now. Very grateful.

One more question, if I wanted to have access put the file on the user's desktop, how would I change this line:

..................."c:\Temp\PostCodes.xls", True, ""

I don't want a situation where the user would have to first of all create a temp folder to make this work..I want to ensure it is as stressfree as possible.

Thanks soooooo much!

 

[alastair69]

Double check that your text box are called the right name, as i think you are getting this error as one of your text boxes is not been found.

 

[odun]

Yes, I found the error, there was a space in my textbox name...

One more question, if I wanted to have access put the file on the user's desktop, how would I change this line:

..................."c:\Temp\PostCodes.xls", True, ""

 

[alastair69]

Ok,

you would need to replace the following line:
DoCmd.OutputTo acQuery, "qryFilterPostCodes", "MicrosoftExcel(*.xls)", "c:\Temp\PostCodes.xls", True, ""

With the following:
DoCmd.OutputTo acQuery, "qryFilterPostCodes", "MicrosoftExcel(*.xls)", "C:\Documents and Settings\All Users\Temp\PostCodes.xls", True, ""

I hope this will work but get back if not and i will have another look.

Alastair

 

[odun]

Thanks Alastair,

I am getting the same error:
Microsoft Access can't save the output data to the file you've selected, any idea how to resolve this?

The code you provided, does this save the xl file on the user's desktop? I don't want to use a temp file, is this possible, or do all the users of the db have a Temp file in their C in other to run this code?

 

[odun]

Hi Alastair,

It worked!!! I decided to use this:
DoCmd.OutputTo acQuery, "qryFilterPostCodes", "MicrosoftExcel(*.xls)", "c:\ PostCodes.xls", True, ""

Thanks so much for all your help, thanks for being patient!!!

 

[alastair69]

No problems

Alastair

 

[ansentry]

I have just seen that you are have a problem with the excel workbook.

I have just tested this and it works for me (Putting on All Users Desktop)

DoCmd.OutputTo acQuery, "qryFilterPostCodes", "MicrosoftExcel(*.xls)", "C:\Documents and Settings\All Users\Desktop\PostCodes.xls", True, ""

Alastair code re temp under all users would not have worked unless you had created a folder called temp under all users.

Hang on I will create a folder called temp under all users and try it.
It works fine;

DoCmd.OutputTo acQuery, "qryFilterPostCodes", "MicrosoftExcel(*.xls)", "C:\Documents and Settings\All Users\Temp\PostCodes.xls", True, ""

Hope this solves the problem.

 

[odun]

Thanks a lot John. This solves the problem!