Dropdown Menus And Printing
I have a very simple label to print that involves two codes held in an Excel spreadsheet.
Example; two colums of data:
A 1
B 2
C 3
D 4
I have created the label and linked to the spreadsheet and all works fine when scrolling through the data.
On the Prompt design, I have setup a dropdown menu looking at column 1 using the following vb script:
[b]Functions and subs[/b]
dim objConn
dim strConn
dim rs
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\labels\data.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
Public Function GetNames()
Dim strNames
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
Set rs = CreateObject("ADODB.Recordset")
Set rs = objConn.Execute("SELECT * FROM [YYY$]")
strNames = ""
rs.MoveFirst()
do while NOT rs.EOF
strNames = strNames + rs.fields("XXX") + vbCrLf
rs.MoveNext()
loop
GetNames = strNames
End Function
[b]OnFillList[/b]
Value = GetNames()
the idea is that you select from the dropdown list the item in column one and it prints a label with the selected item as one field and the corrosponding data from column two as another field.
There are two problems I am getting:
1/ the first label will not print but is held in the spool until a second print job is sent (i.e. a missing EOF or something)
2/ Whilst the data aligns in the labels design, it does not print out so. If I select C from the dropdown, I should have C and 3 on the label, but I get C and 1.
Example; two colums of data:
A 1
B 2
C 3
D 4
I have created the label and linked to the spreadsheet and all works fine when scrolling through the data.
On the Prompt design, I have setup a dropdown menu looking at column 1 using the following vb script:
[b]Functions and subs[/b]
dim objConn
dim strConn
dim rs
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\labels\data.xls';Extended Properties=""Excel 8.0;HDR=YES;"""
Public Function GetNames()
Dim strNames
Set objConn = CreateObject("ADODB.Connection")
objConn.Open strConn
Set rs = CreateObject("ADODB.Recordset")
Set rs = objConn.Execute("SELECT * FROM [YYY$]")
strNames = ""
rs.MoveFirst()
do while NOT rs.EOF
strNames = strNames + rs.fields("XXX") + vbCrLf
rs.MoveNext()
loop
GetNames = strNames
End Function
[b]OnFillList[/b]
Value = GetNames()
the idea is that you select from the dropdown list the item in column one and it prints a label with the selected item as one field and the corrosponding data from column two as another field.
There are two problems I am getting:
1/ the first label will not print but is held in the spool until a second print job is sent (i.e. a missing EOF or something)
2/ Whilst the data aligns in the labels design, it does not print out so. If I select C from the dropdown, I should have C and 3 on the label, but I get C and 1.
0
-
Shotaro Ito
★ BarTender Hero ★
Hi Rick,
#1 - Have you layout 2 labels (2 columns) on a page? In that case, when you print from multiple database record, the user prompt shown 2 times then print occurs.
#2 - From BarTender 9.3, you can use TAB character (vbTab) to separate display option and input value in a dropdown list.
[code]
strNames = strNames & rs("ColumnName").value & vbTab & rs("ColumnValue").value & vbCrLf
[/code]
In BarTender 8.0 - 9.2, display options and values are the same, when you select "C" then input value will be "C".0 -
Hi Shotaro,
#1 No, this is a single label.
#2 The display option works fine, the result in your script is reversed. What I need is to select from the drop down an item in column one, and then bsed on that selection print one field with the content fom column 1 and another field showing the content from column 2.
An example would be:
1/ select part number from dropdown
2/ on label print part number and description as two separate fields.0 -
What you would ideally want is for the database query prompt to support a pull-down list of your live data for the user to select for running the query. Unfortunately, at this time it is still just a regular text input field so this is not possible.
The use of the VB script in the user prompt form to select a record value in a field will only work in isolation to all other fields and so is not the answer either.
Your best bet is to use the regular database connection with no query and use the "Select Records at print time" dialog for the user to select the records to print. See the below training video that cover this:
http://www.seagullscientific.com/aspx/training-video-(reading-data-from-database).aspx
http://www.seagullscientific.com/aspx/training-video-(selecting-records-from-a-database-at-print-time).aspx0
Please sign in to leave a comment.
Comments
3 comments