Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Export form information to Avery Label 8162 with VBA

    I have to be honest, I have no idea how i'd do this with vba or even a macro and i'll be as detailed as I can with my question.

    I have a from called frmProject that has a combo box called cboToItem and three text boxes set up like this:

    [ToDescription] default value = [cboFromItem].[column](1)
    [ToSize] default value = [cboFromItem].[column](2)
    [ToBPC] default Value = [cboFromItem].[column](3)

    I need to put this information on Avery Mailing Label 8162. Right now i use good old ctrl C and ctrl V. Ideally i'd like to load the labels in the printer and hit my cmdprint button.

    I'd like to set up a cmdbutton called cmdPrint that sends the information so it looks like this:

    ToDescription
    cboItemNumber
    ToBPC - ToSize
    and have it center the text with what I believe would be Range.ParagraphFormat.Alignment = wdAlignParagraphCenter

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so set up a report
    specify the label you are using
    then run the report
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Okay i used the label report tool thats in access and set it up. I created a cmdbutton to open the report so I can see it before it runs.

    I put this code on the button
    DoCmd.OpenReport "rptWorkOrder", , , "ProjectID = " & Me!ProjectID
    and it works but it only prints one out of 14 labels on the sheet. I need it to fill in the data for all labels.

    I then tried this:
    DoCmd.OpenReport "rptProjectLabels", acViewReport, "projectid = " & Me!ProjectID
    First to see if it would repeat the data and it doesn't repeat the same data but instead show the data for each record.

    Any ideas?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The report will print the number of labels the underlying query requires
    All you need to do is work out your query that generates the right number of labels
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    The report will print the number of labels the underlying query requires
    All you need to do is work out your query that generates the right number of labels
    Understood but how do i force my query to repeat the data 14 times?

    I could do an open join with a table that has 14 records on it but that seems like a backwards way to do it.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are the person with the requirement to print 14 labels
    whether your current solution seems backwards or not is largely irrelevant assuming it gives you the results you need

    FWIW I would probably join to a numbers table containing 1 to whatever i thought was the upper limit required (and some). then extract the number of labels required from that table and join to the data table

    failing that you could try a stored procedure
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    you are the person with the requirement to print 14 labels
    whether your current solution seems backwards or not is largely irrelevant assuming it gives you the results you need

    FWIW I would probably join to a numbers table containing 1 to whatever i thought was the upper limit required (and some). then extract the number of labels required from that table and join to the data table

    failing that you could try a stored procedure
    So much truth to your statement. Made a outter join table with 1-14 and now i get 14 records on my sheet. I click print it shows me the report and then i select how many copies I need. Much more efficient than what it was. Thank you healdem for thinking through another solution for me.

Posting Permissions

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