HOW CAN I VALID A COMPOUND KEY WITH DLOOKUP FUNCTION

  • Thread starter Thread starter cmweb via AccessMonster.com
  • Start date Start date
C

cmweb via AccessMonster.com

In a form named PRODUCTION there are 3 text box for invoice data: xtype,
xserie, xnumber

I need valid this data from the INVOICE table, where its key is a compound
key (TPINV+SERINV+NUMINV)
I tried with

Private Sub xnumber_AfterUpdate()
Dim searchinv as string
searchinv=Dlookup("*","[Invoice]", [tpinv]="& Forms![production].[xtype] and
[xserie]= &Forms![production].[xnumber] and "&Forms![xnumber]
If searchinv ="" then
MsgBox ("Invoice doesn't exist'")
Cancel = True
End If
End Sub

.... but it doesnt work
Please help me

Thanks in advance
 
searchinv=Dlookup("[tpinv]","[Invoice]", [tpinv]="&
Forms![production].[xtype] & " and
[xserie]= " & Forms![production].[xnumber] & " and numinv= " &
Forms![xnumber])

This assumes that all 3 fields are numeric. If any are strings, you need to
put quotes around the values.

Note that you cannot use * in DLookup: you must put the name of a field.
 
Note that you cannot use * in DLookup: you must put the name of a field.
Click to expand...

....and if you're just trying to check whether a matching record exists,
use DCount() rather than DLookup().
 
John Nurick said:
...and if you're just trying to check whether a matching record exists,
use DCount() rather than DLookup().
Click to expand...

That introduces the age-old argument as to efficiency. DLookup will stop
searching at the first match, while DCount will search the entire table. If
you're generally expecting to find a match, DLookup should be more
efficient.

That having been said, I generally use DCount. <g>
 
That introduces the age-old argument as to efficiency. DLookup will stop
searching at the first match, while DCount will search the entire table. If
you're generally expecting to find a match, DLookup should be more
efficient.
Click to expand...

I forgot about that! ...but presumably the presence or absence of
indexes makes a big difference.
 
Hi Douglas
Thanks for your advice.

I wrote:

nuinvoice = DLookup("[nuinv]", "[invoice]", "[tpinv]= '" & Forms![order].
[tpinv] & "'" & " and [nuser]= '" & Forms![order].[nuser] & "'" & " and
[nuinv]= '" & Forms![order].[nufac] & "'")

But I have a syntasis error. Would you help me ?
Thanks
CM
 
What are the data types of tpinv, nuser and nuinv?

The way you've written your statement, all three of them must be text. If
one or more of them are numeric, remove the single quotes from your
statement.

Also, if they are text, and any of them are names, be careful that the name
could include an apostrophe (O'Brien). If that's the case, replace the
single quotes with double quotes. You can do this as:

... ." and [nuser] = """ & Forms![order]![nuser] & """ and " ...

or

... ." and [nuser] = " & Chr$(34) & Forms![order]![nuser] & Chr$(34) & " and
" ...

Finally, how have you declared nuinvoice? If the DLookup doesn't find a row
in the table, it's going to return Null. The only data type that can accept
Null values is a Variant, so you either need to declare nuinvoice as
Variant, or use the Nz function to insure that a value is returned:

nuinvoice = Nz(DLookup(...), 0)
 
Back
Top