Solved Printing Labels

[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.

 

[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