Possible ?? A loop? to populate subform table from an existing table?

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Hi,
My record source data is a form with a subform. The
subform is parent child with relation to the primary key.
This is the recordset I want to extract data from for a
new table. Data in the existing subform can have as a
reference the same unique number from the main form.For
example main form is number 5820 (also the primary key)
and the items in the sub form are linked like -

5820 red door 7 $200 1400
5820 green door 2 $100 200

My new form requires the information from the existing
subform table to populate its subform so that it can be
used as a packing list. From the new form a combo box
allows the number 5820 to be selected and populates fields
on the main form name address etc. Now to get the
information to the sub form which is the packing list

The following code will give me the first line for the
subform.
Dim strFilter As String

'Evaluate filter before it is passed to Dlookup function
strFilter = "[Works No:]= " & [WorksNumber].Column(0)
Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup
("[Invoice Details:]", "[tblInventory Tracking]",
strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))

What I would like to do is get the next line to append in
code.

I have considered the following.
Dim strFilter As String

'Evaluate filter before it is passed to Dlookup function
strFilter = "[Works No:]= " & [WorksNumber].Column(0)
Dim i As Integer

i = 1
For Each strFilter In [tblInventory Tracking]

'Update the SubForm Controls from the dlookup

Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup
("[Invoice Details:]", "[tblInventory Tracking]",
strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))

i = i + 1
Next

But I know this is not right.
Is there a way in code to do this - trying to automate so
that anyone can produce a commercial invoice and packing
slip from a job card / quote.
If I create an invoice or packing list from the original
table the new forms will also have auto numbers and a
combobox would still have to select the number to locate
records to populate the subform.
Kind regards
Bill
 
Bill,

why would you want or need to do it this way, when you can just use a saved
query as the recordsource for output to a report? You already have
everything you need within the two underlying tables.
Open the query designer, and select both (or all relevant) tables. Make sure
there is a join on the PK from your Main table to the FK in your secondary
table. Then drag all fields (which you'd like to show in your packing list)
into the grid including the PK from your MAIN table. Unless you want to show
the PK in the list, simply uncheck it, and add to the criteria line directly
below:
[Forms]![Name of Your Main Form]![Name Of Control On Main Form Holding PK]
Then add a Command Button to your form to open your Report. This will
effectively restrict the output to only those records shown in the subform
whose FK is equal to the PK in the main form.

Apologies if I've misunderstood your situation.

Robert Dale

Bill said:
Hi,
My record source data is a form with a subform. The
subform is parent child with relation to the primary key.
This is the recordset I want to extract data from for a
new table. Data in the existing subform can have as a
reference the same unique number from the main form.For
example main form is number 5820 (also the primary key)
and the items in the sub form are linked like -

5820 red door 7 $200 1400
5820 green door 2 $100 200

My new form requires the information from the existing
subform table to populate its subform so that it can be
used as a packing list. From the new form a combo box
allows the number 5820 to be selected and populates fields
on the main form name address etc. Now to get the
information to the sub form which is the packing list

The following code will give me the first line for the
subform.
Dim strFilter As String

'Evaluate filter before it is passed to Dlookup function
strFilter = "[Works No:]= " & [WorksNumber].Column(0)
Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup
("[Invoice Details:]", "[tblInventory Tracking]",
strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))

What I would like to do is get the next line to append in
code.

I have considered the following.
Dim strFilter As String

'Evaluate filter before it is passed to Dlookup function
strFilter = "[Works No:]= " & [WorksNumber].Column(0)
Dim i As Integer

i = 1
For Each strFilter In [tblInventory Tracking]

'Update the SubForm Controls from the dlookup

Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup
("[Invoice Details:]", "[tblInventory Tracking]",
strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))

i = i + 1
Next

But I know this is not right.
Is there a way in code to do this - trying to automate so
that anyone can produce a commercial invoice and packing
slip from a job card / quote.
If I create an invoice or packing list from the original
table the new forms will also have auto numbers and a
combobox would still have to select the number to locate
records to populate the subform.
Kind regards
Bill
Click to expand...
 
Back
Top