After update is not working 2

[47redlands]

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.

 

[MajP]

AFAIK, there is no native function "Upper" in Access vba. Is that a user defined function? Did you mean Ucase?

1. Compile your code
2. To verify that this code is even firing type
msgbox me.custID
after your code

 

[47redlands]

Following advice from Majp,

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

 

[MajP]

dim strName as string
dim strCount as string
dim inCount as integer
strName = UCase(left(nz([lastname],""),3)
intCount = nz(dcount("CustID","tblCustomer","CustID = "& me.custID),0)
strCount = format(intCount,"000")
CustID = strName & strCount

I assume you want to count the number of records with CustID equal the current customer ID. Your dlookup appears incorrect.

 

[47redlands]

I have tried the code as suggested by MajP. However it returns with this error:

runtime error '3075':
syntax error (missing operator) in querry expression 'CustID ='.

Here is the full code:
Private Sub Lastname_AfterUpdate()
Dim strName As String
Dim strCount As String
Dim inCount As Integer
strName = UCase(Left(Nz([Lastname], ""), 3))
intCount = Nz(DCount("CustID", "tblCustomer", "CustID = " & Me.CustID), 0)
strCount = Format(intCount, "000")
CustID = strName & strCount
End Sub


Thank you for your help

 

[PHV]

Private Sub Lastname_AfterUpdate()
Me!CustID = UCase(Left(Me!Lastname & "", 3)) & Format(1 + DCount("*", "tblCustomer", "000"))
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[47redlands]

Hello I have tried the code as suggested by PHV:

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.

 

[PHV]

OOps, sorry for the typo:

Me!CustID = UCase(Left(Me!Lastname & "", 3)) & Format(1 + DCount("*", "tblCustomer"), "000")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[47redlands]

Thanks PHV,

This seems to be working.