To place items of an order across the top of a label or report in the first
row and corresponding quantities below that (in the second row) followed by
address (shipping) information on the bottom of the label, use getToken
function available below or from your favorite web site.
Here is how I accomplished this task:
In the program code, take the order detail information for each customer,
concatenate multiple records (order line items) into a single string
separated by a delimiter such as comma (",") like "item1,item2,item3" and
save it to a field such as [Item] of a (new) table such as tblLabel. You
would do the same for quantities. But name and address information need only
have one copy in the same record (row) the items are in.
Example of the new table:
tblLabel:
TABLE FIELDS RESULTING VALUES
Item as text (255) item1, item2, item3
Qty as text (255) qty1, qty2, qty3
Customer as text (255) Joe Customer
Address as text(255) where he lives
Then, create a report (or label) and set its RecordSource property to the
new table that was previously created such as tblLabel.
Place multiple text box controls across the top of the Report/Label like so:
ItemTextbox1 ItemTextbox2 ItemTextbox3 ….
QtyTextbox1 QtyTextbox2 QtyTextbox3 ….
Set the ControlSource property of these controls to =gettoken([Item],",",x)
for first row and =gettoken([Qty],",",x) for the second row. X would be a
literal value corresponding to the number or location of the control for
example 1 or 2 or 3, etc.
For example, ControlSource properties for above textbox controls would be:
=gettoken([Item],",",1) =gettoken([Item],",",2) =gettoken([Item],",",3)
=gettoken([qty],",",1) =gettoken([qty],",",2) =gettoken([qty],",",3)
You would place textboxes for customer name and address information on the
bottom as normal and set their control source property directly to their
corresponding fields names such as =CustomerName, =address, etc.
More over:
There can only be limited number of controls on the labels for order items
(even if multiple rows are used). To be able to place more order items then
the capacity of a single label, multiple labels could be used. To span your
order across multiple labels, in your code, simply create additional records,
for each customer, if the number of items exceeds the number of controls
placed on the label. Report will automatically generate multiple labels to
fit all items on the order.
Known Limitations:
i) The limitation while trying to fit multiple order items across (even in
multiple rows) of the same label could be the size of the [item] and [qty]
fields in your table for example 255 characters if [item] and [Qty] was set
to string of 255.
ii) Integers are used in the function, so the number of tokens can not be
more then the size of Integer (about 32,767)
iii) It hasn’t be thoroughly tested, so use it at your own risk.
'***** FUNCTION GetTOKEN(String, String, Integer) String ****************
Public Function GetToken(txt As String, delimiter As String, location As
Integer) As String
'It returns nth token (specified by the location variable).
'Delimiter is assumed to be a single character such as “,†or “;â€, etc.
'Usage:
'getToken(SearchString, Delimiter_Char, Location_Int)
'For example,
'Dim myToken as String
'myToken = getToken ("ABC,DEF,GHI", ",", 3)
'myToken will be set to "GHI"
'Value of 0 or negative number for location, returns the original string.
'Value of location greater then the number of tokens in the string, returns
empty string.
'If either string or delimiter is empty, it returns the original string back.
'One of the features is that it doesn’t use an array that is why it is bit
long.
'AUTHOR: Mohammad Afzal. Date 6-1-2005.
Dim pos As Integer, x As Integer, maxToken As Integer, startingPOS As
Integer, length As Integer
'*****Initial Error checking
If txt Like "" Or delimiter Like "" Then
GetToken = txt
Exit Function
End If
pos = InStr(1, txt, delimiter, vbTextCompare)
If pos < 1 And location <= 1 Then 'No token found to start with so return
original string
GetToken = txt
Exit Function
End If
'*****Count number of tokens in the string******************************
x = 0 'Counter
maxToken = 0 'Number of Tokens Found
pos = 0 'Position of Token or zero if not found
Do
pos = InStr(pos + 1, txt, delimiter, vbTextCompare)
maxToken = maxToken + 1
If pos < 1 Then Exit Do
Loop
'*****Error Checking****************************************************
If location > maxToken Then 'If requested token is out of bounds, return
empty string.
GetToken = ""
Exit Function
End If
'*****Find starting location of requested token*************************
pos = 0 'Set location to beginning
For x = 1 To location - 1 '1st token doesn't have delimiter, so only runs
if location >2
pos = InStr(pos + 1, txt, delimiter, vbTextCompare)
Next x
startingPOS = pos + 1 'First token position is one passed the
delimiter
' (assuming zero based strings)
'*****Find Length of the requested token********************************
If location = maxToken Then
length = Len(txt) - startingPOS + 1 'If last token, use length of string
because,
'it won't have a delimiter
Else
length = InStr(pos, txt, delimiter, vbTextCompare) - startingPOS
End If
'*****Return requested token********************************************
GetToken = Mid(txt, startingPOS, length) 'Kaboom
End Function
