Lookup account number if duplicate

[hessleroader]

we have a form where we enter new customers and create the account number as first 3 letters then 001,002, etc etc

is there someway of when we enter the account number for a new customer it checks to make sure that this is already not being used

so is i have a customer
joe bloggs, somewhere this would be JOE001

and if a have a customer with the same sort of name
joe blaggs this will tell me that if i enter JOE001 this is duplicated ( a bit like the error message when i try to save it )

i would like the error message to like " this account number is already in use please choose another"

thanks

 

[dkinley]

You can use the following on the BeforeUpdate event of the control that you are inputting the account number.

    Dim iTableID As Long
    Dim sAccountName As String
 
    iTableID = Nz(DLookup("[TableID]", "tTableNameThatHasAccountNumber", _
        "[AccountNumber]= '" & Me.txtControlNameofAccountNumber & "'), 0)
    If iTableID <> 0 Then
        sAccountName = DLookup("[AccountName]", "tTableNameThatHasAccountName", _
            "[TableID]= " & iTableID)
 
        MsgBox "This account number already exists for " & _
            sAccountName & vbCrLf & "Please enter a different accoount name.", _
            vbCritical, "Duplicate Account Number"
        Me.txtControlNameofAccountNumber.Undo
        Cancel = True
    End If

Note that iTableID is the primary key (autonumber) of the table where the account number is held and sAccount name is the name of the account for feedback purposes.

Also, this is untested air code but should get you going down the path you need.

-dK

 

[hessleroader]

hi there, getting compile syntax error


Table Name is "Customers"
here are the fields below

AccountNo ( Primary ) This is entered manually
CustomerName
Address
Town
Postcode
ContactName
Telephone



any ideas



Dim accountno As Long
Dim sAccountName As String

accountno = Nz(DLookup("[accountno]", "customers", _
"[AccountNo]= '" & Me.accountno & "'), 0)
If accountno <> 0 Then
sAccountName = DLookup("[AccountNo]", "customers", _
"[accountno]= " & accountno)

MsgBox "This account number already exists for " & _
sAccountName & vbCrLf & "Please enter a different accoount name.", _
vbCritical, "Duplicate Account Number"
Me.accountno.Undo
Cancel = True
End If

 

[dkinley]

I wasn't sure how you were storing the account number. If JOE001 then I assumed a text field so I used

= ' " & Me.txtControlName & " ') ... which is a text reference, whereas

= " & Me.txtControlName) ... is a number reference.

In the statment ...
accountno = Nz(DLookup("[accountno]", "customers", _
"[AccountNo]= '" & Me.accountno & "'), 0)

You have Me.accountno which is the same name as the variable accountno. The Me. bit is the reference to the control name, not the variable. If both are the same name, give them something to help you and the program to differentiate.

Here are some tips: http://www.mvps.org/access/general/gen0012.htm

For instance, if it is a control on a form you could use txtAccountNum as the name of the control. The iAccountNum tells you at a glance it is a number (usually integer by the i) or you could use lAccountNum so you would know it was declared as a long integer type. I also use Num for a number so I don't confuse it with No when I use Yes/No fields in checkboxes (another "s"Variable As String). Just some more ideas.

-dK