Lara:
In the AfterUpdate event procedure of the Product Number control on the form
put code along the following lines which first looks up the latest date for
an invoice for this product by this client; then if it finds one looks up the
unit price for the product in the invoice of that date. If it doesn’t find
one it looks up the unit price for the product in the stock table:
Dim varDate as Variant
Dim strDateCriteria As String
Dim strPriceCriteria As String
Dim curPrice As Currency
' get date of last invoice to this client for this product, if any
strDateCriteria "[ClientID] = " & Me.[ClientID] & _
" And [Product Number] = " & Me.[Product Number]
varDate = DMax("[Invoice Date]", "[YourQuery]", strDateCriteria)
If Not IsNull(varDate) Then
' get price of product in last invoice to this client for this product
strPriceCriteria = [Invoice Date] = #" & Format(varDate,"mm/dd/yyyy") & _
"# And [Product Number] = " & Me.[Product Number] & _
" And [ClientID] = " & Me.[ClientID])
curPrice = DLookup("[Unit Price], "[YourQuery]", strPriceCriteria)
Else
' get price of product in stock table
strPriceCriteria = "[Product Number] = " & Me.[Product Number]
curPrice = DLookup("[Unit Price], "[Stock Table]", strPriceCriteria)
End If
' assign price to unit price control on form
Me.[Unit Price] = curPrice
The above assumes that Product Number and ClientID are both number data
types. If text you'd have to wrap the value in quotes, e.g.
strDateCriteria "[ClientID] = """ & Me.[ClientID] & _
""" And [Product Number] = """ & [Me.Product Number] & """"
The ClientID has to be entered into the form before the Product Number for
this to work of course, so you should ensure that this is enforced, e.g by
putting code in the Product Number control's BefreUpdate event procedure:
If IsNull(Me.[ClientID]) Then
MsgBox "Please enter a client first.", vbExclamation, "Invalid Operation"
Cancel = True
End If
Ken Sheridan
Stafford, England
Lara said:
hi,
i would like to do the following:
in the invoice form:
when i type the product number i want to display the last unit price invoiced
for this same client and, if not invoiced before, i want to display the
product unit price[from stock table]
nb: invoice form gets its data from a query where invoice headers table,
invoice details table, customer table are linked together.
thank you for your valuable support,
Sincerely,
Lara
thanks again,