-
Jul 10th, 2021, 10:53 PM
#1
Thread Starter
Lively Member
[RESOLVED] Displaying a Form inside another (unbound) Form after it has been dynamically filled?
Doing this with vba in MS Access.
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, this form is saved as a PDF.
I would like to have the third form 'Orders_Print' be displayed in the 2nd forms 'Orders' for easy reading. Im trying to 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, not the dynamic current data.
Im new to all of this (its been years since ive used vb) and a bit lost right now. Everything is working except that i can not display the 'Order_Print' form in the '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)...
Code:
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 ?
Code:
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
~ What was once an opinion, became a fact, to be later proven wrong... ~
-
Jul 10th, 2021, 11:18 PM
#2
Thread Starter
Lively Member
Re: Displaying a Form inside another (unbound) Form after it has been dynamically fil
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 or the Sub from
Code:
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
Code:
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
~ What was once an opinion, became a fact, to be later proven wrong... ~
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|