Dlookup help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a subform where I enter Invoice Details like :Invoice Issuer,Invoice
Number,amount.somtimes I recieve duplicate Invoices. so I am trying to do
somthing so prevent duplicate inv payment.
I want to loolup three things.invoice Issuer,inv number , and amount.from a
table"invoice register" and if duplicate promt user.

your help will be highly appreciated.
my main form is "invoice register form"
subform "invoice details"
 
mhmaid said:
I have a subform where I enter Invoice Details like :Invoice Issuer,Invoice
Number,amount.somtimes I recieve duplicate Invoices. so I am trying to do
somthing so prevent duplicate inv payment.
I want to loolup three things.invoice Issuer,inv number , and amount.from a
table"invoice register" and if duplicate promt user.

your help will be highly appreciated.
my main form is "invoice register form"
subform "invoice details"
Click to expand...

I think this is what you asked for:

Dim varDatePaid As Variant
varDatePaid = DLookup("PayDate", "invoice register", _
"Issuer = """ & Me.txtIssuer & """ AND InvNum = " & _
Me.txtInvNum & " AND Amount = " & Me.txtAmount)
If Not IsNull(varDatePaid) Then
MsgBox "That amount was paid on " & _
Format(varDatePaid, "d mmmm, yyyy")
End If
 
Mybe to add to Marshall post, create a function under the form

Function CheckDuplicates()
Dim varDatePaid As Variant
If IsNull(Me.txtIssuer) or IsNull(Me.txtInvNum) Or IsNull(Me.txtAmount) Then
CheckDuplicates = False
Else
varDatePaid = DLookup("PayDate", "invoice register", _
"Issuer = """ & Me.txtIssuer & """ AND InvNum = " & _
Me.txtInvNum & " AND Amount = " & Me.txtAmount)
If Not IsNull(varDatePaid) Then
MsgBox "That amount was paid on " & _
Format(varDatePaid, "d mmmm, yyyy")
CheckDuplicates = True
Else
CheckDuplicates = False
End If
End If

End function

Call this function on the Before update of each of the three fields, or
ceate it on the before update event of the form

Cancel = CheckDuplicates()
 
Back
Top