Results 1 to 7 of 7
  1. #1
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49

    Custom label print report

    Hello again, I would like your help with creating a report for label printing.

    At work we don't use a label printer but a sheet of 4*14 labels. What I'm trying to do is to set up a way where the user can chose what kind of labels he wants to print, for what person(s), and in what position(s).
    We use three different types of labels (in terms of content) and we usually have to print some of each type for a number of persons.

    I have managed to create a form where the user can select the desired positions on the label sheet, where individually he can choose person and label type. All this info is stored in a table which is the base of a query (qry_LabelPrint) that results in the following data:

    Lbl_position Lbl_type Pt_ID Pt_LastName Pt_FirstName Pt_FatherName BirthDate SocSecNo
    1 2
    2 3
    .....
    56 1

    The label position is defined by the Lbl_position value (1 to 4*14=56), the label type by the Lbl_type value (1 to 3) and the rest of the data will be conditionally displayed according to the type of label.

    My problem is with creating the report:

    I will design 56 sets of 5 text boxes that will hold the data for each label, display those that correspond to the given label type, discard empty ones e.t.c.


    But what is the most efficient way to draw the data from my query? If I use Dlookup for each text box, it will result in 56*5 = 280 calls, I believe that this will be very slow. Should I somehow use a recordset? If yes, could you provide a small example?

    Thank you, I'm a bit lost here

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a table with the 56 rows. never add nor delete records to the table.
    make an update query to clear all the fields except the 1st col. Position....quClearAllLblFlds

    in a form, put in boxes, txtPosition, txtLast, txtFirst, etc...

    run for 1 lable:
    run an update query, quSet1Lbl, to update the position
    update table set Lastname = forms!myform!txtLast, firstName = forms!myFormtxtFirst .... where [position]= forms!myForm!txtPosition

    or user can make a spread sheet to update many
    build a query that updates the table from the linked sheet.

  3. #3
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Thank you Ranman, I decided to go this way (after some more searching and a lot of trial and error):

    Code:
    Dim rs1 As DAO.Recordset
    Dim i As Integer
    
    Private Sub Report_Load()
    
        Set rs1 = CurrentDb.OpenRecordset("qry_LabelPrint", dbOpenSnapshot)
        i = 1
        rs1.MoveFirst
        For i = 1 To 56 'For each record that corresponds to a label position
            'The names of the controls have a number that matches the label position and take the relevant value
            Me.Controls("txt_BirthDate_" & i).Value = Format(rs1!BirthDate, "dd/mm/yy")
            Me.Controls("txt_SocSecNo_" & i).Value = rs1!SocSecNo
            Me.Controls("txt_Date_" & i).Value = Format(rs1!Label_Date, "dd/mm/yy")
            Me.Controls("txt_FatherName_" & i).Value = rs1!Pt_FatherName
            Me.Controls("txt_FirstName_" & i).Value = rs1!Pt_FirstName
            Me.Controls("txt_FullName_" & i).Value = rs1!Pt_LastName & " " & rs1!Pt_FirstName
            Me.Controls("txt_LastName_" & i).Value = rs1!Pt_LastName
    
            'According to the label type, some fields are visible and others invisible
            Select Case rs1!Lbl_type
                Case 1
                    Me.Controls("txt_LastName_" & i).Visible = True
                    Me.Controls("txt_FirstName_" & i).Visible = True
                    Me.Controls("Image_" & i).Visible = True
                    Me.Controls("txt_BirthDate_" & i).Visible = False
                    Me.Controls("txt_SocSecNo_" & i).Visible = False
                    Me.Controls("txt_FatherName_" & i).Visible = False
                    Me.Controls("txt_FullName_" & i).Visible = False
                    Me.Controls("txt_Date_" & i).Visible = False
                Case 2
                    Me.Controls("txt_LastName_" & i).Visible = False
                    Me.Controls("txt_FirstName_" & i).Visible = False
                    Me.Controls("Image_" & i).Visible = False
                    Me.Controls("txt_BirthDate_" & i).Visible = True
                    Me.Controls("txt_SocSecNo_" & i).Visible = True
                    Me.Controls("txt_FatherName_" & i).Visible = True
                    Me.Controls("txt_FullName_" & i).Visible = True
                    Me.Controls("txt_Date_" & i).Visible = True
                Case 2
                    Me.Controls("txt_LastName_" & i).Visible = True
                    Me.Controls("txt_FirstName_" & i).Visible = True
                    Me.Controls("Image_" & i).Visible = True
                    Me.Controls("txt_BirthDate_" & i).Visible = False
                    Me.Controls("txt_SocSecNo_" & i).Visible = False
                    Me.Controls("txt_FatherName_" & i).Visible = False
                    Me.Controls("txt_FullName_" & i).Visible = False
                    Me.Controls("txt_Date_" & i).Visible = True
            End Select
    
        rs1.MoveNext
        Next
        Set rs1 = Nothing
    This does exactly what I want, the only problem is that I have to create all 56 text field sets (7 for each label) manually, that's A LOT of time... unless anybody knows a way to automate this!

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Lets see if I understand correctly.

    You have a form that you select 1 to 56 people for printing info. The form looks like:
    Lbl_position
    Lbl_type
    Pt_ID
    Pt_LastName
    Pt_FirstName
    Pt_FatherName
    BirthDate
    SocSecNo
    1 2
    2 3
    ....
    56 1


    Quote Originally Posted by jabarlee View Post
    I have to create all 56 text field sets (7 for each label) manually, that's A LOT of time... unless anybody knows a way to automate this!
    I think you are saying that you have to manually type in the data for Pt_ID, Pt_LastName, Pt_FirstName, Pt_FatherName, BirthDate & SocSecNo for each of the 56 rows..
    Each Pt has a unique PT_ID.. yes? You could nave a combo box (cboPTInfo) to select the Pt_ID.
    The combo box row source could be something like
    Code:
    SELECT Pt_ID, Pt_ID & " - " & Pt_LastName & ", " & Pt_FirstName as IDName, Pt_LastName, Pt_FirstName, Pt_FatherName, BirthDate, SocSecNo 
    FROM tblPatientInfo 
    ORDER BY Pt_LastName, Pt_FirstName
    In the combo box, the settings would be
    Bound Column = 1
    Column Count = 7
    Column widths = 0, 2, 0, 0, 0, 0, 0

    There would be code in the combo box after update event to put the values into the proper controls.
    So you select the patient by Pt_ID and Name, and the data gets entered into the proper controls.



    -----------------------------------------------------------------------------------------
    BTW, there is an error in your code. (You have two Case 2 cases)
    Code:
            Select Case rs1!Lbl_type
                Case 1
                    Me.Controls("txt_LastName_" & i).Visible = True
                    Me.Controls("txt_FirstName_" & i).Visible = True
                    Me.Controls("Image_" & i).Visible = True
                    Me.Controls("txt_BirthDate_" & i).Visible = False
                    Me.Controls("txt_SocSecNo_" & i).Visible = False
                    Me.Controls("txt_FatherName_" & i).Visible = False
                    Me.Controls("txt_FullName_" & i).Visible = False
                    Me.Controls("txt_Date_" & i).Visible = False
                Case 2
                    Me.Controls("txt_LastName_" & i).Visible = False
                    Me.Controls("txt_FirstName_" & i).Visible = False
                    Me.Controls("Image_" & i).Visible = False
                    Me.Controls("txt_BirthDate_" & i).Visible = True
                    Me.Controls("txt_SocSecNo_" & i).Visible = True
                    Me.Controls("txt_FatherName_" & i).Visible = True
                    Me.Controls("txt_FullName_" & i).Visible = True
                    Me.Controls("txt_Date_" & i).Visible = True
                Case 2     '<------ Shouldn't this be 3 ??? 
                    Me.Controls("txt_LastName_" & i).Visible = True
                    Me.Controls("txt_FirstName_" & i).Visible = True
                    Me.Controls("Image_" & i).Visible = True
                    Me.Controls("txt_BirthDate_" & i).Visible = False
                    Me.Controls("txt_SocSecNo_" & i).Visible = False
                    Me.Controls("txt_FatherName_" & i).Visible = False
                    Me.Controls("txt_FullName_" & i).Visible = False
                    Me.Controls("txt_Date_" & i).Visible = True
            End Select
    Also you need to close the recordset before setting it to nothing:
    Code:
            rs1.MoveNext
        Next
    
        rs1.Close     '<<-- Add this line
        Set rs1 = Nothing
    End Sub


    Since the formatting for Case 1 and 3 are the same except for Me.Controls("txt_Date_" & i).Visible, you could use
    Code:
            Select Case rs1!Lbl_type
                Case 1, 3
                    Me.Controls("txt_LastName_" & i).Visible = True
                    Me.Controls("txt_FirstName_" & i).Visible = True
                    Me.Controls("Image_" & i).Visible = True
                    Me.Controls("txt_BirthDate_" & i).Visible = False
                    Me.Controls("txt_SocSecNo_" & i).Visible = False
                    Me.Controls("txt_FatherName_" & i).Visible = False
                    Me.Controls("txt_FullName_" & i).Visible = False
                    Me.Controls("txt_Date_" & i).Visible = (rs1!Lbl_type = 3)
                Case 2
                    Me.Controls("txt_LastName_" & i).Visible = False
                    Me.Controls("txt_FirstName_" & i).Visible = False
                    Me.Controls("Image_" & i).Visible = False
                    Me.Controls("txt_BirthDate_" & i).Visible = True
                    Me.Controls("txt_SocSecNo_" & i).Visible = True
                    Me.Controls("txt_FatherName_" & i).Visible = True
                    Me.Controls("txt_FullName_" & i).Visible = True
                    Me.Controls("txt_Date_" & i).Visible = True
            End Select

  5. #5
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Steve,
    thank you very much for your input.
    You're right about Case 2, it is just a typo, I had already corrected it and I also had forgotten to close the recordset, thanx for pointing that out.
    Also, I did not know about the Case 1,3 formatting you wrote and, to be honest, I 'm not sure that I understand how it works...Is it that the rs1!Lbl_type = 3 statementis true in Case = 3, so it can replace the actual TRUE value? This is brilliant!

    As for the controls (text fields) I need to create:
    The form where the user selects what and where to print, is ready with all its 56 sets of controls (each set consists of a combobox to select patient and an option group with bullets to select label type).

    My problem is with my printing report:
    Each label has a control set that consists of the 8 fields you see in the code (7 text fields and 1 image). The name of each control ends in a sequential number from 1 to 56 for the for...next loop to work.
    I have created manualy the controls for 3 labels for testing purposes, but to create another 53*8 controls, where I have to rename each of them is very time consuming and mainly, boring.
    So I wonder if there is an automated way to create the new controls with the naming sequence I need.

    Thank you again, Manolis

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I did not know about the Case 1,3 formatting you wrote
    Open the IDE, then HELP. Search for CASE. This will explain the syntax........
    Help says
    "You can use multiple expressions or ranges in each Case clause. For example, the following line is valid:
    Code:
     
    Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber"
    Is it that the rs1!Lbl_type = 3 statementis true in Case = 3, so it can replace the actual TRUE value?
    Yes. If the expression evaluates to TRUE/FALSE, the control will show or be hidden.


    So I wonder if there is an automated way to create the new controls with the naming sequence I need.
    I don't understand why you would need to re-create/rename the controls.


    I have created manualy the controls for 3 labels for testing purposes, but to create another 53*8 controls, where I have to rename each of them is very time consuming and mainly, boring.
    You should only have to do this once.



    Would you post your test (3 records) dB?

  7. #7
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    I will try to post a stripped down / modified version, mainly because labels and names are in Greek , you wouldn't understand anything

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using a Form to Print out a label report
    By kdtrimble in forum Forms
    Replies: 52
    Last Post: 07-22-2015, 11:17 AM
  2. Cannot get label to print out correctly
    By kdtrimble in forum Access
    Replies: 3
    Last Post: 06-26-2015, 09:19 AM
  3. Replies: 2
    Last Post: 02-07-2015, 04:30 PM
  4. Replies: 14
    Last Post: 01-08-2015, 04:50 PM
  5. Print single label
    By Voodeux2014 in forum Access
    Replies: 2
    Last Post: 11-12-2014, 01:52 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