customer ID is not being generated

[msa969]

Hello I have a form called:
Add a new customer.
This form will be used by a receptionist. This form will collect the customer details in tblCustomer. It will allow the receptionist to enter first name and last. When last name is entered it should generate a customer ID called custID. This is the code I have typed in in MS Access 2007.

CustID=Upper(Left([Lastname],3)) & format(DCount("custid","tblCustomer","000"))

This is how I did it by selecting the Lastname bound control, go to its Property Sheet--> Events tab --> After Update. Click on the ellipsis (three dots), select Code Builder and entered the code above.

Now when I go to test the form it says:

You must enter a value in tblCustomer.CustID field.

Please help me.

[OBP]

The custid in the Dcount Function needs to be in square brackets not inverted commas.
CustID=Upper(Left([Lastname],3)) & format(DCount([custid],"tblCustomer","000"))

However I am not sure that your code will work, don't you need to add 1 to the Value of custid, and as well as that isn't custid a text field?
I think you may need to use a recordset to do what you want, or use an Autonumber field and add the Customer name to it.

[msa969]

I have changed the line of code in the Lastname (AfterUpdate)

CustID = UCase(Left([Lastname], 3)) & Format(DCount([CustID], "tblCustomer", "000"))


Now when I fill in the form ... i.e firstname and lastname. After entering lastname Access returns with an error:
Run time error '94':
Invalid use of Null

So when I debug the second line is highlighted as the problem:

Private Sub Lastname_AfterUpdate()
CustID = UCase(Left([Lastname], 3)) & Format(DCount([CustID], "tblCustomer", "000"))
End Sub

Please help

[OBP]

place a ' in front of the
CustID = UCase(Left([Lastname], 3)) & Format(DCount([CustID], "tblCustomer", "000"))
line to prevent the error and add this line
msgbox Format(DCount([CustID], "tblCustomer", "000"))
to see what you are getting from the Table when using the Dcount function.

[msa969]

place a ' in front of the
CustID = UCase(Left([Lastname], 3)) & Format(DCount([CustID], "tblCustomer", "000"))
line to prevent the error and add this line
msgbox Format(DCount([CustID], "tblCustomer", "000"))
to see what you are getting from the Table when using the Dcount function.

Sorry OBP,

Should I place a single quote ' ?
And if so where do i put the singel quote '
Thank you inadvance.

[OBP]

A single quote at the start of the line
CustID = UCase(Left([Lastname], 3)) & Format(DCount([CustID], "tblCustomer", "000"))

A single quote is used for Remarks by VBA (the old REM statement in BASIC) so anything to the right of the single quote is ignored.

[msa969]

Hello I have changed the code so it is like this:
Private Sub Lastname_AfterUpdate()Me!CustID = UCase(Left(Me!Lastname & "", 3)) & Format(1 + DCount("*", "tblCustomer", "000"))End Sub

This does not really work the way I want it to. For example for new customer Camron Kamal the custid becomes kam1. When I want to enter a new customer with the same surname it does not work. For example James Kamal the custid also becomes kam1.
I want to enter the new customer details in tblCustomer like this: ( Customer firstname and second name) Ursula Bear becomes BEA009. This is because Ursula Bear is the 9th person in my database.
How do I modify it so it behaves similar to the way I want it to. Thank you.

[OBP]

If you have entered Camron Kamal and run your code what value do you get if you use the correct Dcount Function?
The Dcount Function takes this Format
DCount Function=DCount("[Tutor ID]","Tutor","left([Tutor Surname], 3) = '" & Left([Tutor Surname],3) & "'")