Text boxes’ visible property

Topic

New Reply

I have a report whose Record Source is set to a query (qryprodgio).
The Format event of the Detail section goes:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
rst.Open “qryprodgio“, cnn, adOpenKeyset, adLockOptimistic
If rst!powerday > Forms!mask1!Text25 * 1000 Then
Reports!prodgio!Text36.Visible = True
Reports!prodgio!Text36 = “more than ” & Forms!mask1!Text25 & “MW”
Else
If rst!powerday < Forms!mask1!Text23 * 1000 Then
Reports!prodgio!Text36.Visible = True
Reports!prodgio!Text36 = "less than " & Forms!mask1!Text23 & "MW"
End If
End If
rst.Close
End Sub

Even when I purposefully set the values in text23 or text25 in order to set off the if condition, I can never see Text36's content in the report.
What's wrong?

Reply | Quote

Replies

Stick a couple of unbound textboxes on your report and use code in the report’s open event to set the text boxes equal to Forms!mask1!Text25 and Forms!mask1!Text23, then reference the textboxes in your code. You may have to leave the textboxes visible but set their forecolor and backcolor to white to hide them.

Reply | Quote

I stuck 2 unbound text boxes in the report (page header section), named them Potmax and Potmin and used:
Private Sub Report_Open(Cancel As Integer)
Reports!prodgio!Potmax = Forms!mask1!Text25
Reports!prodgio!Potmin = Forms!mask1!Text23
End Sub
But when I try to open the report, a message pops up:
Run-time error ‘2448’: You can’t assign a value to this object.
with the line: Reports!prodgio!Potmax = Forms!mask1!Text25 highlighted in yellow.

Reply | Quote

Try setting the control source properties for the report controls to your form controls. Use the expression builder if you’re not sure about the correct syntax. It should be something like this:

=[Forms]![frmTest01]![txtUPCODE]

This should work with your code in the format event.

Reply | Quote

Alternatively, put two calculated fields in your report recordsource that reference the form fields you want to use. Then you can refer the the values directly in your code.

Reply | Quote

But they won’t make it show that darn text box in the report .
Putting two calculated fields in my report record source that reference the form fields I want to use gives me this:

Private Sub Report_Activate()
Set cnn = CurrentProject.Connection
Dim cmd As New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
cmd.CommandText = “SELECT Id, anno, mese, giorno, ” & _
Forms!mask1!List11 & ” as selectedfield, selectedfield/24 as powerday, ” & Forms!mask1!Text23 _
& ” as powermin, ” & Forms!mask1!Text25 & ” as powermax FROM mediegio”
With cat.Views
‘.Append “qryProdGIO”, cmd
.Item(“qryProdGIO”).Command = cmd
End With
Set cmd = Nothing
End Sub

and now here’s how
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
rst.Open “qryprodgio”, cnn, adOpenKeyset, adLockOptimistic
If rst!powerday > rst!powermax * 1000 Then
Reports!prodgio!Text36.Visible = True
Reports!prodgio!Text36 = “more than ” & Potmax & ” MW”
Else
If rst!powerday < rst!powermin * 1000 Then
Reports!prodgio!Text36.Visible = True
Reports!prodgio!Text36 = "less than " & Potmin & " MW"
End If
End If
rst.Close
End Sub
looks like.
Still the report won't show text36's contents for any record.

Reply | Quote

Even putting
Private Sub Corpo_Format(Cancel As Integer, FormatCount As Integer)
rst.Open “qryprodgio”, cnn, adOpenKeyset, adLockOptimistic
If rst!powerday > rst!powermax * 1000 Then
Reports!prodgio!Text36.Visible = True
Reports!prodgio!Text36 = “more than ” & rst!powermax & ” MW”
Else
If rst!powerday < rst!powermin * 1000 Then
Reports!prodgio!Text36.Visible = True
Reports!prodgio!Text36 = "less than " & rst!powermin & ” MW”
End If
End If
rst.Close
End Sub
Doesn’t change the outcome.

Reply | Quote

Hard to tell from here what exactly is causing the breakdown. I’d be curious to hear what the actual flow looks like if you put in a break point and went through the code line by line. That might help give us a clue where to focus our attention.

Reply | Quote

I like that even better!

Reply | Quote

The strange thing is that if I keep the line
“If rst!powerday > Forms!mask1!Text25 * 1000 Then”
as it originally was in my first message,
everything runs smoothly but for the fact that the contents of text36 doesn’t show. But if I apply your advice and change the line to
“If rst!powerday > Reports!prodgio!Potmax * 1000 Then”
after having set Potmax’s control source =[Forms]![mask]![text25]
the code stops at this line(If rst!powerday > Reports!prodgio!Potmax * 1000 Then) with a message box saying, “Run-time error ‘2447’: There is an invalid use of the .(dot) or ! operator or invalid parentheses.”

Reply | Quote

Hi,
I think your problem may be that you are opening a recordset based on qryprodgio (when your report is already bound to qryprodgio) but you’re never moving through the recordset so rst!powerday always refers to the same record.) Assuming that powerday appears in your report, you could use something like the following:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
With Me
If !powerday > Forms!mask1!Text25 * 1000 Then
!Text36.Visible = True
!Text36 = “more than ” & Forms!mask1!Text25 & “MW”
Else
If !powerday < Forms!mask1!Text23 * 1000 Then
!Text36.Visible = True
!Text36 = "less than " & Forms!mask1!Text23 & "MW"
End If
End If
End With
End Sub
Hope that helps.

Reply | Quote

I’ve implemented your Sub Detail_Format word for word and now the code stops at the line that sets the value of Text36:
!Text36 = “more than ” & Forms!mask1!Text25 & “MW” or
!Text36 = “less than ” & Forms!mask1!Text23 & “MW”
depending on which if condition is set off, with the message:”Run-time error ‘2448’: You can’t assign a value to this object.”

Reply | Quote

The error message was due to the fact that Text36’s Control Source property was put =Powerday instead of being empty(oops!Mea culpa).
But there’s another problem:
When the code runs and one of the if conditions occurs, Text36 remains forever visible with the message contained in that particular if condition, so basically if we say that the second if condition becomes true, the report shows the message “less than .4 MW” for all the lines from then on.

Reply | Quote

That’s because you never reset the Visible property for the case neither of the conditions is true. Put a line before the IF-Statement:

with me
!Text36.Visible = false ‘!!!!!!!!!!
If !powerday > Forms!mask1!Text25 * 1000 Then
!Text36.Visible = True
!Text36 = “more than ” & Forms!mask1!Text25 & “MW”
Else
If !powerday < Forms!mask1!Text23 * 1000 Then
…
end with
Cheers
Emilia

Reply | Quote

I guess I needed fresh blood to get out of it .
The table Mediegio, which through qryProdGio feeds data to the text boxes in the detail section, actually is made up of more than a thousand records, so this two events(falling below Forms!mask1!Text23 or rising above Forms!mask1!Text25) are bound to happen many times if I set the value of Text23 or Text25 low or high enough respectively. Well, because of this and any time either event occurs, I’d like the actual message to read, “less than [text23.value] kW n.Y” or “more than [text25.value] kW n.Z” where Y or Z is incrementing each time either event occurs.
How can I can I count the times either event occurs and display it in the Text36 text box?

Reply | Quote

I thought I could solve the problem using:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim a, b
With Me
!Text36.Visible = False
If !powerday > Forms!mask1!Text25 * 1000 Then
a = a + 1
!Text36.Visible = True
!Text36 = “more than ” & Forms!mask1!Text25 & “MW Nr.” & a
Else
If !powerday < Forms!mask1!Text23 * 1000 Then
b = b + 1
!Text36.Visible = True
!Text36 = “less than ” & Forms!mask1!Text23 & “MW Nr.” & b
End If
End If
End With
End Sub
but the number shown in Text36 doesn’t increment:
“less/more than [text23/5.value]MW Nr.1“

Reply | Quote

Hi,
Try changing
Dim a, b
to the following:
Static a as long, b as long
and then it should retain its previous value each time the format runs. It’s probably also worth storing the values of Forms!Mask1!text25 and Forms!Mask1!Text23 in variables in the report’s Open event so the code doesn’t have to keep referring back to the textboxes.
Hope that helps.

Reply | Quote

Thanks also to Charlotte, Emilia and Paul.
Right, so now Report_Activate looks like this:
Private Sub Report_Activate()
Dim Media As Double, a, b, c, d, e, strSQL As String
Set cnn = CurrentProject.Connection
rst.Open “select Max(mediegio.” & Forms!mask1!List11 & “) as maxgio from mediegio”, cnn, adOpenKeyset, adLockOptimistic
a = rst!maxgio
b = a / 24000
Dim cmd As New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
If Not Isloaded(“Mask1”) Then MsgBox “Input Form not loaded”
cmd.CommandText = “SELECT Id, anno, mese, giorno, ” & Forms!mask1!List11 & ” as selectedfield, selectedfield/24 as powerday FROM mediegio”
With cat.Views
‘.Append “qryProdGIO”, cmd
.Item(“qryProdGIO”).Command = cmd
End With
Set cmd = Nothing
Reports!prodgio!Text33 = “Produzioni Giornaliere in ingresso”
Reports!prodgio!Text34 = “Produzione Giornaliera di ” & Forms!mask1!List11
rst.Open (“qryProdGio”), cnn, adOpenKeyset, adLockOptimistic
rst.MoveLast
rst.MoveFirst
rst.Filter = “selectedfield = Null”
Text41 = “I giorni di fermo sono stati ” & rst.RecordCount
rst.Close
‘rst.Open “select Max(mediegio.” & Forms!mask1!List11 & “) as maxgio from mediegio”, cnn, adOpenKeyset, adLockOptimistic
‘a = rst!maxgio
‘b = a / 24000
Text43 = “the peak ” & a & “(” & b & ” MW) was reached on [day][month][year]”
End Sub
And it works fine but if I delete the red part and uncomment the green one the code stops at
rst.Open “select Max(mediegio.” & Forms!mask1!List11 & “) as maxgio from mediegio”, cnn, adOpenKeyset, adLockOptimistic
with the message, “Run-time error ‘3265’: Item cannot be found in the collection corresponding to the requested name or ordinal”
How come I can’t open the recordset if I put the Open command towards the end of the sub?

Reply | Quote

Hi,
I suspect the problem is that you didn’t set rst = Nothing in between closing and reopening. This means that you’re using the same recordset object, on which you’ve applied a filter (selectedfield=Null). This filter still exists when you reopen the recordset, but in this case the field ‘selectedfield’ does not exist, hence your error. If you change your code to:
rst.close
set rst=nothing
rst.open “select Max(mediegio.” & Forms!mask1!List11 & “) as maxgio from mediegio”, cnn, adOpenKeyset, adLockOptimistic
then I think you should be OK.
Hope that helps.
PS You could also simply add rst.filter = “” to your code to clear the filter.

Reply | Quote

Whoa! Closing the barn door is not enough to insure the horse won’t bolt, you must raze it to the ground.
I needed this problem to figure out exactly the implications of closing versus setting a recordset to nothing(Charlotte’s teachings also come to mind).
Now (I’ve got a feeling that this is akin to the closing vs annihilating a recordset but I need your knowledge to sniff it out) Private Sub Detail_Format and Private Sub Report_Activate run smoothly but being the report on many pages because the Mediegio table is made up of more than a thousand records, the two event counters give the correct result only if the event occurs for the record that’s not at the top of the page and here’s why:
Let’s consider the counter b declared in Private Sub Report_Activate; if the event occurs for the record that’s at the top of the page, the counter gets incremented by two instead of one for that single loop. For example, the last reported number in the Text36 message, “less than…” on page 15 of the report is 77 but jumps to 79 on the following page.
Would you like to receive the file? It’s only 173k .

Reply | Quote

Feel free to send it to me at rarchi5404@aol.com – I can’t guarantee I will look at it this week as I’m off on holiday on Wednesday but I’ll get to it as soon as I can.
I will as usual post any useful findings here.

Reply | Quote

Whoa! Closing the barn door is not enough to insure the horse won’t bolt, you must raze it to the ground.
I needed this problem to figure out exactly the implications of closing versus setting a recordset to nothing(Charlotte’s teachings also come to mind).
Now (I’ve got a feeling that this is akin to the closing vs annihilating a recordset but I need your knowledge to sniff it out) Private Sub Detail_Format and Private Sub Report_Activate run smoothly but being the report on many pages because the Mediegio table is made up of more than a thousand records, the two event counters give the correct result only if the event occurs for the record that’s not at the top of the page and here’s what happens:
Let’s consider the counter b declared in Private Sub Report_Activate; if the event occurs for the record that’s at the top of the page, the counter gets incremented by two instead of one for that single loop. For example, the last reported number in the Text36 message, “less than…” on page 15 of the report is 77 but jumps to 79 on the following page.
Would you like to receive the file? It’s only 173k .

Reply | Quote

I forgot to mention in my last response that this may happen if Access need multiple passes to format the report (eg If you have grouping and use the Keep Together option) you effectively end up formatting the section multiple times. You should be able to cure this (assuming it’s the cause) by adding reversing code to the OnRetreat event (basically the same code, from what I recall, but with a = a – 1 and b = b – 1 instead of adding 1).
Hope that helps.

Reply | Quote

Hi,
I’ve just had a quick look at your database and I think the problem is that when Access gets to the bottom of the page, it formats the detail section, checks to see if it will fit on the current page, if not it moves to the next page and then reformats the section but it’s still on the same record, hence the double counting. The solution should be to add the following to the start of the Detail’s Format event code:
If FormatCount > 1 Then Exit Sub
This will stop the value from incrementing if Access has already formatted the section for that record.
Hope that helps.

Reply | Quote

Actually, you don’t want to exit the sub, but you do want to test for the FormatCount and only increment the value if FormatCount = 1, i.e., on the first pass. This has been a standard trick since Access Basic was first introduced. If you use the print event, you need to make the same test and only decrement the value when PrintCount = 1 as well.

Reply | Quote

I guess anyone who doesn’t know this trick is bound to produce some haphazard report sooner or later.
What I don’t understand is why you’d increment the value if FormatCount=1 but decrement it if PrintCount=1

Reply | Quote

You wouldn’t. I posted that early in the morning/late in the evening and my brain got tangled.

Reply | Quote

Do you mean that you wouldn’t use the PrintCount event at all or that you’d actually increment the value when PrintCount = 1?

Reply | Quote

If I had to use it, I would increment it. However, the OnPrint event only occurs when the report is actually printing, not when it’s being formatted, so I rarely use it. It it isn’t really useful except when you want to do something ONLY when the report is actually being printed. I have used it in the past to count the records that were actually printed so I could notify the user if there was a difference between the number of records in the recordset and what was actually printed.

Reply | Quote

But Rory’s suggestion to exit the sub is the only viable in my case; in fact
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static a As Long, b As Long
With Me
!Text36.Visible = False
If !powerday > Forms!mask1!Text25 * 1000 Then
If FormatCount = 1 Then
a = a + 1
!Text36.Visible = True
!Text36 = “more than ” & Forms!mask1!Text25 & ” MW Nr.” & a
End If
Else
If !powerday 1 Then Exit Sub
With Me
!Text36.Visible = False
If !powerday > Forms!mask1!Text25 * 1000 Then
a = a + 1
!Text36.Visible = True
!Text36 = “more than ” & Forms!mask1!Text25 & ” MW Nr.” & a
Else
If !powerday < Forms!mask1!Text23 * 1000 Then
b = b + 1
!Text36.Visible = True
!Text36 = "less than " & Forms!mask1!Text23 & " MW Nr." & b
End If
End If
End With
End Sub
does wonders.
I hope you don't hold any grudge and answer my question about why you’d increment the value if FormatCount=1 but decrement it if PrintCount=1

Reply | Quote

Thanks.
I’ve noticed that if one types a value either in Text23 and Text25 but leaves the cursor in the text box, VBA considers the text box value as being Null thus invalidating the expressions using such text box value.
Suppose the end user does that; how can I tell him/her that, after the value has been typed, he/she hasn’t left the text box?
Isn’t there something similar to a NoData event for a text box?

Reply | Quote

In Access, unlike VB, the value typed into the control isn’t its actual value until it gets written. So you need to invoke one of the control events like Exit or LostFocus, or issue a save on the record before you try to produce the report.

Reply | Quote

But if the end user doesn’t leave the text box, neither the Exit or LostFocus event takes place so how can I invoke it?

Reply | Quote

Hi,
How is your user opening the report? If it’s a button on the form, then there shouldn’t be a problem as he/she will have to exit the text box to click on the button. It might be worth having come code in the report’s open event to just check that neither of the text box values is null or an empty string.
Hope that helps.

Reply | Quote

I’m impressed at the insight you have.
Actually I had yet to come to that, still you hit the spot.
The Mediegio table(included in the file I sent you) is comprised of daily records spanning 4 years.
I’d like to add a textbox to the Prodgio Report footer which should display how many times the average of the selectedfield value for each day gets below the value typed in text23 (similar to text36 which you’ve already brilliantly dealt with). For example, I take the values of selectedfield for the same day each year and make an average, so basically I should obtain a 365 values recordset to be compared to the value in text23 in the same way as I did previously for qryProdGio which was ProdGio report’s control source.
I’ve prepared one query for each year whose code is the same to one another’s except for the where clause’s year:
SELECT MEDIEGIO.Id, MEDIEGIO.Anno, MEDIEGIO.Mese, MEDIEGIO.Giorno, MEDIEGIO.selectedfield
FROM MEDIEGIO
WHERE (((MEDIEGIO.Anno)=1997));
So now I have 4 subqueries. I should now create a query being fed by the four subqueries and this query should include a field which calculates the average of the selectedfield of each of the subqueries but putting avg(query1.selectedfield,query2.selectedfield) as expression for the field gives me an error message.
Could you endow me with another slice of your wisdom?

Reply | Quote

Force a record save in whichever routine on the form opens the report.

Reply | Quote

Issue a save on what record? I’m not creating any record. I’m just comparing a value in a text box with a record that’s already there.

Reply | Quote

[indent]

---

I’ve noticed that if one types a value either in Text23 and Text25 but leaves the cursor in the text box, VBA considers the text box value as being Null thus invalidating the expressions using such text box value.

---

[/indent]This led me to believe you were modifying a value in a record. In that case, the value isn’t saved in the field underlying the textbox until you save the record.

Reply | Quote

I was just using the values in Text23 and Text25 as a reference against which to compare the value taken by the powerday field while the detail section is being formatted, check
If !powerday > Forms!mask1!Text25 * 1000 Then
If !powerday < Forms!mask1!Text23 * 1000 Then
in Sub Detail_Format in one of my earlier messages.

Reply | Quote