auto generate barcode number

[issactang]

given that in the table --books
field--barcode
type is string

the default value is "n288826830000"

I would like to make it autogenerate when I input new books via the form and not as a primary key

thus when I enter a book, the barcode should be
n288826830001, which has the ability +1 from the default value.

I have attempt to dmax the field in load as follows:

Private Sub Form_Open(Cancel As Integer)
Dim strmax As Long
strmax = DMax("Barcode", "books") + 1
 
Me.Barcode.Value = strmax
 
 
End Sub

I got runtime level error 6, with chinese error message that I don't even know what is it.

I know that it should be a number before I can strand the last digit and add one,
how about add one to the text, how could this be done ?
thanks.

[Rabbit]

How did you expect to add 1 to a string? Extract only the number portion, convert it, and then add one.

[issactang]

yes, that is what I want ;)
but another thing Is i want it to check the previous code
if it is up to n288826830001 the next one should be n288882683002
how is the syntax and which function to use ?
thanks,

[issactang]

i would like to ask how do you extract a string ?
convert and put it back ??

[Mariostg]

There are many ways to achieve this. Does your initial string always follow the same pattern? I mean, one letter, followed by the digits?

[issactang]

yes.
Ineed, I thought I am stating the obvious
in table BOOKS
field barcode
the type is string
the default value is "n28888268300"

I would like to make it autogenerate barcode in my form when I
input new books and hit a button to save, clear then be ready for the next book input.

I am focusing on the former one, which is the ability to autogenerate barcode, which is "n2888826830001" and "n2888826830002" and so on.
the default string set is "n28888268300"
I know I can do it by dmax but as "n28888268300" is a text I have no idea how to turn plus one.
I tried to plus one with "1" but giving me 978xxxx01 values and later on run time error 6

thus would you mind suggesting me one way to
make this happen ?
my logic is
1.remove the default value from the table as this will be handled by code in the form
2. set string as follow ( pseudocode)

dim counter as integer
str= "n28888268300" & counter +1
 
 

apparently this will not work and giving me wrong values because they are in different type i would like to either change it all to text and do the merging of string then save it to the record.

thanks.

[Mariostg]

Was not too sure what you meant by default value.

Anyway, here is something that should get you started I hope.

Function IncrementCode(code As String) As String
Dim prefix As String 'n'
Dim suffix As Double 'the digits'
 
prefix = Left(code, 1) 'n could be hard coded, but what if it changes...
suffix = CDbl(Replace(code, prefix, "", 1, 1)) + 1
IncrementCode = prefix & CStr(suffix)
End Function
 

[issactang]

thanks dude, this exmaple gave me a hope to generate patron barcode and continue writing programme ;)

here is my code

Function IncrementCode(code As String) As String
Dim prefix As String 'n'
Dim suffix As Double 'the digits'
'2888826830101 - 13 digits
 
prefix = Left(code, 12) 'n could be hard coded, but what if it changes...
suffix = CDbl(Replace(code, prefix, "0", 1, 1)) + 1
IncrementCode = prefix & CStr(suffix)
 
End Function
 
Public Sub add_catalog_Click()
 
Dim strmax As String
Dim str As String
Dim mycounter As String
Dim count As Integer
 
Rem connection to db
 
Dim rsbooks As ADODB.Recordset
Set rsbooks = New ADODB.Recordset
 
 
Dim sqlbooks As String
Dim sqltrans As String
Dim strsql As String     ' for patrons
 
strsql = "Select * from books"
 
rsbooks.Open strsql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rsbooks.MoveLast
MsgBox "the last barcode is " & rsbooks![Barcode]
 
str = rsbooks![Barcode]
 
 
 
Me.Barcode = IncrementCode(str)
Exit Sub
 
 
End Sub
 

I have done what you have said and used a recordset to check the previous last used barcode , so it will add up everytime I press add :)


yeah hurray, I did it.
thanks Mariostg for your support and help.