Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Labels

  1. #1
    Join Date
    Oct 2010
    Posts
    175

    Unanswered: Labels

    Okay...so I have a form called "Main Form" and on this form I have a subform with a combo box that allows the user to select which programs each individual participates in.

    I also have a form in which I have a separate combo box, not linked to anything, that just lists "Program ID" and "Program Name".

    What I want is to create an event after update that the user can pull address labels based on whatever selection they make...

    For example: When I click on the name of the program, it automatically pops up an Avery 5160 label sheet with the names and addresses of those who participate in the program.

    I should also add that the subform on my "Main Form" is linked to a transaction table.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Doing labels is relatively easy (once you know how). Create a new report and make the Detail section as large as one label. Now add the information you want to see on the one label, like you would with any other report. Now for the trick, go into Page Setup and go to the Columns tab. In there you can define how many columns you need (for Avery 5160 you will need three). Then set the Column Size to Same As Detail and the Column Spacing to about an eighth of an inch. The Column Layout you can decide for yourself which works best.

    You will also want to change the Top and Bottom margins to .5 and the Right and Left to .25.

    The real trick comes when you want to use partial pages of labels. That is more complicated.

  3. #3
    Join Date
    Oct 2010
    Posts
    175
    It didn't work for me. When I followed the steps you gave me, and then looked at my labels in Report View, it only showed one label and my info was not shown.

    I know how to use the Label Wizard to create labels. But I don't know how to pull up address labels using the combo box I created for only those individuals associated with a specific program.

    For example, say I wanted to send a mailing to only the individuals in a certain program...I want to be able to click on my combo box selection (which is by name of program) and have it automatically pull the Avery address labels for just those people.
    Last edited by akanick; 11-05-10 at 12:47.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Open the report using a filter. There is a WhereCondition parameter in the DoCmd.OpenReport that will filter the report for the matching data. If you are familiar with SQL then it is basically everything after WHERE in an SQL statement.

  5. #5
    Join Date
    Oct 2010
    Posts
    175
    Okay, how do I go about opening the report as a filter? I'm not terribly familiar with SQL.

    In the last database that was made for my agency, the creator used VB code that follows:
    Private Sub Command40_Click()
    On Error GoTo Err_Command40_Click

    Dim stDocName As String

    stDocName = "Lables by Last Name"
    DoCmd.OpenReport stDocName, acPreview

    Exit_Command40_Click:
    Exit Sub

    Err_Command40_Click:
    MsgBox Err.Description
    Resume Exit_Command40_Click

    End Sub

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    All you will have to do is add a little more code to get it to work.

    Assumptions:
    1. Your combo box is named cboProgram
    2. The Program ID is stored in the ProgramID field
    3. Program ID is the bound column for the combo box

    Change:

    DoCmd.OpenReport stDocName, acPreview

    to

    DoCmd.OpenReport stDocName, acPreview,,"ProgramID=" & cboProgramID

    You will want to either correct my assumptions or substitute the actual names into the new code.

  7. #7
    Join Date
    Oct 2010
    Posts
    175
    Assumptions:
    1. Your combo box is named cboProgram~(The name is "ProgramComboBox")
    2. The Program ID is stored in the ProgramID field~Yes
    3. Program ID is the bound column for the combo box~Unknown...Row source type=Query and Row Source=SQL Statement with Program ID, Name of Program, and Initial Date of Participation

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What do you have for the Bound Column property? 1? If so, try this:

    DoCmd.OpenReport stDocName, acPreview,,"[Program ID]=" & ProgramComboBox

  9. #9
    Join Date
    Oct 2010
    Posts
    175
    I'm now getting a "Compile Error" "Syntax Error"

    Here's what I have:

    DoCmd.OpenReport All labels, acPreview,,"[Program ID]=" & ProgramComboBox

  10. #10
    Join Date
    Oct 2010
    Posts
    175
    Now I'm getting

    RunTime Error 2497: The action or method requires a report name argument

  11. #11
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The name of the report needs to be in quotes:

    DoCmd.OpenReport "All labels", acPreview,,"[Program ID]=" & ProgramComboBox

  12. #12
    Join Date
    Oct 2010
    Posts
    175
    I'm getting a message that says "some data may not be displayed. There is not enough horizontal space on the page for the number of columns and column spacing you specified. Click page setup on the file menu, click the columns tab, and then reduce the number of columns or the size of the columns.

    Then a dialog box opens up asking me to enter parameter value program id.

    If I click ok to bypass that, the "all labels" report shows with "error" "error"

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Your report is too wide. Add up the Right and Left margins, the column width times the number of columns and any column spacing you have. Together the total needs to be less than the page width (8.5"?).

    If you are being asked for the Program ID parameter then Program ID is either spelled differently or is not part of the data that feeds the report.

  14. #14
    Join Date
    Oct 2010
    Posts
    175
    I corrected the report size. That error message is gone now.

    You are right. "Program ID" is not part of the data that feeds the report.

    I guess maybe I'm confused as to how I should link the combobox selection and the report. Might you know how I should integrate the "Program ID"?

  15. #15
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What is feeding the report? A query? A table? If it is a query, can Program ID be added to the query? If not, can you show the SQL? You can switch to SQL view when you desing the query to get the SQL code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •