VBA problem using If Not In select statement

[delph]

Hi All,

Hope someone can help. I have a database with a form where a user inputs a customer number to update another database. What I want to do is put some validation on the customer number field where it checks if that customer number exists in the other database. If it doesn't I want a messsage to appear saying Invalid Customer Number and set the customer number field to blank.
To do this, I've put the following vba code in the after update event of the customer number field:
Private Sub CustNo_AfterUpdate()
If Me.CustNo Not In(Select[CustDetails].[CustomerNo] from [Cust Details])Then
MsgBox "This is an invalid Customer Number. Please input a correct Customer Number."
Me.CustNo = ""
End If
End Sub

However I think the syntax must be wrong as I get the following message with the In highlighted from If Me.CustNo Not In
Compile Error:
Expected: Expression

Can you do an If field not in (select... in vba and if so how?

Any help gratefully received
Thanks

 

[jdraw]

Do you mean a different database or a different table?

When

user inputs a customer number

to see if that number exists you can use Dcount(). If the return value from Dcount is 0 , number doesn't exist.

I think you need to be working with recordsets, but your problem/issue is not clear.

 

[delph]

Apologies for not being clear.

The [CustDetails] table is in one database and I have a seperate database in which I have linked the [CustDetails] table. The seperate database has a form which users use to input certain information that updates the original database via a macro. The Customer number is actually a reference of numbers and letters. What I want to do is check the customer number is in the original database before the update macro runs so I just want to say in vba, If the customer number is not in the original database then show msg & reset customer number on the form to blank else end if.

I hope that makes it a bit clearer as to what I'm trying to do.

Thanks

 

[WayneRyan]

d,

If DCount("[CustomerNo]", "[Cust Details]", "[CustomerNo] = " & Me.CustNo) = 0 Then
   '
   ' Customer does not exist, 
   '
   MsgBox(...)
End If

Wayne

 

[delph]

Wayne,

Apologies for the late reply, I've been off work due to a bereavement. Many thanks for the code, I'll try it.