Using criteria to choose particular address labels

[Danielle17]

I have a simple database that requires some sort of form that allows the user to choose a certain customer, address(street addr or billing addr), and the type of label...1" or 3 1/3". I've tried to use a suggestion that I received from this same forum. I tired to run the reports off of a query and then put [Forms]![FormName]![txtName] in the criteria expression but it just produces an address label that reads ERROR#. I'm not really sure how to get the report to switch to a certain type of label either. I do have 4 different reports. One 1" label with a street addr and one with a shipping addr and the One 3 1/3" label with the street addr and one with a shipping addr. I'm just not quite sure how to code the command button so that it picks out the criteria in the query and then looks at the option button that was chosen on the form to determine which type of addr label to use. I would appreciate any suggestions.

 

[JoeMiller]

In the query that feeds the reports, you placed the [Forms]![FormName]![txtName] under the Name field in the criteria? If you did when you view the query results you should see the record you're looking for, provided the text in txtName matches a valid Name returned by the query. Once that's working, base your label report on this query and you're all set.

HTH Joe Miller
joe.miller@flotech.net

 

[Danielle17]

If that text box is a combo box does that make a difference?

 

[JoeMiller]

Nope, same difference as long as the Bound column of the combo box (normally column 1) contains the data that will match the Name field in query. If you're matching an ID number to a Name, it obviously won't correspond and you'll get no records.



Joe Miller
joe.miller@flotech.net

 

[Danielle17]

The user is going to have a choice between 4 different reports...they won't know that but by choosing either a street addr label or billing addr label they will be choosing one type of report...now if they decide on a street addr label for a 1" label size.....I can't do this by just putting that [Forms]! statement in the criteria expression right? I'll need to write something with and IF statement....I just figured out how to chose a customer and date with one of my other database(thanks to your help) and I did that will just the criteria statement....I don't think I can do that will this one right?

 

[JoeMiller]

I assume you have a button that will launch your report once the user has made a selection of a name in your combobox. The code behind that command button should decide for the user which report to run for them. Are all the reports based on the same query? Do they always select data from one combobox to pick their record? How does the program know which one to print? If you answer these questions I can most likely help you develop the right code to pick the proper report. So what i'm saying is that the one criteria COULD do it, I don't know if it CAN do it in your situation because there's not enough info. Joe Miller
joe.miller@flotech.net

 

[Danielle17]

What I did was put 2 labels and 4 command buttons on the form. One label lets the user know if it's going to be a address label or a shipping label...then under each one of these labels there are 2 command buttons...one that says ship street and one that says bill street. When they click on one of the command buttons the query finds the customer and the label layout for the right report. This is probably a backwards way to doing it but so far it's working perfect and it looks simple enough for everyone to use. Thanks for your input....That makes two helpful suggestions today

 

[Danielle17]

OH...one more question about label reports....after you've printed out 1 label in the upper left hand corner how do you select the second column-first label, third column-first label and so on down the label sheet??? I've tried page setup....?

 

[JoeMiller]

I don't have an answer for that, but I will tell you as someone who works for an HP dealer that only works on printers that reusing label stock is one of THE WORST things you can do to your printer because the pressure and heat inside a printer will literally squeeze the glue out of the labels through the exposed part of the label sheet. And that glue then sticks on the printer's internal parts. Over time this can lead to damage that is very expensive to repair. This question was asked once before, I don't recall if an answer was found. You could try the keyword search.

HTH Joe Miller
joe.miller@flotech.net

 

[Danielle17]

So it's not good to print off one label and then resuse the same sheet for the other labels? I did not know that. I'll try a keyword search to see if I can find an answer.

 

[Danielle17]

I found the post for the individual labels. It mentioned something about the Access Developer's Handbook....which I do own...I've been looking over the few pages that they have in there on it....It's exactly what I need. It suggests that I copy the form, report and module right into my database...which I've done. There's a section of code that produces an error. I'm not sure what I'm supposed to re-type in the code so that it pertains to my forms and such. Here's a little of the code:

Private Sub Form_Open(Cancel As Integer)

Dim varRows As Variant
Dim varCols As Variant
Dim varStart As Variant
Dim varAcross As Variant

Dim intR As Integer
Dim intC As Integer
Dim varOpen As Variant

' These are normally set by incoming parameters
varOpen = Me.OpenArgs
If Not IsNull(varOpen) Then
varRows = adhGetItem(varOpen, "Rows&quot
varCols = adhGetItem(varOpen, "Cols&quot
varAcross = adhGetItem(varOpen, "Across&quot
varStart = adhGetItem(varOpen, "Start&quot
Else
varRows = Null
varCols = Null
varStart = Null
varAcross = Null
End If

The adhGetItem is giving me an error. The commented out statement mentions that these are filled by parameters....I don't know what to replace these with.......I hope you're still there

 

[JoeMiller]

OpenArgs is a way for you to pass information to a form, report, etc.. The function is inspecting the OpenArgs of the report and it's looking for Rows, Cols, Across, and Start; presumably to tell it where to start putting data in the label grid. Something in the book will tell you how to format the OpenArgs to give the function what it needs.

HTH Joe Miller
joe.miller@flotech.net

 

[Danielle17]

I've re-read the entire section and I've searched the forms, reports, modules and any code that I found in the sample database. I can't seem to find the explanation for the OpenArgs of the report. Do you happen to have the book? X-)

 

[JoeMiller]

I have the 97 version of the book, it's not in the office though. What's the page number for the thing you're looking at, provided you're working with 97 as well.

Joe Miller
joe.miller@flotech.net

 

[Danielle17]

I am also working in 97...pages 556-560...Thanks for hanging in there

 

[JoeMiller]

Ok, did you place the code from the sample report into your report? If not go look at it and you'll need to copy/past it into the proper events of your report. The example is pretty straight forward if you copy the modules and the frmSelectLabel. Then modify your report by adding the code, the report will call the form asking to select the label and then format appropriately.

Joe Miller
joe.miller@flotech.net