auto generate barcode number

[issactang]

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

the default value is "n288826830 000"

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 "n288882683 00"

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 "n288882683 00"
I know I can do it by dmax but as "n288882683 00" 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.