Exporting to Excel via Navigation Form

[KasperBN]

There is nothing in the folder. And yes it's possible, but it seems like it is too big to upload (maximum is only 2 MB)? (pretty sure even a zipped version would be too big)

How do I create a dummy version?

 

[pr2-eugin]

If your DB has 20 tables but the question revolves around only 1 table delete all tables that are not required.. If you havw 100,000 records in your table delete 99,990 records.. Perform a Compact and Repair after deleting them and your file size should have come down..

 

[boblarson]

As this is in 2010, I would advise against using

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Products", "J:\Backup\Products.xls"

I would suggest using

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel14XML, "Products", "J:\Backup\Products.xlsx"

In regards to the button appearing in all tabs, I have selected the Products tab before creating the button (under design view). Yet it still shows up on the other tabs.

 

[KasperBN]

Here is a very, very dummy version of the database, let me know if it will do.

 

[pr2-eugin]

The problem was because you set the caption of the button as exportBtn, you should have its name as exportBtn.. If that is confusing.. Go into design view.. Click on the button.. In the events Tab of the property sheet look for the On Click event click on the three dots at the end.. (...) It will pop up with a small choice box, select Code Builder, it will take you to the VBA Editor.. Type the DoCmd code there.. It works..

 

[KasperBN]

I finally managed to get (part of it) working, thank you!

The export part now works, however I have a small question. Using the innate export button, there is the option of getting the document to open after the operation is complete automatically. Is it possible to code this in?

My second question is something I have still not managed to successfully implement. The button still shows up on every tab, even though I only create it under f.ex, Products. (I would like an export button on most tabs, but not all, hence the problem, also the placement of it).

My final question, is there a reason the export does not show up in the same format as in the database? By this I mean, shading, boxing, gridlines. I hope you understand what I mean here.

Thank you for all the help so far.

 

[KasperBN]

As to the second question, I would like to be able to have a button for each tab, so that I can separately export the forms, not all the forms at the same time. That is what I meant.

 

[pr2-eugin]

Navigation form, is nothing but a Form with many forms.. So Why not create a button in the appropriate Form? What I mean is, if for example your navigation form has four forms 'ProductFrm', 'SalesFrm', 'OrdersFrm', 'ReturnsFrm'.. An you only want buttons on 'OrdersFrm' and 'ReturnsFrm', then create the button on the design view of the 'OrdersFrm' and 'ReturnsFrm' separately.. This will automatically load the form with the button.. Makes sense?

 

[KasperBN]

Makes perfect sense. However much I try it though, it does not show up on Form View (the button that is). I can try and link a picture to show you what I mean.

 

[pr2-eugin]

The problem here is because the default view is Datasheet.. try adding a Form header or footer, and place the button there.. That would sort out..

 

[KasperBN]

Would I not need to open the form in Form view and not datasheet view, if that was to sort it out? If I place the button in the Form header and view it in datasheet mode, it does not show up, but it does in Form view. I need it to show up in datasheet view however.

 

[pr2-eugin]

This might be trickier that I thought.. I am not on my work station so do not have Access on me.. So I am unable to test this out.. Try creating a sub form with the Datasheet if that would be a better solution.. ??!!??

 

[KasperBN]

I am not completely sure what you mean pr2-eugin. I mean what sort of sub form would I place in the original form that would sort it out?

 

[boblarson]

You should be able to just make the form CONTINUOUS view and be fine.

 

[KasperBN]

You should be able to just make the form CONTINUOUS view and be fine.

Sorry for the long time answering, I was on vacation. Where would I do this boblarson? This is a navigation control form, not a normal form, and there is no default view as if it was a normal form?

 

[KasperBN]

If you mean the original form, that will show up in the navigation control, then it will not show up as a datasheet in the navigation control, which is kind of problematic, as it does not give a very good view of things at all.