Populating Child Records From Parent Db Record Follow

Legacy Poster

After some helpful guidance from Ian C in the link below, I was able to write a script capable of performing the desired functionality. I thought it might be worth sharing for the community. I’m relatively new to BarTender so if some of the more experienced folk see some error in my logic or a better way of achieving this solution, please feel free to add to the discussion.

The gist of this implementation is that I'm taking a cabinet serial number and identifying the components we put in the cabinet. I expect up to 6 components per cabinet so I loop through the records in the second half of script to populate the component information from the DB recordset. Setting the placeholder text and barcode objects to the empty string (label=“”) keeps them from displaying if they have no value.

[url="http://seagullscientific.invisionzone.com/index.php?/topic/73-designing-labels-with-1many-relationships/"]1 to Many records discussion[/url]

dim sServer, sConn, oConn,oRS
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog= Dev"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, "aUsername", "aPasword"
Set rstComponents =CreateObject("ADODB.Recordset")

sSQL = "SELECT ComSerialNum"
sSQL = sSQL & ", COALESCE (CustRevisionICS, ComRevision, '') as Revision "
sSQL = sSQL & ", ComponentType"
sSQL = sSQL & ", COALESCE (CustComPartNum, ComPartNum) as PartNum"
sSQL = sSQL & ", ComponentDescription "
sSQL = sSQL & " FROM sp.vwLabelComponentDetail where CabSerialNum = '" & Field("vwLabelCabinetView.CabSerialNum") & "'"
sQQL = sSql & "Order by ComponentType asc"
Set rstComponents = oConn.execute(sSQL)

Rev1 = ""
Rev6 = ""

dim i
i= 1
Do until rstComponents.EOF
Select Case i
Case 1
PartText1 = rstComponents("ComponentType")
PartCode1 = rstComponents("PartNum")
SerialNum1 =rstComponents("ComSerialNum")
Rev1 = rstComponents("Revision")

Case 2-5 ...

Case 6
PartText6 = rstComponents("ComponentType")
PartCode6 = rstComponents("PartNum")
SerialNum6 =rstComponents("ComSerialNum")
Rev6 = rstComponents("Revision")
Case else
End Select
i = i+1




Please sign in to leave a comment.