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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 »

.

Quantcast