By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,793 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

auto generate barcode number

P: 25
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim strmax As Long
  3. strmax = DMax("Barcode", "books") + 1
  4.  
  5. Me.Barcode.Value = strmax
  6.  
  7.  
  8. 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.
Jan 6 '12 #1

✓ answered by Mariostg

Was not too sure what you meant by default value.

Anyway, here is something that should get you started I hope.
Expand|Select|Wrap|Line Numbers
  1. Function IncrementCode(code As String) As String
  2. Dim prefix As String 'n'
  3. Dim suffix As Double 'the digits'
  4.  
  5. prefix = Left(code, 1) 'n could be hard coded, but what if it changes...
  6. suffix = CDbl(Replace(code, prefix, "", 1, 1)) + 1
  7. IncrementCode = prefix & CStr(suffix)
  8. End Function
  9.  

Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,366
How did you expect to add 1 to a string? Extract only the number portion, convert it, and then add one.
Jan 6 '12 #2

P: 25
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,
Jan 6 '12 #3

P: 25
i would like to ask how do you extract a string ?
convert and put it back ??
Jan 6 '12 #4

100+
P: 332
There are many ways to achieve this. Does your initial string always follow the same pattern? I mean, one letter, followed by the digits?
Jan 6 '12 #5

P: 25
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)
Expand|Select|Wrap|Line Numbers
  1. dim counter as integer
  2. str= "n28888268300" & counter +1
  3.  
  4.  
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.
Jan 6 '12 #6

100+
P: 332
Was not too sure what you meant by default value.

Anyway, here is something that should get you started I hope.
Expand|Select|Wrap|Line Numbers
  1. Function IncrementCode(code As String) As String
  2. Dim prefix As String 'n'
  3. Dim suffix As Double 'the digits'
  4.  
  5. prefix = Left(code, 1) 'n could be hard coded, but what if it changes...
  6. suffix = CDbl(Replace(code, prefix, "", 1, 1)) + 1
  7. IncrementCode = prefix & CStr(suffix)
  8. End Function
  9.  
Jan 6 '12 #7

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

here is my code
Expand|Select|Wrap|Line Numbers
  1. Function IncrementCode(code As String) As String
  2. Dim prefix As String 'n'
  3. Dim suffix As Double 'the digits'
  4. '2888826830101 - 13 digits
  5.  
  6. prefix = Left(code, 12) 'n could be hard coded, but what if it changes...
  7. suffix = CDbl(Replace(code, prefix, "0", 1, 1)) + 1
  8. IncrementCode = prefix & CStr(suffix)
  9.  
  10. End Function
  11.  
  12. Public Sub add_catalog_Click()
  13.  
  14. Dim strmax As String
  15. Dim str As String
  16. Dim mycounter As String
  17. Dim count As Integer
  18.  
  19. Rem connection to db
  20.  
  21. Dim rsbooks As ADODB.Recordset
  22. Set rsbooks = New ADODB.Recordset
  23.  
  24.  
  25. Dim sqlbooks As String
  26. Dim sqltrans As String
  27. Dim strsql As String     ' for patrons
  28.  
  29. strsql = "Select * from books"
  30.  
  31. rsbooks.Open strsql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  32. rsbooks.MoveLast
  33. MsgBox "the last barcode is " & rsbooks![Barcode]
  34.  
  35. str = rsbooks![Barcode]
  36.  
  37.  
  38.  
  39. Me.Barcode = IncrementCode(str)
  40. Exit Sub
  41.  
  42.  
  43. End Sub
  44.  



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.
Jan 6 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.