Solved Changing text in a textbox on the main report based on text in a control on the subreport (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
Hi Everyone,

I have tried numerous things on this with no result.

I have a main report 'SafetyInformationCard' and on it in the detail section i have unbound textboxes - 'Haz1' through to 'Haz7' and also 'PPE1' through to 'PPE8'

I have a sub report called 'HazardListSubreport' and in the detail section of this i have a textbox called 'HazardName' which displays multiple hazards for example "Manual Handling" ; "Slips, trips and Falls" etc.

Now depending what is displayed in the list i want to display in the 'Haz' textboxes either "N" or "Y" and in the 'PPE' ones either "Yes" or "No"

I have tried this code so far and nothing happens:-

Private Sub Report_Current()

If Me![HazardName].Report.HazardListSubreport = "Manual Handling" Then
' If Reports![SafetyInformationCard].Report.[HazardListSubreport].HazardName = "Manual Handling" Then
' If Me!HazardListSubreport.Report!HazardName.Text = "Manual Handling" Then
' If Reports!SafetyInformationCard!HazardListSubreport.Report!HazardName.Text = "Manual Handling" Then
' If Me.Report!HazardListSubreport.Report!HazardName = "Manual Handling" Then
Me![SafetyInformationCard].Report!Haz1.Text = "N"
Me![SafetyInformationCard].Report!Haz2 = "N"
Me![SafetyInformationCard].Report!Haz3 = "N"
Me![SafetyInformationCard].Report!Haz4 = "N"
Me![SafetyInformationCard].Report!Haz5 = "N"
Me![SafetyInformationCard].Report!Haz6 = "N"
Me![SafetyInformationCard].Report!Haz7 = "N"
Me![SafetyInformationCard].Report!PPE1 = "Yes"
Me![SafetyInformationCard].Report!PPE2 = "Yes"
Me![SafetyInformationCard].Report!PPE3 = "Yes"
Me![SafetyInformationCard].Report!PPE4 = "No"
Me![SafetyInformationCard].Report!PPE5 = "No"
Me![SafetyInformationCard].Report!PPE6 = "No"
Me![SafetyInformationCard].Report!PPE7 = "Yes"
Me![SafetyInformationCard].Report!PPE8 = "Yes"

End If

End Sub

-----------------------------------------------------------------------------------------------------------------------------------

I also tried this in
 

Minty

AWF VIP
Local time
Today, 11:59
Joined
Jul 26, 2013
Messages
10,354
This sounds like a very poor design, the Hazard and PPE should be child records of the main report.
What happens if you need Haz 8 or PPE10?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:59
Joined
Sep 21, 2011
Messages
14,038
Syntax is Object.Control, not Control.Object?

So I would expect
Code:
Me!Report.HazardListSubreport.[HazardName] = "Manual Handling" Then

I have only referred to controls on subforms and then the syntax is subformcontrol.Form.ControlName ?
I would have thought the same for reports?
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
This sounds like a very poor design, the Hazard and PPE should be child records of the main report.
What happens if you need Haz 8 or PPE10?
Its a fixed number of these
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
Syntax is Object.Control, not Control.Object?

So I would expect
Code:
Me!Report.HazardListSubreport.[HazardName] = "Manual Handling" Then

I have only referred to controls on subforms and then the syntax is subformcontrol.Form.ControlName ?
I would have thought the same for reports?
I will try that right now - thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:59
Joined
Sep 21, 2011
Messages
14,038
I will try that right now - thank you
This just worked for me, however I am not sure how you can tell to what line it is referring to.?
I would expect you bring that in with the record source for the main report.

Code:
Debug.Print Me.rptTransactions.Report.Controls("TransactionDate")
That displayed the last control for Transaction Date in my sub report.

Please note, I am just addressing the syntax, as mentioned, your structure appears to be very wrong?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:59
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Report_Current()
Me!Haz1 = "N"
Me!Haz2 = "N"
Me!Haz3 = "N"
Me!Haz4 = "N"
Me!Haz5 = "N"
Me!Haz6 = "N"
Me!Haz7 = "N"
Me!PPE1 = "No"
Me!PPE2 = "No"
Me!PPE3 = "No"
Me!PPE4 = "No"
Me!PPE5 = "No"
Me!PPE6 = "No"
Me!PPE7 = "No"
Me!PPE8 = "No"

If Me![HazardListSubreport]![HazardName] = "Manual Handling" Then
' If Reports![SafetyInformationCard].Report.[HazardListSubreport].HazardName = "Manual Handling" Then
' If Me!HazardListSubreport.Report!HazardName.Text = "Manual Handling" Then
' If Reports!SafetyInformationCard!HazardListSubreport.Report!HazardName.Text = "Manual Handling" Then
' If Me.Report!HazardListSubreport.Report!HazardName = "Manual Handling" Then
    Me!PPE1 = "Yes"
    Me!PPE2 = "Yes"
    Me!PPE3 = "Yes"
    Me!PPE7 = "Yes"
    Me!PPE8 = "Yes"

End If

End Sub
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
I wonder if its not working due to the fact that 'HazardName' is multiple fields as the list expands depending on what the user selects from the list of 31 Hazards?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:59
Joined
May 7, 2009
Messages
19,169
some of event will not Fire when in "Report" view.
better move your code to Load Event of the Main report.
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
some of event will not Fire when in "Report" view.
better move your code to Load Event of the Main report.
I had it there before but i'll do that - thank you. Hopefully i can get it working?
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
some of event will not Fire when in "Report" view.
better move your code to Load Event of the Main report.
It was in the main report already but i have put it in the Load Event but still my associated 'Haz' and 'PPE' fields remain blank?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:59
Joined
Sep 21, 2011
Messages
14,038
Start walking through your code in the Debug window after setting a breakpoint.?
See my link in my signature for Debugging of you do not know how yet.
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
Start walking through your code in the Debug window after setting a breakpoint.?
See my link in my signature for Debugging of you do not know how yet.
Well i have tried setting breakpoints and used the debug window and still getting nothing?
When i hover over line 5 and 10 i get nothing?

Private Sub Report_Load()

5 Me.Report!HazardListSubreport.Report!HazardName.SetFocus
10 If Report!HazardListSubreport.Report!HazardName.Text = "Manual Handling" Then
' If Report!HazardListSubreport.Report!HazardName.Text = "Manual Handling" Then
' If Me.Report!HazardListSubreport.Report!HazardName = "Manual Handling" Then
'If Me!Report.HazardListSubreport.[HazardName] = "Manual Handling" Then
' If Me![HazardListSubreport]![HazardName] = "Manual Handling" Then
15 Me![SafetyInformationCard].Report!Haz1.Text = "N"
20 Me![SafetyInformationCard].Report!Haz2 = "N"
25 Me![SafetyInformationCard].Report!Haz3 = "N"
30 Me![SafetyInformationCard].Report!Haz4 = "N"
35 Me![SafetyInformationCard].Report!Haz5 = "N"
40 Me![SafetyInformationCard].Report!Haz6 = "N"
45 Me![SafetyInformationCard].Report!Haz7 = "N"
50 Me![SafetyInformationCard].Report!PPE1 = "Yes"
55 Me![SafetyInformationCard].Report!PPE2 = "Yes"
60 Me![SafetyInformationCard].Report!PPE3 = "Yes"
65 Me![SafetyInformationCard].Report!PPE4 = "No"
70 Me![SafetyInformationCard].Report!PPE5 = "No"
75 Me![SafetyInformationCard].Report!PPE6 = "No"
80 Me![SafetyInformationCard].Report!PPE7 = "Yes"
85 Me![SafetyInformationCard].Report!PPE8 = "Yes"

End If

End Sub

----------------------------------------------------------------------------------------------------------------
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:59
Joined
Sep 21, 2011
Messages
14,038
Not sure you need the .Text property, rather the .Value property and as that is the default you can omit it.

Tip, do not start changing code ad hoc until you have some idea as to what the issue is. I do not even know if the .text property would even have any data, as I have only seen that used for Form controls?
Put a breakpoint on 10 after you correct your code (removing the text) and see what it contains.

Either hover over the code with the mouse or use Debug.Print in the immediate window (Ctrl + G)
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
Not sure you need the .Text property, rather the .Value property and as that is the default you can omit it.

Tip, do not start changing code ad hoc until you have some idea as to what the issue is. I do not even know if the .text property would even have any data, as I have only seen that used for Form controls?
Put a breakpoint on 10 after you correct your code (removing the text) and see what it contains.

Either hover over the code with the mouse or use Debug.Print in the immediate window (Ctrl + G)
Tried all that and still absolutely nothing - no error message, nothing when hovering over and nothing in the immediate debug window as per the attached screenshot?
 

Attachments

  • Screenshot 2021-03-11 161556.png
    Screenshot 2021-03-11 161556.png
    62.1 KB · Views: 216

Gasman

Enthusiastic Amateur
Local time
Today, 11:59
Joined
Sep 21, 2011
Messages
14,038
Can you upload a cut down version of the DB and the steps to recreate the problem.?

All that is showing is the line you are on.?
See my signatue if you have to remove personal data and upload to AWF.

This shows the contents of a control. Also you are not using the syntax @arnelgp offered?
1615480536896.png
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
Can you upload a cut down version of the DB and the steps to recreate the problem.?

All that is showing is the line you are on.?
See my signatue if you have to remove personal data and upload to AWF.

This shows the contents of a control. Also you are not using the syntax @arnelgp offered?
View attachment 89900
Right, i just used the debug.print properly and i see what is happening.

Because i have up to 31 Hazards which the user selects from. The hazards selected are then displayed in 'HazardName' in a continuous list.
Where i have specified "Manual Handling", Access is looking for the first hazard on the selected list which is "All Terrain Vehicles".

Maybe this wont work or maybe there is a way using Recordset or something outside my capabilities?

Trouble is that the list is always different as it is what the user selects for a particular hazard.
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
Can you upload a cut down version of the DB and the steps to recreate the problem.?

All that is showing is the line you are on.?
See my signatue if you have to remove personal data and upload to AWF.

This shows the contents of a control. Also you are not using the syntax @arnelgp offered?
View attachment 89900
I just saw what you said regarding Arnelgp's syntax. I'm not quite sure what he meant so i tried to sort it?
 

Sam Summers

Registered User.
Local time
Today, 11:59
Joined
Sep 17, 2001
Messages
939
Can you upload a cut down version of the DB and the steps to recreate the problem.?

All that is showing is the line you are on.?
See my signatue if you have to remove personal data and upload to AWF.

This shows the contents of a control. Also you are not using the syntax @arnelgp offered?
You need to use the bypass key on opening. It is the SafetyInformationCard report that is the problem. Thank you
 

Attachments

  • RAMS Creator.zip
    2.3 MB · Views: 203

Users who are viewing this thread

Top Bottom