DLookup

[Rafi]

I am using a form to record check payments and would like to view the invoice
amount while I am entering the payment. Since a single payment can be
applied to multiple invoices I need help with the following DLookup

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "InvoiceID = " & Me!InvoiceID

' Look up Invoice's Amount price and assign it to InvoiceAmount control.
Me!InvoiceAmount = DLookup("Expr1", "QryHours", strFilter)

When I enter the InvoiceID the first time the InvoiceAmount field is updated
with the correct value. However, when I enter the second, or third,
InvoiceID the previous values assigned to InvoiceAmount change to reflect the
value ofthe most recently entered InvoiceID. How can I prevent this from
happening? I would like to be able to display multiple lines each with a
unique InvoiceID and its corresponding and unique InvoiceAmount

Thanks

 

[Klatuu]

A Dlookup only returns one value. Use the DSum instead.

 

[Rafi]

Klatuu - Thanks for your reply. I am not interested in summing the values
but rather display multiple lines. I need to retreive the unique
InvoiceAmount for Invoices123, 124 and 129 as illustrated below.

123; 556.27
124; 758.00
129; 1200.00

Thanks

 

[Klatuu]

Okay, then you need a sub form with the filtering and data to show all the
invoices you want to see.

 

[Rafi]

I already have a Form / subform structure. The main form has two bound
controls: Date and CheckNo and the subform has the following controls:

InvoiceID: a combobox listing unpaid invoices
PmtAmount: a bound control to capture the amount paid
InvoiceAmount: Retreived by the Dlookup function.

On the subform I would like to be able to select multiple InvoiceID, and
display the IncoiceAmount for each InvoiceID along with the Payment
information (amount) for each InvoiceID

Thanks

 

[Klatuu]

Raf,

Sorry, but this is more complex than I can help with without seeing
everything. It seems to me that if you have all the invoices that could be
covered by the payment, there should be no problem. There is somehing
missing I don't understand.