So on my Data Entry Form, I have a dropdown list that is populated using this code:
dataSource = "EPICORSQL01"
initialCatalog = "Epicor10Live"
tableName = "dbo.Part"
columnName = "PartNum"
'Create connection
Set objCon = CreateObject("ADODB.Connection")
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Integrated Security=SSPI;Persist Security Info=False;"
objCon.Open
'generate SQL
strCon = "SELECT PartNum FROM dbo.Part WHERE AnalysisCode = 'F/P' and TypeCode = 'M' and LabelRequired_c = 1 and InActive = 0 and LabelFormat_c = 'REEL_BV' and PartNum not like '%O' and PartNum not like '%R' and PartNum not like '%C' and PartNum not like '%P' and PartNum not like '%M' ORDER BY PartNum"
'run query
Set rs = CreateObject("ADODB.Recordset")
Set rs = objCon.Execute(strCon)
'fill options
options=""
Do Until rs.EOF
If Len(options)>0 Then options = options + vbCR
options = options + rs(columnName).value
rs.MoveNext
Loop
'Close connection
objCon.Close
Set objCon = Nothing
'set options
Value=options
That field is linked to a Named Data Source called EpiPartNum.
Then, each field on the label itself that requires data from Epicor has something like this:
dataSource = "EPICORSQL01"
initialCatalog = "Epicor10Live"
tableName = "dbo.Part"
columnName = "LabelDesc_c"
Set objCon = CreateObject("ADODB.Connection")
objCon.ConnectionString = "Provider=SQLOLEDB;Data Source=" & dataSource & ";Initial Catalog=" & initialCatalog & ";Integrated Security=SSPI;Persist Security Info=False;"
objCon.Open
strCon = "SELECT " & columnName & " FROM " & Tablename & " WHERE PartNum = '" & Format.NamedSubStrings("EpiPartNum").Value & "'"
Set rs = CreateObject("ADODB.Recordset")
Set rs = objCon.Execute(strCon)
options=""
Do Until rs.EOF
If Len(options)>0 Then options = options + vbCR
options = options + rs(columnName).value
rs.MoveNext
Loop
objCon.Close
Set objCon = Nothing
Value=options
It works like this, has been for a couple of years. The issues are that it doesn’t run super speedy - and if I try sending the .btw to Bartender support, they always claim it is corrupt. It isn’t, but because they don’t have the SQL server and DB name available I think it just sits for ages until it times out. One agent said that it did open after approx 1 hour, which is a massive timeout! Hence why they suggested using the built in tooling.
But, with the built in tooling it seems to misbehave. If I put a query filter on the SQL connection in BT, then it displays that prompt first before the data entry form - and the data entry form only has 1 part in it. The nice thing about the data entry form is that I have a preview of the label displayed as an image, and it responds to changes to Part Number, Use By Date, batch number and shows a live preview.