Purchase Order Continuation

[steve21nj]

Issue: the code below opens another form (not report) that displays details of a purchase order. The form was created to mirror a gov form that we use, and the 10 line items are hard coded. when the code is run, it will display the first 10 items.

If the original form, where I am entering the purchase order info has 15 items, it doesn't push the 5 additional items on to a new page.

How would I accomplish this with what I have now?

Private Sub btnDH_Click()
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "DH01"
    stLinkCriteria = "[RequisitionNumber]=" & "'" & Me![RequisitionNumber] & "'"
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
 
Set db = CurrentDb
 
Set rst = db.OpenRecordset("SELECT * FROM tblPurchaseOrderDetails WHERE fk_RequisitionID=" & RequisitionID)
i = 1
If rst.RecordCount > 0 Then
   Do Until rst.EOF
    If i >= 11 Then Exit Do
     Forms!DH01.Controls(i & "txtItem1") = rst("ItemDescription")
     Forms!DH01.Controls(i & "txtItem2") = rst("StockNum")
     Forms!DH01.Controls(i & "txtItem3") = rst("Quantity")
     Forms!DH01.Controls(i & "txtItem4") = rst("UnitofIssue")
     Forms!DH01.Controls(i & "txtItem5") = rst("UnitPrice")
     Forms!DH01.Controls(i & "txtItem6") = rst("Quantity") * rst("UnitPrice")
    i = i + 1
   rst.MoveNext
   Loop
End If
 
rst.Close
Set rst = Nothing
End Sub

 

[gemma-the-husky]

personally, I would have thought the fact that what you are mirroring only shows 10 items, does not mean that you should actually manage it that way.

out of interest, does a 15 line order constitute 1 order, or 2 orders?

if 1, then I imagine your system should have no upper limit on order size

 

[steve21nj]

the purchase order can have 50 lines, but the previous person only wanted to display the first 10 and not print out pages 1-5, just page 1.

is there an easy fix to this? if I were to modify the line

If i >= 11 Then Exit Do

to say

If i >= 21 Then Exit Do

it'll just kick errors saying "11txtItem1 doesn't exist" because the form only goes to 10txtItem1.

 

[gemma-the-husky]

the problem appears to be that the form is hard coded with all the data lines.

A normal representation is to use a continuous form (or datasheet). Effectively you code one row, and access then "knows" how to duplicate this over all the rows.

to solve it in your terms, you would need to copy the details for row 10 etc, to as many new rows as you need. Bear in mind there is a maximum limit of controls on a form, which you may run into (not 100% sure what that is, though)