Solved Changing text in a textbox on the main report based on text in a control on the subreport

[Sam Summers]

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]

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]

Syntax is Object.Control, not Control.Object?

So I would expect

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]

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]

Syntax is Object.Control, not Control.Object?

So I would expect

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]

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.

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]

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]

Tried all so far and nothing is happening?

 

[Sam Summers]

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]

some of event will not Fire when in "Report" view.
better move your code to Load Event of the Main report.

 

[Sam Summers]

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]

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]

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]

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]

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]

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?

 

[Gasman]

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?

 

[Sam Summers]

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]

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]

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