Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    Robyn_P's Avatar
    Robyn_P is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Nov 2017
    Posts
    150
    This should read "If nbrecords Mod labelsPerSheet 0" Then

  2. #17
    mize91 is offline Novice
    Windows XP Access 2016
    Join Date
    Aug 2018
    Posts
    4
    Quote Originally Posted by Robyn_P View Post
    Still not ideal formatting, phone doesn't believe in code tags or leading spaces, but better. As said, not tested and you'll need to do a little thinking about the required temporary records and making sure you delete them at the end. Code above certainly won't work due to missing edit/update steps. But off the top of my head I think that should do it for you. Right I'm off. Kitty needs to go to the vet and this really isn't what I am paid to do (fun though!)

    I haven't worked with VBA code. I attached a sample of a database that has 15 records.
    What code would be in order of stack 3-up ( yes, you are correct I want to stack each pile to be back in order of 1,2,3 )?

    Thanks

    Click image for larger version. 

Name:	database for 3-up order.PNG 
Views:	37 
Size:	33.3 KB 
ID:	35060

  3. #18
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Would be more helpful to attach actual db or Excel or build table in the post. But I made my own table and records to test.

    The example PDF shows 4 labels per sheet, not 3.

    Why is label identified as C#1? Is that the ListOrder field?

    Here is a tested procedure (thanks to Robyn for the ideas). Table has 3 fields - ListOrder, Address, PrintOrder. The ListOrder can be an autonumber field or your ListOrder as shown in image. PrintOrder is populated by code. Report has Sorting set on PrintOrder. It works for any number of labels per page without having to create dummy records.

    Code:
    Sub SetPrintOrder()
    Dim rs As DAO.Recordset
    Dim labelsPerSheet As Integer, countA As Integer, countRecs As Integer, countPages As Integer, x As Integer, z As Integer
    ' Get all labels
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Labels ORDER BY ListOrder")
    labelsPerSheet = 3
    rs.MoveFirst
    countRecs = rs.recordCount
    countPages = Int(countRecs / labelsPerSheet) + IIf(countRecs Mod labelsPerSheet = 0, 0, 1)
    ' Loop through the recordset populating the PrintOrder field
    For x = 1 To labelsPerSheet
        countA = x
        For z = 1 To countPages
            If Not rs.EOF Then
                If countA <= countRecs Then
                    rs.Edit
                    rs!PrintOrder = countA
                    rs.Update
                    rs.MoveNext
                    countA = countA + labelsPerSheet
                End If
            End If
        Next
    Next
    rs.Close
    Set rs = Nothing
    End Sub
    Using the actual data table will cause conflicts if this is a multi-user db. If this is a multi-user db, should split the db and use a temp table in frontend to temporarily save records for this report output. Code would have to be modified to either open a second recordset on the temp table and add records to it or run INSERT sql action.
    Last edited by June7; 08-10-2018 at 07:22 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Update: just revisited this and find code is missing a line, although it must have worked at some point without.

    Code:
    ...
    rs.MoveLast
    rs.MoveFirst
    ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Printing labels from order data in Access ERP
    By beachbumch in forum Programming
    Replies: 2
    Last Post: 11-30-2016, 03:05 PM
  2. Code for Printing Labels
    By bezoomnyveshch in forum Access
    Replies: 1
    Last Post: 12-15-2015, 01:21 PM
  3. Replies: 6
    Last Post: 07-28-2014, 01:04 PM
  4. Replies: 1
    Last Post: 05-04-2012, 02:22 PM
  5. Printing Labels.
    By Robeen in forum Reports
    Replies: 1
    Last Post: 05-02-2011, 05:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums