Problem using Form_BeforeInsert()

[Sonny Jim]

I was given the following code to enable me to prefix my primary key with "ACS-" and to start the numbering at "1500" but now I'm discovering that I cannot advance beyond the record number "9999" (ie: "ACS-9999"). Any ideas why?

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim stemp As Variant
stemp = Nz(DMax("strAcsFileNo", "tblClaim"), 0)
If stemp = 0 Then

stemp = "ACS-1500"
​

Else

stemp = Mid(stemp, 5)
stemp = stemp + 1
stemp = "ACS-" & stemp
​

End If
Me.strAcsFileNo = stemp
​

End Sub​

 

[evanscamman]

Should you be using a Long instead of a Variant?

 

[Sonny Jim]

The variant character type is needed to include the "ACS-" portion.

 

[vbaInet]

Use a string, not a variant.

You must have specified in your table that you want a max number of four digits. Increase that limit.

 

[Sonny Jim]

If I use "string" as the character type I will not be able to add "1" to it which is needed in order to advance to the next sequential index. In my table the character type is "text" and the number of digits allowed is 25 (actually far more than I need to allow).

 

[vbaInet]

Here you go:

[FONT=Times New Roman][SIZE=3]dim stemp [/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=Navy]As string[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]     stemp =  Nz(DMax("strAcsFileNo", "tblClaim"), 0)[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]     [COLOR=Navy]I[/COLOR][/SIZE][/FONT][FONT=Times New Roman][COLOR=Navy][SIZE=3]f[/SIZE][/COLOR][/FONT][FONT=Times New Roman][SIZE=3] stemp = 0 [/SIZE][/FONT][FONT=Times New Roman][SIZE=3][COLOR=Navy]Then[/COLOR][/SIZE][/FONT][INDENT][FONT=Times New Roman][SIZE=3]         stemp = "ACS-1500"[/SIZE][/FONT]
[/INDENT][FONT=Times New Roman][SIZE=3][COLOR=Navy]Else[/COLOR][/SIZE][/FONT][INDENT] [FONT=Times New Roman][SIZE=3]         stemp = "ACS-"  & val(stemp) + 1[/SIZE][/FONT]
[/INDENT][FONT=Times New Roman][SIZE=3][COLOR=Navy]End If[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]     Me.strAcsFileNo = stemp[/SIZE][/FONT]

 

[Sonny Jim]

This gives me a "type mismatch" error for the line:

If stemp = 0 Then
​

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim stemp As String
     stemp = Nz(DMax("strAcsFileNo", "tblClaim"), 0)
     If stemp = 0 Then

    stemp = "ACS-1500"

Else

    stemp = "ACS-" & Val(stemp) + 1

End If
     Me.strAcsFileNo = stemp

End Sub

 

[vbaInet]

If val(stemp) = 0

 

[Sonny Jim]

This gives me a new value of "ACS-1500". It should be "ACS-10000" as the last number was "ACS-9999".

 

[vbaInet]

That's because your DMax code isn't getting the Max value in your table. You would have to seperate your number from the string in your table if you want to get a max. Then use format() to append "ASC-" to it.

 

[Sonny Jim]

Yes, I was beginning to think that too because when I manually entered new values into the table they would be placed at the beginning of the column (ex: ACS-10000, ACS-10001, ACS-1500, ACS-1501 ... ACS-9999). However, I'm really not sure if I understand how to carry out your solution. Could you give me some more details?

 

[vbaInet]

Do this on a backup copy first:

1. Get rid of all the "ACS-" in your table data
2. Change the datatype to Long Integer

Now when you do the DMax() it would get the max value for that field.

If you look at one of the properties for that field (in the table) you will find a property called FORMAT. The format you need to enter there is:

"ACS-"000000

So when you enter 1234 it will appear as ACS-001234. Put the number of zeros you would like to appear. For the formatting I would advise you do it on form level rather than table level. A textbox control has that property as well so if you were to put that format in the textbox's Format property it will also work.

 

[Sonny Jim]

That worked great!!! Thank you very, very much vbaInet!!!

For anyone else who might try this here is the final updated code that I am using:

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim stemp As String
     stemp = Nz(DMax("strAcsFileNo", "tblClaim"), 0)
     If Val(stemp) = 0 Then

    stemp = "1500"

Else

    stemp = Val(stemp) + 1

End If
     Me.strAcsFileNo = stemp

End Sub

 

[vbaInet]

You're welcome Sonny Jim.