Serialization Reset For Each Record Using Db

Legacy Poster

• April 09, 2013 16:59

Hi, all.

 

Need to print xls record.

 

Print qty is in xls file. And currently using print qty as xls db source.

 

The serialzation needs to reset to 1 for each record 

 

EX)

 

xls file below (SN is not in xls file.)

 

item qty

a      2

b      3

c      2

 

The labels need to print like below. Print all the record.

 

a 001

a 002

b 001

b 002

b 003

c 001 

c 002

 

I read below that I need to use VB script in OnNewRecord.

 

I do not know how to use VB script.

 

So could someone help me out?

 

I'm using BarTender version 10 Enterprise Automation edition with Seagull Driver 7.3.2.

 

I found OnNewRecord in BT document option in File menu.

 

I set the qty field as shared datasource and found it in VB script editor.

 

But I do not know how to reset the qty field on new record.

 

I couldn't find reset menu in the list.

 

Thank you very much for your advice.

 

0

• 

  Canguita

  ★ BarTender Hero ★

  • April 15, 2013 10:43

  Hello Michael,

   

  Thank you very much for your post.

   

  In order to make sure, that are you requesting is to set your quantity field record as 1 after to print your labels?

   

  If yes, firstly you will need to fix your serial numbers getting the quantity from Database field. for it click on "File" menu > "Print" item > click the button for "Serial Number" > select the option "Get quantity from Database field" > select the proper field. 

   

  After this you will need to create a VBscript in your label to set the printed labels quantity to 1. In OnNewRercord() Event you will need to insert a code as this:

   

   

  'Define the object names.

  dim objConn

  dim strConn

  dim rs

   

  'Define the database connection parameter constants.

  Const adOpenStatic = 3

  Const adLockOptimistic = 3

  Const adCmdText = &H0001

   

  'Connect to the Excel file.

  Set objConn = CreateObject("ADODB.Connection")

  objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Seagull\Fruits.xls';Extended Properties=""Excel 8.0;HDR=YES;"""

   

  'Create a record set of one record which is the current record being printed.

  Set rs = CreateObject("ADODB.Recordset")

  rs.open "SELECT * FROM [Sheet1$]", objConn, adOpenStatic, adLockOptimistic, adCmdText

  rs.Find "Product = '" & Field("Sheet1$.Product") & "'"

   

  'Set the "Printed" field of the record to be "1".

  rs.Fields("Printed") = "1"

  rs.Update

   

  Where you will need to change the red text for your Excel version, correct path and Database fields.

   

  Please note that you will need to create this code in OnNewRecord() event instead of OnPrintEnd() as the last event only can update the current record.

   

  Please let me know if you have any further question.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • April 16, 2013 09:11

  Dear Carlos,

   

  Thank you so much for your detailed reply.

   

  I'll test and if there's anything wrong, I'll post a reply.

   

  Good day.

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • May 22, 2013 05:57

  I am looking for the same thing how can I do it my database is a csv file mixed quote and comma how can I do it?

  Please help

     Thanks

  0
• 

  Domingo Rodriguez

  Moderator

  ★ BarTender Hero ★

  • May 28, 2013 15:43

  If you're looking to reset the serial number on your label each time a new record is printed, it doesn't matter to what type of database you're connecting to. Please check the following topic which will help you:

  http://seagullscientific.invisionzone.com/index.php?/topic/1800-to-restart-serialization-if-a-db-feild-change/

   

  This example is even more complex than what you're trying to achieve. In your case you would just need to set value = 0 "OnNewRecord" and to use "value = value + 1" on the "OnSerialize" event.

  0

Please sign in to leave a comment.