HOW CAN I VALID A COMPOUND KEY WITH DLOOKUP FUNCTION

[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

 

[Douglas J. Steele]

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.

 

[John Nurick]

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

....and if you're just trying to check whether a matching record exists,
use DCount() rather than DLookup().

 

[Douglas J Steele]

...and if you're just trying to check whether a matching record exists,
use DCount() rather than DLookup().

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>

 

[John Nurick]

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.

I forgot about that! ...but presumably the presence or absence of
indexes makes a big difference.

 

[cmweb via AccessMonster.com]

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

 

[Douglas J Steele]

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)