Solved Print Labels with additional Values (1 Viewer)

mike60smart

mike60smart

Registered User.
Local time
Today, 03:56
Joined
Aug 6, 2017
Messages
1,913
Hi Everyone

Moke123 gave me a solution to printing a number of Labels for a selected Record in a Continuous Form

Here

I have been asked now to add additional functionality.

The record has additional Controls named as follows:-

BoxNr with a Default value of 1
PackNo with a Default value of 1
LabelNumber with a value of 50050

When I run the Code to generate the Labels in this case 10 Labels

The BoxNr on each Label will run from 1 through to 10
The PackNo on each Label will run from 1 through to 10
The LabelNumber will run from 50050 through to 50059

Is this at all possible?

Any help appreciated.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:56
Joined
Sep 21, 2011
Messages
14,350
mike60smart said:
Hi Everyone

Moke123 gave me a solution to printing a number of Labels for a selected Record in a Continuous Form

Here

I have been asked now to add additional functionality.

The record has additional Controls named as follows:-

BoxNr with a Default value of 1
PackNo with a Default value of 1
LabelNumber with a value of 50050

When I run the Code to generate the Labels in this case 10 Labels

The BoxNr on each Label will run from 1 through to 10
The PackNo on each Label will run from 1 through to 10
The LabelNumber will run from 50050 through to 50059

Is this at all possible?

Any help appreciated.
Click to expand...
Is this at all possible?
Click to expand...

More than likely. :)
I would just add those values to the table, using the loop to increment each as required.
 
mike60smart

mike60smart

Registered User.
Local time
Today, 03:56
Joined
Aug 6, 2017
Messages
1,913
Gasman said:
More than likely. :)
I would just add those values to the table, using the loop to increment each as required.
Click to expand...
Hi Gasman
Added the fields to the table and when the code runs it adds values as shown:-

How would I adjust the code so that the values are entered as follows:-
 

Attachments

  • Labels.PNG
    Labels.PNG
    8.3 KB · Views: 63
  • Labels2.PNG
    Labels2.PNG
    8.4 KB · Views: 60
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:56
Joined
Sep 21, 2011
Messages
14,350
Just set the values as you are currently doing in the loop, only they will come from variables and not the recordset.
You will of course need to create the new fields in the table that holds the new labels data.
 
mike60smart

mike60smart

Registered User.
Local time
Today, 03:56
Joined
Aug 6, 2017
Messages
1,913
Hi Gasman

The current code is as follows:- Note the Code is actually indented on my PC but when I insert the code it aligns everything to the Left???
I put Code Tags in and pasted the actuakl Code between the Tags and this is the result. The site is actually aligning it Left automatically.

Code: 
Private Sub cmdPrint_Click()

10 On Error GoTo cmdPrint_Click_Error
Dim db As DAO.Database
Dim rs As DAO.Recordset
20 Set db = CurrentDb

30 db.Execute "Delete * from tblLabels", dbFailOnError


'Print multiple labels for current record.
Dim i As Integer

40 If IsNull(Me!txtNumberOfLabels) Then
50 MsgBox "Please indicate the number of labels you want to print", vbOKOnly, "Error"
60 DoCmd.GoToControl "txtNumberOfLabels"
70 Exit Sub
80 End If

90 Set rs = db.OpenRecordset("Select * from tblLabels")

100 If Me.txtPack = 1 Then
'Adds duplicate records for selected company using input value.
110 For i = 1 To Me.txtNumberOfLabels.Value
120 With rs
130 .AddNew
140 !BoxNr = Me.BoxNr
150 !OrderItemSizeID = Me.OrderItemSizeID
160 !CROP = Me.txtCrop
170 !Variety = Me.txtVariety
180 !Gen = Me.txtGen
190 !Line = Me.txtLine
200 !WeightKg = Me.txtWeight
210 !GradeSize = Me.txtSize
220 !CustomerDelivery = Me.txtCustomer
230 !PackNo = Me.PackNo
240 !Dessicated = Me.txtDessicated
250 !HarvestedFrom = Me.txtHarvestedFrom
260 !Treatment = Me.txtTreatment
270 !WHP = Me.txtWHP
280 !LabelNo = Me.LabelNumber
290 .Update
300 End With
310 Next i
'Opens report.
320 DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
330 End If

340 If Me.txtPack = 1 Or 3 Or 4 Then
'Adds duplicate records for selected company using input value.
350 For i = 1 To Me.txtNumberOfLabels.Value
360 With rs
370 .AddNew
380 !BoxNr = Me.BoxNr
390 !OrderItemSizeID = Me.OrderItemSizeID
400 !CROP = Me.txtCrop
410 !Variety = Me.txtVariety
420 !Gen = Me.txtGen
430 !Line = Me.txtLine
440 !WeightKg = Me.txtWeight
450 !GradeSize = Me.txtSize
460 !CustomerDelivery = Me.txtCustomer
470 !PackNo = Me.PackNo
480 !Dessicated = Me.txtDessicated
490 !HarvestedFrom = Me.txtHarvestedFrom
500 !Treatment = Me.txtTreatment
510 !WHP = Me.txtWHP
520 !LabelNo = Me.LabelNumber
530 .Update
540 End With
550 Next i
'Opens report.
560 DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
570 End If

580 rs.Close
590 Set rs = Nothing


600 On Error GoTo 0
610 Exit Sub

cmdPrint_Click_Error:

620 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPrint_Click, line " & Erl & "."

End Sub

So the following would be the variables:-

Dim lngBoxNr as Long
Dim lngPackNo as Long
Dim lngLabelNo as Long

Where would I put these ? if they are correct that is?
 
Last edited:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:56
Joined
Sep 21, 2011
Messages
14,350
Note the Code is actually indented on my PC but when I insert the code it aligns everything to the Left???
Click to expand...
That is because you are not using code tags Mike. :mad:
Seriously? :(
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:56
Joined
Sep 21, 2011
Messages
14,350
I have already told you, within the loop.:(

As the first two are going to hold EXACTLY the same value, you only need one variable and set both fields to that.
For the first two you would use the loop index.
The same with labelnumber except you would add it to the starting number for the label.

Why do you think you can set those 3 from the form values? They would be the same for all records?
They were commented out in the other thread and were being set to Country then, if they had not been?

Edit: Start the label number as 1 less than your required value, so the math will be correct.
 
Last edited:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:56
Joined
Sep 21, 2011
Messages
14,350
Gasman said:
Just set the values as you are currently doing in the loop, only they will come from variables and not the recordset.
You will of course need to create the new fields in the table that holds the new labels data.
Click to expand...
Mike, You only need one variable, to hold the labelno as it increments. For the other two, just use the loop index number.
 
mike60smart

mike60smart

Registered User.
Local time
Today, 03:56
Joined
Aug 6, 2017
Messages
1,913
Gasman said:
I have already told you, within the loop.:(

As the first two are going to hold EXACTLY the same value, you only need one variable and set both fields to that.
For the first two you would use the loop index.
The same with labelnumber except you would add it to the starting number for the label.

Why do you think you can set those 3 from the form values? They would be the same for all records?
They were commented out in the other thread and were being set to Country then, if they had not been?

Edit: Start the label number as 1 less than your required value, so the math will be correct.
Click to expand...
Hi Gasman

Is the following Code correct?

I added the Dim line at the beginning and the additional Lines 110,111,112 within the Loop
I will be very surprised if I have done this correctly

Code: 
Private Sub cmdPrint_Click()

10        On Error GoTo cmdPrint_Click_Error

          Dim lngLabelNumber As Long

          Dim db As DAO.Database
          Dim rs As DAO.Recordset
20        Set db = CurrentDb

30        db.Execute "Delete * from tblLabels", dbFailOnError

      
          'Print multiple labels for current record.
          Dim i As Integer
            
40        If IsNull(Me!txtNumberOfLabels) Then
50            MsgBox "Please indicate the number of labels you want to print", vbOKOnly, "Error"
60            DoCmd.GoToControl "txtNumberOfLabels"
70            Exit Sub
80        End If
          
90        Set rs = db.OpenRecordset("Select * from tblLabels")

100       If Me.txtPack = 1 Then
          'Adds duplicate records for selected company using input value.
110       For i = 1 To Me.txtNumberOfLabels.Value
111       For i = 1 To Me.BoxNr
112       For i = 1 To Me.LabelNumber - 1
120           With rs
130               .AddNew
140               !BoxNr = Me.BoxNr
150               !OrderItemSizeID = Me.OrderItemSizeID
160               !CROP = Me.txtCrop
170               !Variety = Me.txtVariety
180               !Gen = Me.txtGen
190               !Line = Me.txtLine
200               !WeightKg = Me.txtWeight
210               !GradeSize = Me.txtSize
220               !CustomerDelivery = Me.txtCustomer
230               !PackNo = Me.PackNo
240               !Dessicated = Me.txtDessicated
250               !HarvestedFrom = Me.txtHarvestedFrom
260               !Treatment = Me.txtTreatment
270               !WHP = Me.txtWHP
280               !LabelNo = Me.LabelNumber
290               .Update
300           End With
310       Next i
          'Opens report.
320       DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
330           End If
 
mike60smart

mike60smart

Registered User.
Local time
Today, 03:56
Joined
Aug 6, 2017
Messages
1,913
Gasman said:
Mike, You only need one variable, to hold the labelno as it increments. For the other two, just use the loop index number.
Click to expand...
Hi Gasman

Had another stab at it but I am fumbling in the dark with this one.

This is current Code is as follows but get the following error:

Code: 
Private Sub cmdPrint_Click()

10        On Error GoTo cmdPrint_Click_Error

          Dim lngLabelNumber As Long

          Dim db As DAO.Database
          Dim rs As DAO.Recordset
20        Set db = CurrentDb

30        db.Execute "Delete * from tblLabels", dbFailOnError

      
          'Print multiple labels for current record.
          Dim i As Integer
          Dim a As Integer
          Dim b As Integer
          Dim c As Integer
          
40        If IsNull(Me!txtNumberOfLabels) Then
50            MsgBox "Please indicate the number of labels you want to print", vbOKOnly, "Error"
60            DoCmd.GoToControl "txtNumberOfLabels"
70            Exit Sub
80        End If
          
90        Set rs = db.OpenRecordset("Select * from tblLabels")

100       If Me.txtPack = 1 Then
          'Adds duplicate records for selected company using input value.
110       For i = 1 To Me.txtNumberOfLabels.Value
120       For a = 1 To Me.BoxNr
130       For b = 1 To Me.PackNo
140       For c = 1 To Me.LabelNumber - 1
150           With rs
160               .AddNew
170               !BoxNr = Me.BoxNr
180               !OrderItemSizeID = Me.OrderItemSizeID
190               !CROP = Me.txtCrop
200               !Variety = Me.txtVariety
210               !Gen = Me.txtGen
220               !Line = Me.txtLine
230               !WeightKg = Me.txtWeight
240               !GradeSize = Me.txtSize
250               !CustomerDelivery = Me.txtCustomer
260               !PackNo = Me.PackNo
270               !Dessicated = Me.txtDessicated
280               !HarvestedFrom = Me.txtHarvestedFrom
290               !Treatment = Me.txtTreatment
300               !WHP = Me.txtWHP
310               !LabelNo = Me.LabelNumber
320               .Update
330           End With
340       Next i
350       Next a
360       Next b
370       Next c

          'Opens report.
380       DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
390           End If
 

Attachments

  • error.PNG
    error.PNG
    36.7 KB · Views: 56
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:56
Joined
Sep 21, 2011
Messages
14,350
No, No, No :(
That code does not even make any sense :(, well not to me at least.
Have you walked through that code to see what it does? Where are you getting these Me.PackNo, Me.BoxNr and Me.LabelNo from?

This is just the minimum I need to write to show you what you should be using.
Code: 
Dim lngLabelNo As Long
......
lngLabelNo = 50049

For i = 1 To Me.txtNumberOfLabels.Value
    With rs
        .AddNew
        !BoxNr = i
        !PackNo = i
        !LabelNo = lngLabelNo + i
....
then the rest of your code
Next
I put Code Tags in and pasted the actuakl Code between the Tags and this is the result. The site is actually aligning it Left automatically.
Click to expand...


You probably copied from the site and not your computer?, you will have lost the indent by then :(
I hate line numbers :)

Code: 
Private Sub cmdPrint_Click()
    On Error GoTo cmdPrint_Click_Error
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    db.Execute "Delete * from tblLabels", dbFailOnError


    'Print multiple labels for current record.
    Dim i As Integer
    If IsNull(Me!txtNumberOfLabels) Then
        MsgBox "Please indicate the number of labels you want to print", vbOKOnly, "Error"
        DoCmd.GoToControl "txtNumberOfLabels"
        Exit Sub
    End If
    Set rs = db.OpenRecordset("Select * from tblLabels")
    If Me.txtPack = 1 Then
        'Adds duplicate records for selected company using input value.
        For i = 1 To Me.txtNumberOfLabels.Value
            With rs
                .AddNew
                !BoxNr = Me.BoxNr
                !OrderItemSizeID = Me.OrderItemSizeID
                !CROP = Me.txtCrop
                !Variety = Me.txtVariety
                !Gen = Me.txtGen
                !Line = Me.txtLine
                !WeightKg = Me.txtWeight
                !GradeSize = Me.txtSize
                !CustomerDelivery = Me.txtCustomer
                !PackNo = Me.PackNo
                !Dessicated = Me.txtDessicated
                !HarvestedFrom = Me.txtHarvestedFrom
                !Treatment = Me.txtTreatment
                !WHP = Me.txtWHP
                !LabelNo = Me.LabelNumber
                .Update
            End With
        Next i
        'Opens report.
        DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
    End If
    If Me.txtPack = 1 Or 3 Or 4 Then
        'Adds duplicate records for selected company using input value.
        For i = 1 To Me.txtNumberOfLabels.Value
            With rs
                .AddNew
                !BoxNr = Me.BoxNr
                !OrderItemSizeID = Me.OrderItemSizeID
                !CROP = Me.txtCrop
                !Variety = Me.txtVariety
                !Gen = Me.txtGen
                !Line = Me.txtLine
                !WeightKg = Me.txtWeight
                !GradeSize = Me.txtSize
                !CustomerDelivery = Me.txtCustomer
                !PackNo = Me.PackNo
                !Dessicated = Me.txtDessicated
                !HarvestedFrom = Me.txtHarvestedFrom
                !Treatment = Me.txtTreatment
                !WHP = Me.txtWHP
                !LabelNo = Me.LabelNumber
                .Update
            End With
        Next i
        'Opens report.
        DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
    End If
    rs.Close
    Set rs = Nothing
    On Error GoTo 0
    Exit Sub

cmdPrint_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPrint_Click, line " & Erl & "."

End Sub
 
mike60smart

mike60smart

Registered User.
Local time
Today, 03:56
Joined
Aug 6, 2017
Messages
1,913
Gasman said:
No, No, No :(
That code does not even make any sense :(, well not to me at least.
Have you walked through that code to see what it does? Where are you getting these Me.PackNo, Me.BoxNr and Me.LabelNo from?

This is just the minimum I need to write to show you what you should be using.
Code: 
Dim lngLabelNo As Long
......
lngLabelNo = 50049

For i = 1 To Me.txtNumberOfLabels.Value
    With rs
        .AddNew
        !BoxNr = i
        !PackNo = i
        !LabelNo = lngLabelNo + i
....
then the rest of your code
Next



You probably copied from the site and not your computer?, you will have lost the indent by then :(
I hate line numbers :)

Code: 
Private Sub cmdPrint_Click()
    On Error GoTo cmdPrint_Click_Error
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    db.Execute "Delete * from tblLabels", dbFailOnError


    'Print multiple labels for current record.
    Dim i As Integer
    If IsNull(Me!txtNumberOfLabels) Then
        MsgBox "Please indicate the number of labels you want to print", vbOKOnly, "Error"
        DoCmd.GoToControl "txtNumberOfLabels"
        Exit Sub
    End If
    Set rs = db.OpenRecordset("Select * from tblLabels")
    If Me.txtPack = 1 Then
        'Adds duplicate records for selected company using input value.
        For i = 1 To Me.txtNumberOfLabels.Value
            With rs
                .AddNew
                !BoxNr = Me.BoxNr
                !OrderItemSizeID = Me.OrderItemSizeID
                !CROP = Me.txtCrop
                !Variety = Me.txtVariety
                !Gen = Me.txtGen
                !Line = Me.txtLine
                !WeightKg = Me.txtWeight
                !GradeSize = Me.txtSize
                !CustomerDelivery = Me.txtCustomer
                !PackNo = Me.PackNo
                !Dessicated = Me.txtDessicated
                !HarvestedFrom = Me.txtHarvestedFrom
                !Treatment = Me.txtTreatment
                !WHP = Me.txtWHP
                !LabelNo = Me.LabelNumber
                .Update
            End With
        Next i
        'Opens report.
        DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
    End If
    If Me.txtPack = 1 Or 3 Or 4 Then
        'Adds duplicate records for selected company using input value.
        For i = 1 To Me.txtNumberOfLabels.Value
            With rs
                .AddNew
                !BoxNr = Me.BoxNr
                !OrderItemSizeID = Me.OrderItemSizeID
                !CROP = Me.txtCrop
                !Variety = Me.txtVariety
                !Gen = Me.txtGen
                !Line = Me.txtLine
                !WeightKg = Me.txtWeight
                !GradeSize = Me.txtSize
                !CustomerDelivery = Me.txtCustomer
                !PackNo = Me.PackNo
                !Dessicated = Me.txtDessicated
                !HarvestedFrom = Me.txtHarvestedFrom
                !Treatment = Me.txtTreatment
                !WHP = Me.txtWHP
                !LabelNo = Me.LabelNumber
                .Update
            End With
        Next i
        'Opens report.
        DoCmd.OpenReport "rptVerticalLabel", acViewPreview, , "[OrderItemSizeID]=" & Me.OrderItemSizeID
    End If
    rs.Close
    Set rs = Nothing
    On Error GoTo 0
    Exit Sub

cmdPrint_Click_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdPrint_Click, line " & Erl & "."

End Sub
Click to expand...
Hi Gasman
You are a Star. That now works a treat.
Many thanks everyone.
 
You must log in or register to reply here.

Similar threads

mike60smart
Solved Printing Labels
2
Replies
38
Views
1,727
Pat Hartman
P
M
AutoSizing Text Fields on a pop-up form ...
Replies
9
Views
617
Marshall Brooks
M
C
Solved Returning from acDialog
Replies
6
Views
467
gemma-the-husky
gemma-the-husky
P
Barcode Printing on Self Adhesive Stickers
Replies
6
Views
516
Pac-Man
P
Mylton
Solved How do I create and use a global variable later?
Replies
6
Views
353
Mylton
Mylton

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom