searching for some advice

[Chummly66]

Hey all,

I am working on something and was wondering what you guys thought would be the best way of doing this.

I have a "OrderEntry" order form, and on the form there is info entered on the form itself, and there is info entered in a subform on the "OrderEntry" form.

On the main part of the form, I enter in PO#, PR#, Date Ordered and Date Completed. this info is saved in a table called "PartOrders".

On the subform, it has drop lists which allow me to select manufacturer, model, part description, and enter in quantity and date each item was received. This is stored in a table called "PartOrders Parts"

My goal is to enter in an order, and have that order exported out to a spreadsheet. Having looked around in here, there are those that say the best way is to open Excel and import the date, while some show how to export the data to the actual spreadsheet file.

Being that the data in my database is in two separate tables, would you recommend doing a query (which also by the way allows me to select only orders that need to be processed vs those already ordered base the field "date Ordered") and then exporting that to excel, or perhaps query into a temp table and then going to excel and importing the info.

As for the formatting in Excel, there really isnt anything exciting there. There is a top part which needs to be there (so the importing of data should start about line 5 in the spreadsheet).

I was just looking for suggestions before I dive into this and find out it could have been easier and more efficient by doing it one way vs the other, and since you all have the knowledge, I figure who better to ask??

thanks guys!!

Richard

 

[Golom]

What's the purpose of getting Excel involved?

Are you planning to do some manipulation of the data in the spreadsheet or is it just a presentation issue?

If you're just trying to build a displayable and/or printable Purchase Order then you can probably get that from an Access report without messing with exporting to Excel. That has several added benefits

- You don't need to contend with issues about data conversion between Access & Excel
- The users don't need to have Excel installed.
- You get to tailor your formatting. If you export to Excel you are not going to see a good presentation-quality report.
- If you go the Excel route with this Parent-Child relationship you will probably need to create an excel object in your code and custom-load the cells with what you want to see. Just exporting a JOINed query will show everything (Parent data and Child Data) in every line.

Exporting to Excel is fairly easy (check out the TransferSpreadSheet option of DoCmd) but getting to look the way you want is a different matter entirely.

 

[Chummly66]

Golom,

The only purpose of sending the data to an Excel sheet, is to email the file to our inventory person.

I believe they she can do it because all our order forms are in excel, and each order form has 7 columns across:

ID, Quantity, Price each, Description, Mfg Part #, Suplier #, and Total Cost

And I am sure they have it somehow automated either by code or macros to import the data into the ordering system.

I'll take a peek at the Gransferspreadsheet option of DoCmd.

thanks for the reply!
Richard