Solved Printing Labels

[mike60smart]

Moke's code would not do that?
Have you even walked through the code?

Hi Gasman
I put a breakpoint on the line:-

100 For i = 1 To Me.txtNumberOfLabels.Value

When I checked the value by hovering on the Me.txtNumberOfLabels.Value it indicates 10 labels which is the correct value.

Cannot see why the Report does not generate 10 Labels?

 

[Pat Hartman]

The solution in #16 only requires a simple table and a change to your where clause to include the join. NO CODE IS REQUIRED. I've written my million lines of code. I don't need the practice so I don't write unnecessary code.

 

[Gasman]

Hi Gasman
I put a breakpoint on the line:-

100 For i = 1 To Me.txtNumberOfLabels.Value

When I checked the value by hovering on the Me.txtNumberOfLabels.Value it indicates 10 labels which is the correct value.

Cannot see why the Report does not generate 10 Labels?

So does it loop 10 times? I cannot see why it would not?
Are you using the correct table?, inspect the record count? after the loop.

 

[mike60smart]

Hi Everyone
Many thanks to Moke for his Code which works just great.

The reason it was not displaying the correct number of Labels was as always a
mistake on my part. I had the wrong Record Source set.

Now I need to sort out how to deal with Controls that contain no value.

In this section:-

.AddNew
'!BoxNr = Me.CustomerID
!CROP = Me.txtCrop
!Variety = Me.txtVariety
!Gen = Me.txtGen
!Line = Me.txtLine
!NetWeight = Me.txtWeight
!Customer = Me.txtCustomer
'!PackNo = Me.Country
!Dessicated = Me.txtDessicated
!HarvestedFrom = Me.txtHarvestedFrom
'!LabelNo = Me.Country
.Update
End With

How would I deal with the line:- !HarvestedFrom = Me.txtHarvestedFrom when Me.txtHarvestedFrom does not have a value?

 

[moke123]

Nz() is your friend.

 

[Gasman]

I cannot believe you have not used NZ() in all the time you have been using Access.

 

[mike60smart]

Nz() is your friend.

Hi Moke

I tried !HarvestedFrom = Nz(Me.txtHarvestedFrom, 0)
This gave me a Conversion error
Tried this !HarvestedFrom = Nz(Me.txtHarvestedFrom, "")
Same error

I cannot believe you have not used NZ() in all the time you have been using Access.

Hi Gasman

I have used it many times and tried using it as mentioned and for some reason it would not work.

 

[moke123]

Note Line is a reserved word!

Here's a qryDef version. Check that the parameters are correct ( copy/paste and aircode)

Dim db As DAO.Database
Dim i As Integer


Set db = CurrentDb

db.Execute "Delete * from tblLabels", dbFailOnError

    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

 For i = 1 To Me!txtNumberOfLabels

Const Sql_Insert As String = _
          "Insert into  tblLabels" & _
          "(BoxNr,Crop,Variety,Gen,Line,NetWeight,Customer,PackNo,Dessicated,HarvestedFrom,LabelNo) " & _
          "Values(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10)"


     With Db.CreateQueryDef("", SQL_Insert)
          .Parameters(0) = Me.CustomerID
          .Parameters(1) = Me.txtCrop
          .Parameters(2) = Me.txtVariety
          .Parameters(3) = Me.txtGen
          .Parameters(4) = Me.txtLine
          .Parameters(5) = Me.txtWeight
          .Parameters(6) = Me.txtCustomer
          .Parameters(7) = Me.Country
          .Parameters(8) = Me.txtDessicated
          .Parameters(9) = Me.txtHarvestedFrom
          .Parameters(10) = Me.Country
     .Execute dbFailOnError
     .Close
End With

Next i

DoCmd.OpenReport "Customer Label Report", acViewPreview

set db = nothing

 

[Gasman]

Hi Moke

I tried !HarvestedFrom = Nz(Me.txtHarvestedFrom, 0)
This gave me a Conversion error
Tried this !HarvestedFrom = Nz(Me.txtHarvestedFrom, "")
Same error

Hi Gasman

I have used it many times and tried using it as mentioned and for some reason it would not work.

What does harvestedfrom contain normally?

 

[moke123]

What does harvestedfrom contain normally?

I'm thinking Mike might be growing his own weed.

 

[Pat Hartman]

If you actually want to create rows in a table for each carton you are receiving, here is an example:

 

[KitaYama]

If you actually want to create rows in a table for each carton you are receiving, here is an example:

@Pat Hartman That was a very smart way. Hadn't seen something like that ever.
Thanks for the lesson.

 

[Gasman]

Mike, I have to ask.
What does it matter whether the field has data or not?
If HarvestedFrom is a location or even a date, I would expect is NEEDS a value?, and not just any old default value from a NZ() ?
So there should be data validation to ensure this, else just leave it empty.

 

[mike60smart]

Mike, I have to ask.
What does it matter whether the field has data or not?
If HarvestedFrom is a location or even a date, I would expect is NEEDS a value?, and not just any old default value from a NZ() ?
So there should be data validation to ensure this, else just leave it empty.

Hi Gasman
I agree. It is a Date field.

 

[mike60smart]

If you actually want to create rows in a table for each carton you are receiving, here is an example:

Hi Pat A very good example. Many thanks

 

[mike60smart]

Hi Everyone
Now that this is working as the op asked I have now been asked to add something which is out of my VB Skill range,

I will start a new thread, Many thanks for all the help

 

[Gasman]

Well at least mark it as Solved?

 

[mike60smart]

Well at least mark it as Solved?

Thanks for the reminder

 

[Pat Hartman]

The append query in the example can also append a date.

I actually used this method for a client. When he received goods, he wanted them to go into inventory as separate cartons rather than as one item with a quantity of 5. That was how he wanted to track shipments so he always sold the oldest cartons first.

There are a couple of other uses for this method.
1. When you are printing address labels you don't want to have to waste sheets. So, you can insert 12 blank records for example so that the printing starts on label 13.
2. You can generate a record for each day between two dates by using dates instead of sequence numbers in the table. Same client. He needed to know how many cartons he was storing each day since that is how his warehouse storage fee was determined.
3. You can print x labels per shipping order so record1 prints 4 labels and record2 prints 8 labels and record3 prints 1 label, etc.

It is always easiest when you can create solutions with queries rather than VBA code loops