Form Suggests Next ClientID

Question

I have a form based on my Client table that is set to only allow Data Entry (does not show current records). My table has a ClientID which is the primary key. The syntax for my Client ID is:

CL_0001

CL_0002

CL_0003

etc.....

It's not a number formatted to appear as as CL_ something, but the actual string. 

I want my form to allow users to enter all information about a new client, but then enter the ClientID based on the last one that was created. So if the last ClientID was:

CL_2036

I'd like the form to enter CL_2037 for the new record. 

Any help is appreciated.

tod

Answer 1

Hi tod,

>> I want my form to allow users to enter all information about a new client, but then enter the ClientID based on the last one that was created. So if the last ClientID was:

For this requirement, you could get the last value for ClientID, and set new value for ClientID before the form record insert into table in Form_BeforeInsert event.

Here is a simple code:

Private Sub Form_BeforeInsert(Cancel As Integer)
result = DLast("TestText", "cTime", "") 'get the TextText field value of the last record in cTime table
newId = Mid(result, 4, 4)
Debug.Print "CL_" & Format((newId + 1), "0000")
Me.TestText.Value = "CL_" & Format((newId + 1), "0000")
End Sub

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

Hi tod. Is this for a multi-user database? If so, you'll have to watch out for more than one user assigning the same ClientID. Just my 2 cents...

Answer 3

You would be better off using a number (long integer) and formatting it as desired for display "CL_" & Format([ID],"0000").

If you don't care that there may be gaps in the sequence, use an autonumber and you won't need to worry about multiple users.

If you do care about gaps in the sequence, then in the before_Update event of your form use:

If Me.NewRecord Then
Me!ID = nz(Dmax("ID","Clients"),0) + 1
End If

Doing this in the Before_Update event minimizes chances of conflicts with multiple users.

Answer 4

I decided abandon the idea of special syntax for my ClientID. I just use the autonumber set to randomize. 

Thanx for the replies.

Answer 5

Hi Tod. Why work harder than you need to, right? Good luck with your project.

Answer 6

Hi todtown,

It seems you accept the suggestion from Alphonse, am I right? If so, I suggest you mark the helpful reply as answer to close this thread.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.