Results 1 to 11 of 11
  1. #1
    Join Date
    May 2009
    Posts
    20

    Unanswered: Print report copies based on criteria in a query

    I have a query that pulls out line items on a Sales Order.

    My user then types in how labels she want to print for each line item.

    SELECT dbo_SO_Detail.ORDNUM_28, dbo_SO_Detail.LINNUM_28, dbo_SO_Detail.DELNUM_28, dbo_Part_Master.PMDES1_01, dbo_SO_Detail.FILL04_28 AS Qty
    FROM dbo_SO_Master INNER JOIN (dbo_SO_Detail INNER JOIN dbo_Part_Master ON dbo_SO_Detail.PRTNUM_28 = dbo_Part_Master.PRTNUM_01) ON dbo_SO_Master.ORDNUM_27 = dbo_SO_Detail.ORDNUM_28
    WHERE (((dbo_SO_Detail.ORDNUM_28)=[Forms]![frmPrintLabels]![txtSalesOrder]));

    The quantity of labels goes in the dbo_SO_Detail.FILL04_28 field which is just a user defined text field in my MRP system.

    The problem is labels need to say: box 1 of 17, 2 of 17, 3 of 17 with each label being different.

    To get this to work, I was thinking that I'd write a separate record to a new table for each label to be printed.

    And then write them to another table with the label number on them like this:

    Box: IIf(CInt(DCount("UniqueField","tblNewLabels_Series _Work","UniqueField <='" & [UniqueField] & "'"))<1000,CInt(DCount("UniqueField","tblNewLabels _Series_Work","UniqueField <='" & [UniqueField] & "'")),1000)

    (UniqueField is just an autonumber converted to a text field to get the DCount to work.)

    The question is: How do I tell the system to write X number of identical records to a table based on the number in the dbo_SO_Detail.FILL04_28 field?

  2. #2
    Join Date
    May 2009
    Posts
    20
    I found this searching through the forum:

    Dim Counter, MyValue
    MyValue = [SomeTextBox]
    Counter = 0 ' Initialize variables.
    Do While Counter < MyValue ' Inner loop.
    Counter = Counter + 1 ' Increment Counter.
    docmd.openquery(your append query goes here)
    Loop

    How do I write it so that
    MyValue = tblNewLabels.Qty
    My append query: appendNewLabels_Series
    ?

    Starting table:
    Part Number Qty
    101263 5
    101264 4
    101265 6


    Desired Results
    Part Number Qty Label Number
    101263 5 1
    101263 5 2
    101263 5 3
    101263 5 4
    101263 5 5
    101264 4 1
    101264 4 2
    101264 4 3
    101264 4 4
    101265 6 1
    101265 6 2
    101265 6 3
    101265 6 4
    101265 6 5
    101265 6 6

  3. #3
    Join Date
    May 2009
    Posts
    20

    Maybe I'm just thinking out loud here...

    So I go this to work (sort of):

    Private Sub Print_Label_Click()

    Dim stDocName As String
    Dim aVariable As Integer
    aVariable = DFirst("[Qty]", "[qryLabelQuantities]")

    MyValue = aVariable
    Counter = 0 ' Initialize variables.
    Do While Counter < aVariable ' Inner loop.
    Counter = Counter + 1 ' Increment Counter.
    DoCmd.OpenQuery ("appendNewLabels_Series")

    Loop

    End Sub

    But instead of it giving me 5 records for the first line, 4 records for the second line, and 6 records for the third line, it gave me 5 records for each of the records in my original table.

    How do I get this code to look at one record at a time?

    Lucy

  4. #4
    Join Date
    May 2009
    Posts
    20
    K, so now I'm trying to just isolate row one (which seems to be what DFirst is doing), tell the code to run the append query 5 times on row one only, then run an update query to check a box on row one in the original query to say that its had its labels created and can be ignored now and the code should move on to row two. Is this making any sense?

  5. #5
    Join Date
    May 2009
    Posts
    20
    K so I changed my append query to look for the minimum line number with the Printed field set to 0. The code runs and loops through 6 times and appends row one to another table. Then it sets the Printed field to 1. Now my append query sees line two as the minimum line number with the Printed field set to 0. So now I just need to be able to tell my code to keep looping through until all of my Print fields are set to 1.

  6. #6
    Join Date
    May 2009
    Posts
    20
    k, so now it's working for the first record and I need to nest a loop inside a loop.

    Here is the inner loop:

    Private Sub Print_Label_Click()

    Dim stDocName As String

    stDocName = "macroPrintLabels"
    DoCmd****nMacro stDocName

    Dim aVariable As Integer
    aVariable = DFirst("[Qty]", "[qryNewLabels_NextRecord]")

    MyValue = aVariable
    Counter = 0 ' Initialize variables.
    Do While Counter < aVariable ' Inner loop.
    Counter = Counter + 1 ' Increment Counter.
    DoCmd.OpenQuery ("appendNewLabels_Series")

    Loop

    stDocName = "macroUpdatePrintFile"
    DoCmd****nMacro stDocName

    End Sub

    This loop created 5 records for line one, numbered them one through five, put them in a table for the label report to pull from and then went back and updated the record for line one to say it had been printed.


    This loop needs to get nested inside a loop that says repeat for the number of records I have in the first table I made based on the query from the MRP system with one record per sales order line.

    SELECT Count(tblNewLabels.LINNUM_28) AS CountOfLINNUM_28
    FROM tblNewLabels;

    so if there are three records, it needs to run the above routine three times.

    (So close but so far away!)

  7. #7
    Join Date
    May 2010
    Posts
    601
    I have an example that shows a simple way to handle this.

    See: Print Multiple Labels (Click Here)
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    May 2009
    Posts
    20
    Dude, you just took my 14 steps down to 2. Kinda took the wind out of my sails; I was having fun figuring out the loop within a loop.

    All I had to do was change my Qty field from a text field to a number and your method worked beautifully. Simpler is always better. Thanks much!

    Lucy

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by lucy68 View Post
    Dude, you just took my 14 steps down to 2. Kinda took the wind out of my sails; I was having fun figuring out the loop within a loop.

    All I had to do was change my Qty field from a text field to a number and your method worked beautifully. Simpler is always better. Thanks much!

    Lucy

    Lucy,

    You're welcome.

    Glad that worked for you.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    Jun 2011
    Posts
    1

    Perfect!

    I have been trying to do exactly this for the whole morning! Thank you for your simple and wonderful solution!

  11. #11
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by TheCrocc View Post
    I have been trying to do exactly this for the whole morning! Thank you for your simple and wonderful solution!
    You're welcome.

    Glad you found it helpful.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Tags for this Thread

Posting Permissions

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