MS Access: Printing Order Details on Customer Labels

[Speckly]

Hi,

I have been having difficulty with the following problem for sometime. My boss is asking for some functionality on a MS Access database that I designed but I haven't been able to crack it yet! Please can anybody help??

The database is an ordering system, whereby customer orders (quantities and titles) for their required publication are entered. The database is able to (among other things) print labels (of customer names & addresses) for the people packing the books to stick on the packages.

The Problem: My boss wants the order items to be listed (title and quantity) on the labels - so that its much easier for the workers to pack. Currently, they use an order sheet which is printed each day and details all the orders and items.

Why I can't do it:
Many customers order more than on publication when they order. Therefore, I designed the database so that If Mrs Smith wants a Cook Book and a Car Book, there are two records associated with her order number. My difficulty is how do you merge the two records for that order so that both order item quantities and titles are printed on one label. I can manage to print 1 of the record details on the label but not both.

Please can anyone offer some direction.

Thanks

 

[GingerR]

I'm assuming that right now, Mrs. Smith's two items are being printed on two different labels?

Can't you just fiddle with the page setup/margins/columns so that every detail record associated with Mrs. Smith comes out on one label? Are you grouping on OrderID at all?

What's your table structure?


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

You can string the titles together as a string and print the the string on the label. A function would do this and be easy to call within your label print routine.

Assumming...

tblOrder
OrderID - primary key
...etc

tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to Order table
Title

The function would use the primary key, OrderID in my example, and return a string...

Function GetTitles(lngOrderID as Long) as String

Dim rst as DAO.Recordset
Dim strSQL as String, strTitles as String

strSQL = "SELECT * From tblOrderDetail WHERE OrderID = " & lngOrderID

Set rst = CurrentDB.Openrecordset(strSQL)
strTitles = ""

With rst

   .MoveFirst
   Do While Not .EOF
      strTitles = strTitles & " / " & !Title
      .MoveNext
   Loop
End rst

End Function

The code could be improved upon by adding line breaks, or truncation of longer titles or checks to make sure the text string does exceed a limit or a word wrap...

Richard

 

[Speckly]

Thanks for your suggestions. Richard your idea I think is great. I've created a sample text box on my label report. This textbox's datasource points to the function GetTitles() that you suggested. But when I preview the report, NUM!! is shown in the text box.

I think there's something wrong with how I'm processing the recordset. Can you look at it again.

Function GetTitles(LngOrderID As Long) As String

Dim db As Database 'declare a variable that points to database
Dim rst As DAO.Recordset 'declare a variable that will holds the recordset
Dim strSQL As String 'declare a variable that will hold the sql string
Dim StrTitles As String

'set the db variable equal to the currentDB
Set db = CurrentDb

'Populate the Recordset
strSQL = "Select * from [Order Details] where OrderID =" & LngOrderID

Set rst = CurrentDb.OpenRecordset(strSQL)
StrTitles = ""

'Now we've got the recordset, do some processing with its records

With rst
    .MoveFirst
    Do While Not .EOF
    StrTitles = StrTitles & "/" & ![PublicationID]
   .MoveNext
Loop


End With

End Function

The variable OrderID is actually an int but it doesn't seem to make any difference as a long.

The textbox on my report I have named StrTitles. But how do I get the result from the recordset to be displayed in the report. (BTW PublicationID is the Title of the publication and the database tables are set-up just as you described in the previous comment.)

Any ideas what I'm doing wrong??

 

[traingamer]

You need to pass the results of the function back out of the function

.MoveFirst
    Do While Not .EOF
    StrTitles = StrTitles & "/" & ![PublicationID]
   .MoveNext
Loop


End With
[COLOR=red]GetTitles = StrTitles[/color]
End Function

traingamer

 

[willir]

Whoops -- TrainGamer is correct -- my bad. Thanks for the catch TrainGamer!

Also, is PublicationID the title or a number? If it is a number, the code will generate a string of numbers -- which may be what you want.

You can also remove the "/" that appears at the end...

StrTitles = Left(StrTitles, Len(StrTitles) - 1)
GetTitles = StrTitles

Richard

 

[Speckly]

Thanks Richard, Traingamer and everyone else. It worked perfectly. Its really exciting when something that has stumped me for a while actually works!

Cheers!!