I'm pretty much a novice when it comes to VB coding in access, so I've been trying to find some code that is already written that can do what I need. Basically, what I'd like to do is just be able to print return labels onto Avery sheets, so I'd like to define what lable position to start with, and how many return labels to print. I found this very good article at "support microsoft com/kb/231801" (had to remove the dots between the words) that seemed like it should do exactly what I want, so I attempted to follow the instructions, but when I get to part where I'm putting the function calls into my report, such as "=LabelLayout(Reports![MyLabels])", it just comes out in red meaning it's not a valid call. I realize this article was published for Access 2000 and I'm using 2007, so is there something I need to modify to get it to work? Any help would be MUCH appreciated. Thanks!

Hi,



In Step 1, when you created your module, what name did you give it?

Just my 2 cents...

mikeb2,



I have a co-worker who hated to waste Avery labels, so he'd print four rows of address labels, and then a few days later, print three more rows, re-feeding the original label sheet with stickers removed.

Eventually, the greasy release agent that's on the wax paper got all over the parts of our copier that are meant to draw paper in via friction.

The repair costs were a lot higher than if he'd have just used a new sheet of labels for each print job.

On the other hand, if this isn't a common occurrence, you'll probably be fine. Just thought it worth mentioning, though.

Dennis

It just defaulted to Module1...does that matter?

Thanks and that makes sense, but i'll be printing a pretty good amount each time, so i only anticapate putting a used sheet in at most once.

A common mistake is to name the code module the exact same name as the function. If you saved the code module as "Module1", you should be fine.

What is the name of your report (as it appears in the list of Access Objects)?

Dennis

Just like it asked for in the article, I named the report "MyLabels".

Mike,

Well, so far it sounds okay. Can you copy/paste the contents of the report's code module? It should include three fairly short sections.

After you paste them into a message, highlight them and click the "Wrap in Code Tags" button (the far right one) just above the white text area.

Dennis

Sure, here you go...

In the Report On Open Event I've got



As mentioned, the original article asked for , but when I entered that, it was immediately highlighted in red and wouldn't run.

Then in the Report Header On Format Event I've got



Same comment about the function call, where I had to change the "=" to "Call"

Finally in Report Detail On Print Event I've got



And again, same comment about the function call. Thanks!

Mike,

Aha! That's what I thought. There's our problem. You're misunderstanding what the article wants you to do.

There should be NO VBA code for the report's code module. Instead, you need to type "=LabelSetup()" into the white area of the Property Sheet, next to the words "On Open". Don't press the little button with three dots to create an On Open event in the Visual Basic Editor. Actually type (or Paste) the above phrase into the white area next to "On Open" in the Property Sheet.

So, delete all of the VBA from the report's code module, and try again, but this time just focus on the Property Sheet.

Hope this helps,

Dennis

It kind of did...I deleted the VBA code, and inserted the function calls as you suggested in the property sheet, however I was still having the same problem in that I'd get many more labels that asked for, for example, I'd say skip 3 labels and print 5 labels and it would skip the 3 spaces but end up printing like 35 labels. I thought the problem had something to do with the fact that I changed the "=" to a "Call", so I was stuck on that, but when I made your suggested change, I figured it had to be something else, so I looked at the record source for the report and for some reason it was listing Switchboard?? I'm thinking now that when I used the create Labels wizard, the Switchboard was up, and so it got sucked in as the data source. Once I deleted that, everything worked as advertised, but long story short, I probably wouldn't have thought of looking there since I kept thinking my probllem was related to my switching the function calls to get them to work. But all is now good, and thanks so much for the help!!

Mike,

Yeah, those demos/tutorials sometimes assume you know more about Access than they should.

Glad you got it working!

Dennis