vba: Displaying a Form inside another (unbound) Form after it has been dynamically filled?

Question

Have a form (named: List_Items) which has acouple controls, one being a listbox with items, when an item is selected it is added to different listbox (on same form, for re-ordering), once all re-order items are filled there is a cmd Button that will save those items to the database's 'Order' Table, then that data will fill in a third form 'Orders_Print' (descibed below) and that third form is then saved as a PDF 'DoCmd.OutputTo acOutputForm, frmName, acFormatPDF, fileName' (sub listed below). This is all working fine.

Have a second form (named: Orders) which has acouple controls, one being a listbox with (order) dates.

I have a third form (named: Orders_Print) which has a few controls on it that are fill from a Sub in a Module.

I would like to have the third form 'Orders_Print' be displayed in the 2nd forms 'Orders' for eaze reading. Im tryin got use a subform control. I can get the subform's form to display the correct data, but i can not get the subform control to display this data in its parent form. As of now, the Date listbox on the 'Orders' will pass a date to the sub in the Module, fill the 3rd form 'Orders_Print' corretlly, open the Orders_Print form, but the subform control is not displaying any data, it will only display what ever static data was originally save in the Orders_Print form.

Im new to all of this and a bit lost right now. Everything is working except that i can not display 'Order_Print' form in 'Order' form.

Tried "linking" but get an unbound form error, and dont understand enough to link manually (if possible).

Also tried to Requery alot of things (and also in different orders)...

Me.Requery
subOrderDetails.Requery
subOrderDetails.Form.Requery
subOrderDetails.Form.Controls("txtVender").Requery

From the below screen shots hopfully you can see whats going on and what im tring to achive.

Heres the Sub im using to fill "Order_Print"

Is there any way to do this, the main reason for the sub is to format the Desc in the list box, i want it to be displayed in multi-line like shown in image 2 ?

Public Sub saveAsPDF(frmName As String, PO As String, Optional subForm As Boolean = False)

    DoCmd.OpenForm frmName
    
    Dim frm As Form
    Set frm = Forms(frmName)
    
    frm.Visible = True

    If subForm = True Then
        ' frm.Visible = False ' testing
    End If
    
    frm.txtVender = ""
    frm.txtPO = ""
    
    Dim ii As Integer
    With frm.lstOrderItems
        For ii = .ListCount - 1 To 0 Step -1
            .RemoveItem (.ListCount - 1)
        Next ii
    End With
    
    Dim fileName As String
    Dim vender As String
    Dim orderDateIs As String
    
    
    ' ItemID
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim i As Integer
    
    sSQL = "SELECT Orders.*, Items.ID, Items.VenderSKU as VenderSKU,Items.VenderItemName as ItemName, Venders.ID, Venders.VenderName as VenderName"
    sSQL = sSQL & " FROM (Orders INNER JOIN Items ON Orders.ItemID = Items.ID) "
    sSQL = sSQL & " left JOIN Venders ON Orders.VenderID = Venders.ID"
    sSQL = sSQL & " WHERE Orders.PO = '" & PO & "'"
    
    Set rs = getRecord(sSQL)
    Call Forms.Orders_Print.setLstItems
    Do While Not rs.EOF
        i = i + 1
        vender = rs!VenderName
        orderDateIs = Format(rs!OrderDate, "yyyy.mm.dd-hh.mm.ssAM/PM")
        orderDateIs = Replace(orderDateIs, "AM", "")
        orderDateIs = Replace(orderDateIs, "PM", "")
        
        frm.txtVender = vender
        frm.txtOrderDate = Format(rs!OrderDate, "Short Date")
        frm.txtOrderTotalCost = rs!TotalOrderCost
        
        Dim itemLen As Integer
        Dim itemNameShort As String
        Dim itemNameShortBuild As String
        Dim itemNameShortBuildArray()
        Dim x, y, z As Integer
        Dim splitStringAt As Integer
        Dim spl As Variant
        
        x = 0
        y = 0
        z = 0
        splitStringAt = 73
        
        itemNameShort = rs!ItemName
        'itemNameShort = Replace(rs!ItemName, ",", "")
        spl = Split(itemNameShort, " ")
        If Len(itemNameShort) > splitStringAt Then
            For x = 0 To UBound(spl)
                
                itemNameShortBuild = itemNameShortBuild & spl(x) & " "
                
                If Len(itemNameShortBuild) < splitStringAt Then
                    itemNameShort = itemNameShortBuild
                Else
                    ReDim Preserve itemNameShortBuildArray(y)
                    itemNameShortBuildArray(y) = itemNameShort
                    itemNameShortBuild = ""
                    y = y + 1
                    x = x - 1
                End If
                
            Next x
            
            ReDim Preserve itemNameShortBuildArray(y)
            itemNameShortBuildArray(y) = itemNameShort
            
            itemNameShortBuild = ""
            itemNameShort = ""
            
        Else
            ReDim Preserve itemNameShortBuildArray(0)
            itemNameShortBuildArray(y) = itemNameShort
        End If
        
        itemNameShortBuild = ""
        itemNameShort = ""
        
        For z = 0 To UBound(itemNameShortBuildArray)
            If z = 0 Then
                frm.lstOrderItems.AddItem rs!VenderSKU & ";'" & itemNameShortBuildArray(z) & "';" & rs!OrderQTY & ";$" & rs!TotalItemCost
            Else
                frm.lstOrderItems.AddItem ";'" & itemNameShortBuildArray(z) & "';;"
            End If
        Next z
        
        
       frm.lstOrderItems.Selected(i) = False
       rs.MoveNext
       
       frm.txtPO = PO
    Loop
    
    If subForm = False Then
        fileName = projectPath & "POs\" & vender & "\"
        Call makeDir(fileName)
        
        fileName = fileName & "PO." & PO & "_" & vender & "_" & orderDateIs & "_" & currentUserName & ".pdf"
        
        DoCmd.OutputTo acOutputForm, frmName, acFormatPDF, fileName
        DoCmd.Close acForm, frmName, acSaveNo
        Call runit(fileName)
    End If
End Sub

SidewaysUpJoe SidewaysUpJoe · Accepted Answer · 2021-07-11T04:22:04

Got it working...

The subform.control.form needs to be populated "through" the parent form. I was tring to populate the subform itself, then "refresh" it in the parent form.

Changed the top part of the Sub

from

    DoCmd.OpenForm frmName
    
    Dim frm As Form
    Set frm = Forms(frmName)
    
    frm.Visible = True

    If subForm = True Then
        ' frm.Visible = False ' testing
    End If

to

Dim frm As Form

If subForm = True Then
    Set frm = Forms("Orders").subOrderDetails.Form
Else
    DoCmd.OpenForm frmName
    Set frm = Forms(frmName)
    frm.Visible = True
End If

vba: Displaying a Form inside another (unbound) Form after it has been dynamically filled?

1 Answers