Which Event Adodb Database Connection?
Hi again. I am trying to print multiple labels based on Customer Account Number stored in a Visual Foxpro database. The user will use a handheld barcode scanner to scan the parcels. I have used Bartender Prompt Design screen and applied Linked Sub-String to a Text field on a label (with Frequency Every Record set). It is supposed to extract Customer Account Number and print labels accordingly. I am trying to avoid having multiple pop-up screen for SQL Query and Bartender prompt popups for every record label, but giving the user flexibility to reprint the labels in case they need to.
(1) Under which event in the event list should I place these codes for adodb connection to connect to the database & close connection?
Set dbconn = CreateObject("ADODB.Connection")
dbconn.ConnectionString = "provider=vfpoledb.1;;data source=C:\_PROJECTS\RDAUWH\DATA\custmast.dbf"
dbconn.Open
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open ("SELECT `fullname`, `aux`, `add`, `loc`, `pcde`, `state`, `dpid`, `fmnum`, `country`, `ipl_day`, `ipl_month` FROM `custmast` WHERE `accnum` = '" + acc_no + "'"), dbconn
If rs.RecordCount = 1 Then
Format.NamedSubStrings("ref_aux").Value = rs.Fields("aux").value
Format.NamedSubStrings("ref_add").Value = rs.Fields("add").value
Format.NamedSubStrings("ref_loc").Value = rs.Fields("loc").value
Format.NamedSubStrings("ref_state").Value = rs.Fields("state").value
Format.NamedSubStrings("ref_pcode").Value = rs.Fields("pcde").value
Format.NamedSubStrings("ref_country").Value = rs.Fields("country").value
Format.NamedSubStrings("ref_barcode").Value = value
Format.NamedSubStrings("ref_fm").Value = rs.Fields("fmnum").value
Format.NamedSubStrings("ref_ipl_day").Value = rs.Fields("ipl_day").value
Format.NamedSubStrings("ref_ipl_month").Value = rs.Fields("ipl_month").value
End If
dbconn.close
(2) Will the print popup window (ie. Bartender prompt) remain open for subsequent user scans, or does the user have to initiate a print from Bartender Print Station for every new parcel?
If not, how do I control the popup window to remain open until there is no more parcels label to print ?
(3) I am also trying to link up a digital weighing scale (Metler Toledo) to the prompt screen. But it does not seem to work. Is it because I am using Bartender 9.4 TRIAL copy? The weighing scale properties Test Connection returns garbage characters:
Sent to scale: W
Received from scale: 5.²´
Received from scale: ëç <LF>
Sent to scale: W
Received from scale: 5.²´
Received from scale: ëç <LF>
...and when I click on Start Test button, I am getting "No Data Received" message.
Sorry for the long questions. I am very new to this software.
Thank you.
(1) Under which event in the event list should I place these codes for adodb connection to connect to the database & close connection?
Set dbconn = CreateObject("ADODB.Connection")
dbconn.ConnectionString = "provider=vfpoledb.1;;data source=C:\_PROJECTS\RDAUWH\DATA\custmast.dbf"
dbconn.Open
Dim rs
Set rs = CreateObject("ADODB.Recordset")
rs.Open ("SELECT `fullname`, `aux`, `add`, `loc`, `pcde`, `state`, `dpid`, `fmnum`, `country`, `ipl_day`, `ipl_month` FROM `custmast` WHERE `accnum` = '" + acc_no + "'"), dbconn
If rs.RecordCount = 1 Then
Format.NamedSubStrings("ref_aux").Value = rs.Fields("aux").value
Format.NamedSubStrings("ref_add").Value = rs.Fields("add").value
Format.NamedSubStrings("ref_loc").Value = rs.Fields("loc").value
Format.NamedSubStrings("ref_state").Value = rs.Fields("state").value
Format.NamedSubStrings("ref_pcode").Value = rs.Fields("pcde").value
Format.NamedSubStrings("ref_country").Value = rs.Fields("country").value
Format.NamedSubStrings("ref_barcode").Value = value
Format.NamedSubStrings("ref_fm").Value = rs.Fields("fmnum").value
Format.NamedSubStrings("ref_ipl_day").Value = rs.Fields("ipl_day").value
Format.NamedSubStrings("ref_ipl_month").Value = rs.Fields("ipl_month").value
End If
dbconn.close
(2) Will the print popup window (ie. Bartender prompt) remain open for subsequent user scans, or does the user have to initiate a print from Bartender Print Station for every new parcel?
If not, how do I control the popup window to remain open until there is no more parcels label to print ?
(3) I am also trying to link up a digital weighing scale (Metler Toledo) to the prompt screen. But it does not seem to work. Is it because I am using Bartender 9.4 TRIAL copy? The weighing scale properties Test Connection returns garbage characters:
Sent to scale: W
Received from scale: 5.²´
Received from scale: ëç <LF>
Sent to scale: W
Received from scale: 5.²´
Received from scale: ëç <LF>
...and when I click on Start Test button, I am getting "No Data Received" message.
Sorry for the long questions. I am very new to this software.
Thank you.
0
-
Shotaro Ito
★ BarTender Hero ★
Dear Kiat,
1) If you want to take value from prompt input then update label's substrings, place script to "OnPostPropmt" event of Object datasource.
(There's no way to [b]update[/b] prompt text responding to prompt input, so you have to update value [u]after[/u] prompt closed)
2) Enable "Automatically print again" in Print dialog's Option minitab, so after print, it return to user prompt(or query prompt) again.
3) Trial copy would work fine with Scales. As data is not properly transferred, please check Scale Properties > Connection tab. Make sure serial connection parameters (Baud rate, Data bits, Parity, Stop bits and flow control) are properly matches with Scale's port settings.
Try connecting via Hyper Terminal or any terminal software, to make sure the PC is able to connect to the scale properly.
Hope that helps!0 -
Thank you Shotaro san. I followed your advice and it worked.
I have a question about using VB Script on Bartender...
What is the difference "Functions and Subs" and "OnProcessData" inside More Options > VB Scripting... versus.. "Multi-Line Line Script" and "Event Control Scripts" inside VB Script, when we specify the Substring source ?
I noticed that (in previous occasions) the database actually got updated several times in succession (same data) when using VB Script. Similarly I am getting a test MsgBox pop up so many times. Like as if there is some kind of encapsulation of classes. And I am not exactly sure whic class is being executed.
If so.. is there a precedence in VB Scripting in Bartender, which class (and/or events) gets executed first?
(also btw... whenever I insert a MsgBox in a code for test, and click on OK to exit object property, Bartender would execute the MsgBox several times, and when I try to enter the object property again, Bartender executes MsgBox again repeatedly, and then freezes, and restarts, without saving the btw file.)0 -
Shotaro Ito
★ BarTender Hero ★
Dear Kiat,
Frankly speaking, I don't have deep knowledge of VB Script of BarTender.
You can get some info from BarTender's online help > Visual Basic Scripting - unfortunately there's not much detail about what you've asked.
There's Substring level VB script and Format Level VB script.
About Substing level VB Script, what I can tell in my rough recognition is,
- Single line expression / Multi line script
Works as datasource, creation of substring data.
Single line expression
[code]"Hello world"[/code]
is equal to Multi line script
[code]Value = "Hello world"[/code]
(it looks these scripts executed in several times in a print job)
- Event control script
Works as datasource, runs at specified occasion.
You can set multiple event to single substring, such as "OnPrePrompt" to pre-enter prompt data,
then "OnPostPrompt" to process after prompt input.
Default Value shown in design view is defined in "OnAutoSelectedEvent".
- OnProcessData in More options VB Script
Works as post processing of datasource. You can modify input data - such as Database field, User prompt input etc.
datasource is referenced by keyword "Value".
[code]'Format number by comma separator (1000 to 1,000)
If IsNumeric(Value) Then Value = FormatNumber(Value, 0) [/code]
- Functions and Subs
You can place Functions and Subs in here for repeatedly used process in the a substring.
Datasource's Functions and Subs are not format global.
That means, if you want to use the same Function in many text objects,
you have to place the same Function on each text's datasource.
You can set VB Script to populate dropdown list's options, and Modify data in Query Prompt input too.
(Guys, please tell me if you know more detail about this.)
About Format Level VB Script,
It is able to control object's property such as Box's fillcolor, font size or barcode location etc.
Format level VB Script is new function from BarTender 9.3 and only available from Automation edition or above.
[url="http://s3.amazonaws.com/SeagullBarTender/ftp/WhitePapers/WhitePaper_DynamicallyChangingObjectsAtPrint-TimeUsingVBScript.pdf "]WhitePaper:Dynamically Changing Objects At Print-Time Using VBScript[/url]
About your crash of BarTender, the object might be broken. if you repeatedly have that problem, delete the object (Text, barcode etc) and create the same object again, then apply the script.
If you have certain reproducible pattern and that prevent you to create desired format, please contact to Technical support with the format attached.
Hope that helps!0 -
Thank you Shotaro san for your prompt reply.
I hope Seagull can provide us with more information about how VB scripting works inside Bartender.
What happenned yesterday, when I tried to read (and print) address record onto a label, (test) I placed some codes inside Multiline scripts, it prints, but on printing 2nd label onwards, it prints the previous record (even though I am using a WHERE clause in my SQL), and it prints the address twice on the same label too (giving me the hint that the code is executed twice). But when I place the [u]same [/u]code inside Screen Data VB Scripting, it works as expected. So I am confused about how these classes works.
I also have this problem, when I transfer the btw file from one Bartender client and use on another Bartender client, make some modifications to that btw file, to change data source and delete some of the previous objects, save the btw file, but it still "remembers" the previous datasource and picture object. It keeps appearing as error message saying previous database field not found when printing (even though I have saved the object numerous times).
I am just wondering if there is any command lines to refresh/regenerate the btw file, to clean up the old object reference inside the btw file.
Anyway, I will investigate more about how the code behaves inside Bartender.
Thank you again.0
Please sign in to leave a comment.
Comments
4 comments