Labels for one in Access 2007

[teachesms]

I used to be able to do an exercise in Access 2003 and earlier that allowed me to make a "dummy table" with two fields: an ID field and a Qty Field, along with my fields from a table: Last Name, Address, City, State, Zip etc...

In a query I placed all the person info fields above and the ID field (autonumber).

I placed a parameter prompt of [Enter Last Name] under the last name field, and <=[Enter Qty of Labels] prompt under the ID field.

I created a report based on this query.

It would give me however many labels indicated by the user entry into the prompt boxes for one individual person.

If I entered "Smith" in the first box and 18 in the second, it would give me 18 labels for Smith.

I also had a very long piece of code that would do the same thing (still do), and a shorter piece of code as given below:

Code: Select all

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static lngPreviousID As Long
Static lngCurrentCount As Long
If Me.ID = lngPreviousID Then
lngCurrentCount = lngCurrentCount + 1
If lngCurrentCount < Me.NumberOfCopies Then
Me.NextRecord = False
End If
Else
lngCurrentCount = 1
lngPreviousID = Me.ID
If Me.NumberOfCopies > 1 Then
Me.NextRecord = False
End If
End If
End Sub

but with 2007 I am learning that I can't get the same result I used to as this approach fails in the new Report view in Access 2007 since the events of the sections do not fire any longer. I can see the labels in Report view...in one long line, but when I try to print, or go to print preview it will not print, and will not go to print preview...only back to design view with the following graphical error I am enclosing. Any work arounds without having to use a ton of code? I really liked the ease of the dummy table + query combo to do this in previous versions.

[HansV]

What is NumberOfCopies and how is it populated?

[teachesms]

The NumberOfCopies just came in an old piece of code I picked up somewhere (I'm no programmer...scraper yes, programmer no) :)

I am placing that code in the "On Print" event of the report labels "detail" section.

It is populated by the query based on the: dummy table with two fields "countid" and "qty"

along with another tbl: employees, with the personal fields of Last Name, Address, etc...
The code is one I picked up today, and am assuming the code is the problem...actually my original method of using the dummy table with parameter prompt query used to do the trick...I am just trying pieces of code to see if it will make things work. It is not of course...

My original questions needs clarification. Why doesn't it work anymore in 2007 using the simple parameter prompt and a dummy table as the source?

[HansV]

For the code to work, the report should contain a text box NumberOfCopies that is somehow populated with the number of copies that you want. My guess would be that the report doesn't contain a text box named NumberOfCopies, and this causes the error message.

If you use the query as record source for the report, you shouldn't need any code in the On Print event of the Detail section.

[teachesms]

If you use the query as record source for the report, you shouldn't need
any code in the On Print event of the Detail section."

Hans, that is my original question...I got really wired on this one. Sorry.

Why does it not work as it used to in Access 2003 with just the query as the record source for the label report. I know I should not need one speck of code for this, and never used to...however, it won't work in Access 2007. Keeps coming back with the error as shown below. I can see the Labels in Access 2007 Report View. I can't see them in Print Preview...nor will they print! just keeps giving me the following error and slamming me back to design view of the label.

[HansV]

If you open the report in design view, select the Detail section and look at the Event tab of the Properties pane, what do you see in the On Print event?

[teachesms]

Sorry...

I used the CountID field when it was the QTY field I should have been using in the query. It's Monday isn't it?

Sorry to waste your time...but at least I figured it out and it won't drive me crazy any more...

PS...the only thing is that is NOT what I used to do. I always added the autonumber field CountID. Why it works with the QTY field now is beyond me...it just does. (I'm looking at my book so know what it used to use as query fields, and what it uses now in order to work). Why the flux in what fields it needs to make it work is beyond me...Just glad it does.

Thanks Hans