label.visible=false on report??

[thecheyenne]

Hi there everybody;
Fresh from reading my Access VBA for Dummies, I'd like to update my database about activities on a school outing.
Here's what I'd like to achieve.

The school outing costs money - nothing's for free these days.
The prices for the individual activities are stored in tbl.prices; in this table,
there are 3 fields: PriceID, Amount, Description
and 4 records: a record for the morning activity
a record for the afternoon activity
a record for the transport costs
a record with the amount 0.00, for those who don't take part

Based on this tabel is a query, named qryCharges with only one field, namely SumofAmount.

Now for the tricky bit, well for me it's tricky anyway.
The report I'm working on at the moment is essentially a notification letter, informing participants of how much they've paid so far and how much is still outstanding. Easy enough, as I've got that data stored in a table but I don't much like the fact that participants have to read: " You now need to pay $0.00." when they've already paid everything.
Therefore, I'd like to play around with the visible-property of label8, which contains the text "you now need to pay...." as well as the visible-property of the field 'total_now_due' , contained on the report.

My first thought was to attach the code to the individual controls, i.e. the label8 and the field 'total_now_due' , but for some reason, I seem to be unable to enter anything in the Event-Tab when viewing their properties. So, I thought I'd put the code into the report properties but that doesn't quite seem to work either.

Here's my effort:

Private Sub Report_Open(Can cel As Integer)
If Total_now_due = qry_charges!sum ofAmount Then
repParentsInfo! Total_now_due.V isible = False
repParentsInfo! Label8.Visible = False
Else: repParentsInfo! Total_now_due.V isible = True
repParentsInfo! Label8.Visible = True
End If


End Sub

Any ideas?
Thanks for reading, anyway

[ChipR]

What are the names of the Field from the underylying data that holds the amount due and the Control that holds the amount due on your report?
I can't figure ou why you use = qry_charges!sum ofAmount instead of = 0.

[thecheyenne]

Hello ChipR (chipR rings a friendly bell, I'm sure I got some tips from you before - thanks again for taking the time to help)
I ought, perhaps, have given a more detailed discription of the database. In a nutshell it is thus:

every student must be processed one way or another, so there is a tbl.students
not all students will take part but those who do, are appended to tbl.participant s.

tbl.participant s only contains two fields, 'StudentID' and 'Money_received '.
(I'd be the first to admit that this is, perhaps, not the most elegant way of doing things but I'm still learning, and results were needed, and it did the job; a more elegant solution will come, I guess, as my knowledge increases)

The price-components (morning-activity, afternoon-activity, transport) for the activity day itself are stored in tbl.prices
and a total price is calculated using the query qry_charges, which only has the one field 'SumofAmount.'

Another query by the name of 'qry_total_now_ due' subtracts the amount stored in tbl.participant s!money_receive d from the total; the SQL statement for 'qry_total_now_ due' is as follows:

SELECT tbl_participant s.studentID, tbl_participant s.Money_receive d, 32-tbl_participant s!Money_receive d AS Total_now_due
FROM tbl_participant s;

I have entered the total amount - 32 - by hand because it was quicker at the time, but on my -to improve-list is a reminder to replace it with the field 'sumofAmount', so that as prices change, all I have to do is change them once in the tbl.prices. That's the plan, anyway.

The report I'd now like to improve uses, amongst others, two fields from qry_Total_now_d ue, namely the fields 'money_received ' and 'total_now_due' .
On the report itself, each of these fields is preceded by a label.
'Label7' contains the text "Thank you for paying..." and is followed by data from 'money_received ';
'Label8' contains the text "Please arrange for payment of..." and is followed by data from "total_now_due" .

Using the visible - property, I'd like to cover the following scenarios:
a) nothing has been paid yet;
label7.visible = false
money_received. visible = false
label8.visible = true
total_now_due.v isible = true

b) some but not all has been paid;
label7.visible = true
money_received. visible = true
label8.visible = true
total_now_due.v isible = true

c) full payment has been made:
label7.visible = true
money_received. visible = true
label8.visible = false
total_now_due.v isible = false

And after typing all this, I think I've spotted at least on mistake in my effort so far:

If Total_now_due = qry_charges!sum ofAmount ....
can't be right, it should be
If Money_received = qry_charges!sum ofAmount

But still it doesn't work, I have entered somewhere, 'an expression that has no value, aka run time error 2427.

Any ideas? Thanks for reading

[thecheyenne]

o.k. that one is solved; - only took me one day !! If I had to earn my daily bread with this type of thing, I'd starve to death.

thanks be to this site, which helped me discover that the 'event' tab, into which code can be written, is accessible only for sections of a report, rather than for individual controls within a section.

Apart from that, for some reason, I was under the impression that the code ought to include the report-name as well as the control-name, hence things like
repParentsInfo! Total_now_due
when, infact just the control-name
total_now_due
is what appears to be needed.

Still, it's strange that you would need to include within the underlying query of the report all the table-fields and query-fields which are to be used in the code. I thought that this is why you have these long names like
[table-name]![field-name] or even [query-name]![field-name]
Not so???

I am now puzzling over a similar thing, and I have made sure that all fields are contained in the query that underlies the report.

I have a table-field called 'medical'
Now, most of the kids who took part this year, are likely to take part again next year, and for these kids, there will already be data in the 'medical'-field; so all I want the report to show is
label26 (please check.....) and then the data contained in the 'medical' field.

However, any newcomers won't have submitted medical details and for them, the 'medical' field will be empty. For these kids, I want the report to show
label50 ("Please give medical info)
as well as 2 lines (on which medical info gets written when the report gets printed)
line51
line52

Now, here's my attempt at coding, there are now error-messages, (good sign??)

Private Sub GroupFooter1_Fo rmat(Cancel As Integer, FormatCount As Integer)
If Medical = 0 Then
Label26.Visible = False
Label49.Visible = False
Medical.Visible = False
Label50.Visible = True
Line51.Visible = True
Line52.Visible = True
Else: Label49.Visible = True
Medical.Visible = True
Label50.Visible = False
Line51.Visible = False
Line52.Visible = False
End If
End Sub

but somehow it doesn't do what I want it to do; an empty field 'medical' does not result in a hidden label26 and a hidden 'medical'-field, neither does it result in a visible label50 and a visible line51 and line52.

Any ideas?

Thanks for reading

[thecheyenne]

most people probably know this anyway;
If Medical = 0 Then
is wrong

it should be
if medical.text = "" then