Error 2455: “You entered an expression that has an invalid reference to the property Form/Report”

[ItsRoland]

I have a form with a subreport in it that has been working for months now. This form is called "RAMandLRAM" and the report is called "rRAM10x10" (planning on renaming these) but we'll call the form "fRAM" and the report "rRAM" for simplicity. fRAM is going to be opened as a popup form by clicking a button on another form, called "fHighRisk" (fHighRisk is a subform to "fRiskAssessmentTest"). I recently went in to improve some of the code in fRAM, as I've gotten much better / more familiar with VBA since creating it. I made sure to test everything I improved / replaced / added before moving on to the next so I could isolate any issues that occurred. The last thing I did before I started receiving the error was add in a way to check how the form was opened using 'Screen.ActiveControl.Name' and change the Tab Order of fRAM based on that. After adding that code to fRAM, I opened it in form view to ensure there weren't any problems, and didn't receive any errors. Then I wen't to fHighRisk and added 2 buttons to open fRAM, the names of which are whats checked for when the form opens to change the Tab Order. Once I tested that these buttons work, the error started appearing.

"Error 2455: You entered an expression that has an invalid reference to the property Form/Report"

The error appears on some lines that mess with the recordsource of the subreport rRAM, but not all of them.


i.e.

Me.rRAM10x10.Report.RecordSource ="tRAM10x10"

Removing the code I created before this error started appearing does not fix the error. The code is as follows:
(NOTE that I wrote "fHighRisk" instead of the parent form -> subform path for testing purposes)

If CurrentProject.AllForms("fHighRisk").IsLoaded =TrueThen
    If Screen.ActiveControl.Name ="btnRAMimpact"Then

        Me.btnForFocus.TabIndex =0

    ElseIf Screen.ActiveControl.Name ="btnRAMlhood"Then

        Me.Tabordertest.TabIndex =0

    EndIf
 EndIf

I've done some research, and everything I've found on the error states that it derives from the parent form (in this case fRAM) having no records in its recordset. Maybe I'm wrong, but I do not believe this is the case. fRAM isn't a form based on a table / query / etc., it's just a means to hold controls. More on that later.

I've also done a bit of troubleshooting, and have found the following so far:
- The recordsources I set to rRAM are all fine. They haven't been changed, and the correct data is still there.
- If I comment out all the lines that give me this error and try to open fRAM, the form opens, but entire form is blank for some reason. All the controls (buttons / labels / etc) are gone, even though their visible properties are set to 'Yes'.
- Adding "Option Explicit" to the module doesn't provide any extra information. (After adding it, Access yelled at me for not declaring the variables in my 'For' loops, but after that it went back to the same error on the same lines)

The Open event of fRAM adds appropriate captions to controls on the forms and not much else. It also calls the public subs "Refresh" and "Activate", which is where I'm getting all of the errors. Commenting out these calls doesn't prevent the error. Note that the error appears on lines that change the recordsource of the report (see example above) within the subs "Refresh" and "Activate" (These lines of code only appear in these two subs). I can post more code within "Refresh" and "Activate" if requested, but they're both fairly long and inefficiently built, which is why I only posted whats giving the error. (inefficient as in long IF loops & Case statements instead of simpler, more effective methods since I was new to VBA when I made those sections)

Just in case I'm missing something, here is the code for fRAM's open event.

Private Sub Form_Open(Cancel As Integer)

Dim sID As Integer


If CurrentProject.AllForms("fRiskAssessmentTest").IsLoaded = True Then


    sID = Forms!fRiskAssessmentTest!fHighRisk!ScenarioID.Value


ElseIf CurrentProject.AllForms("fHighRisk").IsLoaded = True Then


    sID = Forms!fHighRisk!ScenarioID.Value


Else


    sID = 1


End If


Me.lblScenarioID.Caption = "Scenario " & sID




If CurrentProject.AllForms("fHighRisk").IsLoaded = True Then


    If Screen.ActiveControl.Name = "btnRAMimpact" Then
    
        Me.btnForFocus.TabIndex = 0
        
    ElseIf Screen.ActiveControl.Name = "btnRAMlhood" Then
        
        Me.Tabordertest.TabIndex = 0
        
    End If


End If




clr1 = RGB(122, 197, 205)
clr2 = RGB(152, 245, 255)


'Call Refresh_Click


'Tells activate to not prompt for RAMsize when the form opens
RSmsg = "no"


'Call Activate_Click






'button colors
Dim lblName As String, btnName As String, ctrlName As String, lblCaption As String, Rating As Integer, ctrl As Control


For Each ctrl In Me.Controls
    
    If ctrl.ControlType = acCommandButton Then
  
        ctrlName = ctrl.Name
    
        Me.Controls(ctrlName).BackColor = clr2
    
    End If
    
Next


Dim i As Integer
'Darkens buttons if value is stored in table
For i = 1 To 5
    
    lblName = "Vardisp" & i
    
    lblCaption = Me.Controls(lblName).Caption
    
    If IsNull(DLookup("[Variable Impact]", "tImpact", "scenarioID = " & sID & " AND [Impact Variable] = """ & lblCaption & """ ")) Then
    
        GoTo Skip_loop2
        
    Else
    
        Rating = DLookup("[Variable Impact]", "tImpact", "scenarioID = " & sID & " AND [Impact Variable] = """ & lblCaption & """ ")
    
    End If
    
    Dim b As Integer
    For b = 1 To 10
        
        If Rating = b Then
            
            btnName = "Var" & i & "R" & b
            
            Me.Controls(btnName).BackColor = clr1
            
        End If
        
        
    Next
    
    
Skip_loop2:
    
Next


End Sub

Some context about fRAM and it's report:
(PLEASE NOTE: This was built a while ago and is in need of improvements)

The purpose of fRAM is to display a matrix for Risk Assessment. rRAM acts as the matrix; The user inputs the dimensions, and the matrix changes size (anywhere from 10x10 to 3x3) by changing it's recordsource to a table that has been laid out for those specific dimensions. fRAM is where user interaction happens; Buttons will appear / hide based on the dimensions of the matrix and a few other user dependent things, labels and captions change, so on and so forth. This form doesn't display any records from a table, it just acts as a means to display / interact with the risk matrix and perform calculations based on choices in the background. Hopefully that helps.

I'm pretty lost here, can't seem to find any kind of solution. Any help is greatly appreciated. Please let me know if I forgot to add something important here. Thanks!

[Minty]

I'm always dubious of any code that requires the use of Screen.ActiveControl.Name
It implies that your process flow may be unknown, or not accurately defined.

You should be able to use passed values (OpenArgs is an obvious method) from specific actions or controls to determine what called a form or what action is required on a form when opened, and act accordingly.

I would examine why you are using that and what you could do to better determine the workflow.

[ItsRoland]

That's a fair point. I'll be sure to make that change once I get this working again. However, that code isn't the problem. As stated in my initial post, removing it doesn't result in the error going away. Not sure how adding code can cause an error but removing it doesn't cause the error to go away, but oh well. What really confuses me is that this has worked with no problems for months and suddenly it doesn't like me changing the recordsource of a report, even thought I didn't touch anything related to that process.

[Micron]

from post 1, why a form goes completely blank
http://allenbrowne.com/casu-20.html

[ItsRoland]

None of the criteria listed in that link is true for the form (fRAM) in question here.

For each of the criteria listed covering why a form can be blank:
1. There are no records to display
- The form doesn't display any records
- Even if the form DID display records, the Record Source set to fRAM has records in it
- Data Entry is set to "No"
- There is no Filter or where condition applied to fRAM
- The form is based on a table that has records in it

2. No new records can be added
- Allow additions is set to "Yes"
- Recordset Type is set to Dynaset
- fRAM is NOT based on a query, let alone a ready only query


Also, the issue here is not that the form is blank after getting rid of the problem code, that was just a result of a troubleshooting step I tried. The issue here is the error stated in my post

"Error 2455: You entered an expression that has an invalid reference to the property Form/Report"

Unless I'm missing something and the form being blank after removing the code that gets the error is also caused by the error, fixing the form being blank after removing the code will still leave me in the same position I started in.

[Minty]

Have you tried setting the record source of the underlying report rather than the form object and then reloading it?

Referring to reports embedded in forms is particularly troublesome in my experience, they don't behave the same as a form or sub-form as they aren't "live".

[ItsRoland]

The report's record source is set to the table that gives it the largest dimensions (10x10) by default (as in the report's record source property is manually set to the corresponding table). By removing the lines in the code that change the recordsource of the report, it should just use the table I've set in the recordsource property, as there's nothing there to change it. For whatever reason this results in the entire form being blank as discussed above. Alternatively, there is the possibility that this DOES work, and I just have no idea it's working because the entire form is blank. However, due to the points I mentioned in my last post covering reasons why a form goes completely blank, I don't think this is the case.

[Minty]

I think you might need to post up your database in a stripped out form for anyone to assist further, I don't think we can see enough or more likely fix what is happening despite your detailed descriptions.

[Minty]

Just noticed this is cross-posted here https://stackoverflow.com/questions/...ssion-that-has
Roland I realise you aren't getting a quick solution but please for future reference have a read https://www.excelguru.ca/content.php?184

[ItsRoland]

My bad, didn't realize cross posting was frowned upon, until recently I've never posted on forums like this. I'll be sure to follow those guidelines in the future. Thanks!




Also, I've attached the database. The forms "RAMandLRAM" and "fHighRisk" are in the "Troubleshoot here" group, and the tables/reports that are used for fRAM are in the "RAMandLRAM backend tbls/rprts" group.


Just a reminder that "RAMandLRAM" is the table I've been referring to as "fRAM", in case that wasn't clear. Also, do remember that "RAMandLRAM" was built when I didn't have much access experience yet, I apologize for any headaches that might cause anyone who takes a look at this. Hopefully it's not too painful.

Let me know if you have any questions about it.

Thanks again for the help.

Cyber Risk Database - ForTroubleshooting.zip

[Minty]

I have had a quick look, and I think an enormous amount of your issues are down to your data structures.

If your matrix tables for risk level were normalised correctly, I think you could handle these displays and reports much easier using a dynamic cross tab type of report.
This would also preclude the need to keep moving controls around and trying to work out what form you have opened from another form.

Currently you appear to be trying to rebuild both your forms and reports around the data in the lots of different tables, and as a result have tied yourself in many many knots.
Hence the need to run a set of queries like this one;

Dim db As DatabaseSet db = CurrentDb


'Row ID = 1
db.Execute "UPDATE tLAM5x5 SET [c1] = 1 WHERE [ID] = 1"


db.Execute "UPDATE tLAM5x5 SET [c2] = 1 WHERE [ID] = 1"


db.Execute "UPDATE tLAM5x5 SET [c3] = 2 WHERE [ID] = 1"


db.Execute "UPDATE tLAM5x5 SET [c4] = 3 WHERE [ID] = 1"


db.Execute "UPDATE tLAM5x5 SET [c5] = 3 WHERE [ID] = 1"


'Row ID = 2
db.Execute "UPDATE tLAM5x5 SET [c1] = 1 WHERE [ID] = 2"


db.Execute "UPDATE tLAM5x5 SET [c2] = 2 WHERE [ID] = 2"


db.Execute "UPDATE tLAM5x5 SET [c3] = 3 WHERE [ID] = 2"


db.Execute "UPDATE tLAM5x5 SET [c4] = 4 WHERE [ID] = 2"


db.Execute "UPDATE tLAM5x5 SET [c5] = 5 WHERE [ID] = 2"


'Row ID = 3
db.Execute "UPDATE tLAM5x5 SET [c1] = 2 WHERE [ID] = 3"


db.Execute "UPDATE tLAM5x5 SET [c2] = 3 WHERE [ID] = 3"


db.Execute "UPDATE tLAM5x5 SET [c3] = 4 WHERE [ID] = 3"


db.Execute "UPDATE tLAM5x5 SET [c4] = 5 WHERE [ID] = 3"


db.Execute "UPDATE tLAM5x5 SET [c5] = 6 WHERE [ID] = 3"


'Row ID = 4
db.Execute "UPDATE tLAM5x5 SET [c1] = 3 WHERE [ID] = 4"


db.Execute "UPDATE tLAM5x5 SET [c2] = 4 WHERE [ID] = 4"


db.Execute "UPDATE tLAM5x5 SET [c3] = 5 WHERE [ID] = 4"


db.Execute "UPDATE tLAM5x5 SET [c4] = 6 WHERE [ID] = 4"


db.Execute "UPDATE tLAM5x5 SET [c5] = 7 WHERE [ID] = 4"


'Row ID = 5
db.Execute "UPDATE tLAM5x5 SET [c1] = 3 WHERE [ID] = 5"


db.Execute "UPDATE tLAM5x5 SET [c2] = 5 WHERE [ID] = 5"


db.Execute "UPDATE tLAM5x5 SET [c3] = 6 WHERE [ID] = 5"


db.Execute "UPDATE tLAM5x5 SET [c4] = 7 WHERE [ID] = 5"


db.Execute "UPDATE tLAM5x5 SET [c5] = 7 WHERE [ID] = 5"

This shouts loudly of very badly stored data and process...

I would strongly recommend you take a step back and describe to us your basic business process and the what you would like the database to manage for you.
As it stands I think you are diving down a very long one way rabbit hole.

[ItsRoland]

I am aware of the issue. This is one of the things I was talking about when I said I created this when I was starting out and need to go back in and do something along the lines of what you've recommended. Fixing that was next on my list when I hit Error 2455. I wanted to resolve the issue before trying to change anything because I wasn't sure how I was going to go about making these improvements, or if the error would still be around after the changes / get in the way of troubleshooting other things / etc.


Basically, I work for a company that gets contracted out for cyber security related tasks. The purpose of this database is to make the process of cyber risk assessment less painful to go through. Up until now it's been done on an Excel spreadsheet, requiring us to completely erase and recreate it for each client.

The specific section we're discussing here is the Risk Assessment Matrix (RAM for short). The RAM changes depending on what the client company's matrix is, each of them usually being different in one way or another, and the goal is to present them with what they're familiar with to avoid confusion. There are buttons to the left of this matrix that represent different categories of impact (Environmental, Safety, Financial, etc) and ratings (minor, low, medium, high, etc). These need to match / line up with the size of the matrix, which got a little tricky because when the report (which is the matrix) gets smaller the bottom moves up instead of the top moving down, if that makes sense. These buttons enter the associated matrix value into a table (1, 2, 3, etc) which is then used with likelihood to calculated a risk rating.

There is also a matrix for Likelihood separate from the RAM (LRAM for short), but that will always stay the same so it's much less of a hassle.



I haven't messed with cross tabs very much, but you may be right. I'm not sure if I'd be able to get rid of moving buttons if I used one though, since each category of impact has its own column of buttons (i.e. rate Safety impact at 2, environmental impact at 3, etc).

It sounds to me like the next step is to remake this if I want to get rid of the issue at hand. Assuming I'm using a cross tab type of report like you mentioned, won't the error still be there since I'm still using a report? Also, I still don't get how this worked for so long and then suddenly broke with no explanation. I understand that the code was terrible, but it was working and nothing I'm aware of caused it start giving this error. If someone could explain that to me I'd appreciate it.


I hope my explanation of the databases purpose was sufficient, let me know if anything was unclear. Thanks again for the help!

[Minty]

That's an excellent explanation. This may be one of those instances where the nature of your data and the customers requirements require a "hybrid" solution.
Your core data and records should probably be in Access. It handles data correctly and can easily enforce business rules in a way that Excel simply can't do.

What you might need out of the system is possibly more suited to displaying or reporting on in Excel, which can be achieved using automation from within Access.
I'm not saying you can't build the reporting in Access, it's just somethings aren't worth the effort if they can easily be achieved with Excel based on sensible data storage from Access.

From where you are at the moment I would be tempted to genuinely start from scratch with a pen and paper, you are in a good position where you know what hasn't been working well from your Excel experience, and what hasn't worked well from your Access experience so far.


As for your immediate problem I'm not sure it's worth spending the effort trying to fix, unless you can upload a previous backup that did work, and tell us what you tried to achieve with the code you added.

[ItsRoland]

I agree with you on this, even back when I was first creating the matrix functionality I was thinking it should be done through something other than access.

The only problem is it's not up to me, it's up to my boss, and he's been pretty adamant we stick with only access. I see where he's coming from though, sometimes this process can take days and I think he's trying to save as much time as possible by minimizing the number of clicks / seconds everything takes. Toward the end of the process the clients get pretty fatigued and we have to carry any form of discussion, which can degrade the quality of the end result, so the less lengthy the process is the better is it for everyone.

I personally don't have very much Excel experience as I wasn't the one who created / maintained the spreadsheet (I didn't even have any access experience when this project was given to me...).

The desired end result is the RAM form/sheet acting as a popup when you click on a button associated with a record on a continuous form. The RAM pops up and you click the desired impact / likelihood for that scenario, it gets thrown into the tables, you close the popup and move on the the next one. Is there a way to achieve a similar experience by using Excel like you mentioned?

I'll do some research on using Excel with Access, if it can be used in a similar way to the desired outcome I can probably get the go-ahead.

[Micron]

I think the issue arises from putting a report in a subform control. I've tried this in the past out of curiosity and never got it to work, assuming the reason being that Access can't handle it. Perhaps when creating the hierarchy syntax, the processing of it is looking for a report when it's on a report, and a form when it's on a form, and it blows up when that isn't the case. I did see a thing here https://access-programmers.co.uk/for...ad.php?t=78237 about it being possible with API calls; maybe you don't want to go there.

Haven't absorbed 100% of the business requirements you posted, but there are probably easier approaches. Will review again later. In the meantime, maybe the link will help. Also, this db won't compile, at least due to your incorrect use of the Call keyword. If used, the called procedure must be in () and maybe in quotes. Can't recall for sure. Simply writing the procedure name will do when that procedure doesn't return any value. I scanned some code; there's probably lots of room for improvement (not meaning to be nasty here) such as not having useless GoTo's such as GoTo skip_filter, when that simply results in an end to the procedure. Exit Sub would do the same and is generally considered better then GoTo's (as would be a lot of similar uses for GoTo). I probably won't review the code further because of what seems like the possibility that you'll change course here.