A "Report " query

[macky]

I am using a report to produce a laden sheet which opens in design mode "On Click" of a command button. There are 3 columns, Quantity, Type and Model, all of the fields are lables thus enabling the quantity options to be different each time the report is printed, (without saving), the other lables contain the equipment details. To cover all options I have a lot of lables to cover all types of equipment, my question is:

What code can I use if the lable remains unfilled it will not be printed so I dont get a lot of empty boxes or spaces in between rows that do have data in them.

Thanks for time in advance

 

[allanon]

Use a query to select only records that have data to print.

 

[macky]

I cant run a query as I am using only lables to hold the data not bound fields. An example is shown below:

Quantity Type Model
100 Radio XYZ1
50 Battery Charger ABC2
etc.

What I need is that if the lable that holds the quantity is empty then the following 2 labels that contain type and model do not appear on the print out of the report.

Thanks

 

[allanon]

I am not entirly sure I understand BUT that being said there is a way to do this. It wont be easy. Before I say more I need to know a couple of things. How are you getting your data for the report? How are you placing that data into the labels. On what event? etc.... Do you have a certain number of labels on the form.

 

[macky]

the report is being used to produce a "pro-forma" laden sheet that will have the deails typed in and printed but not saved so you get the template report layout the same every time you open the report. Its used purely as a neat way of producing a laden sheet that can have different quantity's of pre-defined equipment for a specific location at the click of a button.

There are 10 rows of labels, (1 for each piece of equipment)
The labels are filled in each time the report is opened, the type and model labels wont change only the quantity will, this will depend on the location that the equipment is sent to.

I hope this answers your question.

 

[allanon]

Ok. So Lets see if I have an understanding. You have a report with a detail section that is about the size of a page. You have 30 labels in this detail section, 10 rows of 3. The report opens up in design mode. Someone enters the data. Flip the report into view mode. Print the report. Is this about right?

 

[macky]

YEP!!! I think you understand this problem now, is there some code that will not print the row of 3 labels if the quantity lable is left empty.

Cheers

 

[allanon]

Ok... I am not completely sure what you will do about the field disappearing if it is blank but I would code something like this in the activate event.

Private Sub Report_Activate()
Dim theTop As Integer

theTop = 100

If Me.lbl01 <> &quot;&quot; Then
Me.lbl01.Top = theTop
Me.lbl02.Top = theTop
Me.lbl03.Top = theTop
theTop = theTop + 400
Else
Me.lbl01.Visible = False
Me.lbl02.Visible = False
Me.lbl03.Visible = False
End If
If Me.lbl04 <> &quot;&quot; Then
Me.lbl04.Top = theTop
Me.lbl05.Top = theTop
Me.lbl06.Top = theTop
theTop = theTop + 400
Else
Me.lbl04.Visible = False
Me.lbl05.Visible = False
Me.lbl06.Visible = False
End If
If Me.lbl07 <> &quot;&quot; Then
Me.lbl07.Top = theTop
Me.lbl08.Top = theTop
Me.lbl09.Top = theTop
theTop = theTop + 400
Else
Me.lbl07.Visible = False
Me.lbl08.Visible = False
Me.lbl09.Visible = False
End If
End Sub

and so on. Still a couple of things to work out from there but it should get you going.

 

[macky]

Firstly Thanks for your effort in helping to solve this dilemma.

I've copied your code into the &quot;On Activate&quot; event of the report and turned all but the first 12 lines into a statement format to see if I can get the first row of lables not to print if the quantity box is left empty, unfortunately this did not happen, I changed the lable numbers to the correct ones from the report and this iswhat is in the event procedure:

Private Sub Report_Activate()

Dim theTop As Integer

theTop = 100

If Me.lbl01 <> &quot;&quot; Then
Me.lbl17.Top = theTop
Me.lbl18.Top = theTop
Me.lbl19.Top = theTop
theTop = theTop + 400
Else
Me.lbl17.Visible = False
Me.lbl18.Visible = False
Me.lbl19.Visible = False
End If
'If Me.lbl04 <> &quot;&quot; Then
' Me.lbl04.Top = theTop
' Me.lbl05.Top = theTop
' Me.lbl06.Top = theTop
'theTop = theTop + 400
'Else
'Me.lbl04.Visible = False
'Me.lbl05.Visible = False
'Me.lbl06.Visible = False
'End If
'If Me.lbl07 <> &quot;&quot; Then
' Me.lbl07.Top = theTop
' Me.lbl08.Top = theTop
' Me.lbl09.Top = theTop
'theTop = theTop + 400
'Else
' Me.lbl07.Visible = False
' Me.lbl08.Visible = False
' Me.lbl09.Visible = False
'End If
End Sub

I would have completed the rest of the code and corrected the label numbers if the first bit of code had worked, what do you suggest.

 

[allanon]

What is happening?