Theory and Implimentation

[mdschuetz]

I need some advice.

In my database I have a form.
The form holds a subform.

The form is tied to tblPurchaseOrders.
The subform is tied to tblInventoryTransactions

Inventory transactions captures specific information related to each purchase order such as quantity ordered, quantity received, part number ect...

Now the goal is to order and receive products using frmPurchaseOrder.
I've created two command buttons.
btn1 exports form and subform data to an excel file which is a template for a physical purchase order.
btn2 exports form and subform data to an excel file which is a template for a physical recieving document.

I am having difficulty in getting the subform data I desire into the btn2template properly. It posts all the data, when I just want the items received to populate.

The subform is a datasheet.

I doubt this is enough information to get any advice on. Anyone wanna see if they can help me with this? I sure would appreciate any advice.

Here is the code for my command button. This may help you understand what I am doing.

Dim objXxL As Excel.Application
Dim objWxB As Excel.Workbook
Dim objWxS As Excel.Worksheet

Set objXxL = Excel.Application
Set objWxB = objXxL.Workbooks.Open("\\mypath.xls")
Set objWxS = objWxB.Worksheets("Form")

With objWxS
.Cells(6, 3).Value = Me.txtTD
.Cells(13, 13).Value = Me.txtYes
.Cells(13, 16).Value = Me.txtNo
.Cells(10, 4).Value = Me.PurchaseOrderNumber
.Cells(8, 3).Value = Me.cboSupplierID.Column(1)
.Cells(8, 19).Value = Me.cboAccount.Column(1)
.Cells(10, 9).Value = Me.cboPJO.Column(1)
.Cells(13, 31).Value = Me.cboInspect.Column(0)


Dim xx As Integer

xx = 38

[Purchase Orders Subform].Form.Recordset.MoveFirst

While Not [Purchase Orders Subform].Form.Recordset.EOF

   

   .Cells(xx, 2).Value = [Purchase Orders Subform].Form!UnitsRecieved

   .Cells(xx, 4).Value = [Purchase Orders Subform].Form!PartNumber & " -" & [Purchase Orders Subform].Form!ItemDescription

   .Cells(xx, 15).Value = [Purchase Orders Subform].Form!UnitPrice


    [Purchase Orders Subform].Form.Recordset.MoveNext

    xx = xx + 1

Wend




End With

objXxL.Visible = True

End Sub

Marty

 

[The_Doc_Man]

First, remember to close what you open. You open but never close your Excel spreadsheet. Keep that up or do it too often and you will end up with an "Out of ..." message where "..." will be some memory-related resource. It's a tossup as to exactly which one.

Second, there is no reason to not use Excel. But is there any reason to not use Access reports directly and thus avoid the first problem completely? You can make a report that includes lines and boxes and line-item numbering and all sorts of good stuff. So much so that if you printed the report you would have a hard time knowing it wasn't created using Excel.

 

[mdschuetz]

Unfortunately,

They have to be in excel, part of our ISO 9000 compliance requires controlled forms and they chose excel as the medium in which to store them so my hands are somewhat tied there.

Thanks for pointing out that I didn't close the excel spreadsheet. I was so absorbed into getting the proper results I neglected the workflow.

I usually go through and QA my code before I finalize it anyhow, but you saved me a possible miss there.

Thanks,

Marty

 

[The_Doc_Man]

I am having difficulty in getting the subform data I desire into the btn2template properly. It posts all the data, when I just want the items received to populate.

OK, there is an Old Programmer's Rule that goes like this: Access cannot tell you anything you didn't tell it first - or tell it how to find out. There is a corrolary that says Access will tell you EVERYTHING you told it - if you don't tell it how to be selectively quiet. In your case, I suspect that your code needs to test the criteria you named. I.e. If you wrote a query to filter out the stuff you didn't want to see, what would you test?

This leads to the suggestion: Write that query and then use it to populate your spreadsheet. You didn't say whether your form populated off a query or directly from the table. But if there is a query involved, you can pre-filter your data sheets via query and still use the same (or VERY similar) recordset code to push what you want to the spreadsheets.

 

[mdschuetz]

The records are derived directly from the table I only use queries for reports. I was hoping that maybe an IF statement could be used to filter the recordset if I used it before the WHILE loop. I am not having much luck though.

 

[The_Doc_Man]

I only use queries for reports.

This is a wrongful viewpoint. Queries are your friends. You can do so much in a query to simplify your life. I would seriously question why you want to limit yourself.

Tables provide recordsets to forms, reports, VBA, and queries. BUT ... queries can do that, too. Unless you are dealing with aggregate queries, you will find that anything you can do directly to a table can OFTEN to VERY FREQUENTLY done in a table.