Help-How to retreive multiple records from 1 subform to another subform?

Hi there,
Im wondering if anybody could help me on this. I have 1 subform
(EditTarget) and 2 nested subforms (EditOrderDetails and
EditProductSubform)within 1 Main Form (EditCustomer). In EditTarget
subform, I have records of "ProductName" along with its
"TargetCapacity" that is associated with each Customers (1-M
relationship). What I would like to do is to somehow retrieve the
"ProductName" records linked to customers into a textbox called
"ProductID" in EditProductSubform. Thus, when I open the EditCustomer
Main Form, it will automatically fill the "ProductID" textbox with
multiple records in Detail sections of EditProductSubform.
I tried to do it by using Dlookup but it only gives me the first value.

I guess, thats because Dlookup will only return single value. Thus, I m

trying to use Recordset. However, my code currently is not working.
Here is what I got so far:

Private Sub Form_Activate()
Me.ProductID = [ProductName]
Dim db As DAO.Database, rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("SELECT [ProductName] FROM
tblTargetCapacity WHERE [Customers ID]=" &
[Forms]![EditCustomers]![EditOrderDetails].[Form]![EditProductSubform].[For*m].[CustID])

With rs
While Not .EOF
Debug.Print ![ProductName]
.MoveNext
Wend
End With


End Sub


I dont know how to show that "ProductName"( in the line
"Debug.Print![ProductName]" ) in the EditProductSubform.
Any help or suggestions will be greatly appreciated because Im so lost
in this!!


Thanks!


Reply »

On 26 Jul 2006 13:30:03 -0700, "Ramona"
wrote:

Hi there,
Im wondering if anybody could help me on this. I have 1 subform
(EditTarget) and 2 nested subforms (EditOrderDetails and
EditProductSubform)within 1 Main Form (EditCustomer). In EditTarget
subform, I have records of "ProductName" along with its
"TargetCapacity" that is associated with each Customers (1-M
relationship). What I would like to do is to somehow retrieve the
"ProductName" records linked to customers into a textbox called
"ProductID" in EditProductSubform. Thus, when I open the EditCustomer
Main Form, it will automatically fill the "ProductID" textbox with
multiple records in Detail sections of EditProductSubform.
I tried to do it by using Dlookup but it only gives me the first value.

I guess, thats because Dlookup will only return single value. Thus, I m
trying to use Recordset. However, my code currently is not working.

Your basic error is assuming that data is stored in Subforms or in
Textboxes. It isn't. It's stored in tables; the form is only a window,
a tool to let you view data.

If - and I REALLY have to question the validty of this entire
operation!!! - you want to copy multiple detail records, an Append
query into the table upon which the Product Subform is based would be
the appropriate technique.

John W. Vinson[MVP]

Hi John,
Thanks for replying! After I worked it out for quite some time, I
managed to do it. Your suggestion actually helped me.

Thanks!
Ramona

John Vinson wrote:
On 26 Jul 2006 13:30:03 -0700, "Ramona"
wrote:

Hi there,
Im wondering if anybody could help me on this. I have 1 subform
(EditTarget) and 2 nested subforms (EditOrderDetails and
EditProductSubform)within 1 Main Form (EditCustomer). In EditTarget
subform, I have records of "ProductName" along with its
"TargetCapacity" that is associated with each Customers (1-M
relationship). What I would like to do is to somehow retrieve the
"ProductName" records linked to customers into a textbox called
"ProductID" in EditProductSubform. Thus, when I open the EditCustomer
Main Form, it will automatically fill the "ProductID" textbox with
multiple records in Detail sections of EditProductSubform.
I tried to do it by using Dlookup but it only gives me the first value.

I guess, thats because Dlookup will only return single value. Thus, I m
trying to use Recordset. However, my code currently is not working.

Your basic error is assuming that data is stored in Subforms or in
Textboxes. It isn't. It's stored in tables; the form is only a window,
a tool to let you view data.

If - and I REALLY have to question the validty of this entire
operation!!! - you want to copy multiple detail records, an Append
query into the table upon which the Product Subform is based would be
the appropriate technique.

John W. Vinson[MVP]

Hi John,
Thanks for replying! After I worked it out for quite some time, I
managed to do it. Your suggestion about the Append Query actually
helped me.

Thanks!
Ramona

John Vinson wrote:
On 26 Jul 2006 13:30:03 -0700, "Ramona"
wrote:

Hi there,
Im wondering if anybody could help me on this. I have 1 subform
(EditTarget) and 2 nested subforms (EditOrderDetails and
EditProductSubform)within 1 Main Form (EditCustomer). In EditTarget
subform, I have records of "ProductName" along with its
"TargetCapacity" that is associated with each Customers (1-M
relationship). What I would like to do is to somehow retrieve the
"ProductName" records linked to customers into a textbox called
"ProductID" in EditProductSubform. Thus, when I open the EditCustomer
Main Form, it will automatically fill the "ProductID" textbox with
multiple records in Detail sections of EditProductSubform.
I tried to do it by using Dlookup but it only gives me the first value.

I guess, thats because Dlookup will only return single value. Thus, I m
trying to use Recordset. However, my code currently is not working.

Your basic error is assuming that data is stored in Subforms or in
Textboxes. It isn't. It's stored in tables; the form is only a window,
a tool to let you view data.

If - and I REALLY have to question the validty of this entire
operation!!! - you want to copy multiple detail records, an Append
query into the table upon which the Product Subform is based would be
the appropriate technique.

John W. Vinson[MVP]