none
Error when referencing Subform in Datasheet View RRS feed

  • Question

  • I have a form that, by default, loads in Form View as a Split Form. It is called "myFormA," and has a subform called "myFormB."

    When you change the view to Datasheet View (via clicking View > Datasheet, or clicking the "Datasheet View" icon on the lower right), I get the following error:

    Quote:
    You entered an expression that has an invalid reference to the property Form/Report
    ...this is the result of line 2 below (the "Set" assignment):

    Code:
    Dim frm As Form
    Set frm = Forms![myFormA]![myFormB].Form
    I don't get this error when loading the form (in Form View). But, I do get this error when changing to Datasheet View (via the method mentioned above). 

    The reason this error doesn't make sense is, when I change to Datasheet View, the records in the Datasheet View all have a little plus sign on the left, which, when you click that plus, the record expands to show the subform in question. So, the subform is THERE in the datasheet view. And, I know the assignment works in Split Form View. So, why does the above Set command have a problem in Datasheet View?
    Friday, August 12, 2016 8:05 PM

Answers

  • Without really knowing anything about your database, I can only offer general advise. For example, if you're trying to perform calculations against the records in the subform, then perhaps you might be able to perform those calculations against the  table records to which the subform is bound. Just a thought...
    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:30 AM
    • Marked as answer by David_JunFeng Tuesday, August 23, 2016 1:16 AM
    Monday, August 15, 2016 9:32 PM
  • Hi. If your subform is bound to a table, you should be able to "set" the value in the table. No?

    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:29 AM
    • Marked as answer by David_JunFeng Tuesday, August 23, 2016 1:16 AM
    Tuesday, August 16, 2016 1:49 AM

All replies

  • Hi. For what are you using the variable frm? When you switch to Datasheet view, some form controls are not rendered. For example, a command button (in the Details section) won't show up in Datasheet View. My guess is a subform control is the same way. Just my 2 cents...
    Saturday, August 13, 2016 2:41 PM
  • >>>The reason this error doesn't make sense is, when I change to Datasheet View, the records in the Datasheet View all have a little plus sign on the left, which, when you click that plus, the record expands to show the subform in question. So, the subform is THERE in the datasheet view. And, I know the assignment works in Split Form View. So, why does the above Set command have a problem in Datasheet View?<<<

    According to your description, I have made a sample to try to reproduce this issue, unfortunately, I am not able.
    Private Sub Command11_Click()
       Dim frm As Form
       Set frm = Forms![FM_Teacher]![FM_Student].Form
       MsgBox frm.Name
    End Sub



    So I suggest that you could provide more information about this issue, for example screenshot, that will help us reproduce and resolve it.

    Thanks for your understanding.
    Monday, August 15, 2016 7:01 AM
  • The error is occurring when you switch to Datasheet mode.  So, go up to View and select Datasheet.

    Monday, August 15, 2016 5:02 PM
  • I wonder if my post above explains why you're getting this error. Besides, what exactly do you need to do with the subform object in your code, if you were able to grab a copy of it?
    Monday, August 15, 2016 5:08 PM
  • There are a few calculated textboxes on the subform, and I assign those values in a VBA module. So, the ACTUAL code you're seeing isn't the code I'm using in my implementation... though it throws the same error for (presumably) the same reason. FWIW, the controls in question are list boxes.  It seems really lame that certain controls wouldn't render on a subform in datasheet mode (not that I don't believe you).  I mean, it's the same form... same controls, same look and feel.. why would I not want it to behave the same way in Datasheet View that it does in Form View?
    Monday, August 15, 2016 6:23 PM
  • Hi. I am just guessing on why MS decides on certain things, but I suspect "they" built single/continuous forms and datasheets to serve different purposes and therefore may have decided to make them behave differently. Just a thought...
    Monday, August 15, 2016 6:33 PM
  • So, would you say the conclusion here is to just go back to the PM and tell them that the fields shouldn't expect to be calculated while in Datasheet mode, and that they'll just see an error whenever they try to use it?
    Monday, August 15, 2016 7:12 PM
  • Actually, the solution might be to modify your code to use a different approach when the user switches to Datasheet mode. Just my 2 cents...
    Monday, August 15, 2016 7:24 PM
  • Yeah, well... I don't even like using Form_Current, but it was really the only option to accomplish the task given the scenario.  So, rewriting a loophole for use in Datasheet view isn't really an option.  The accdb is a monstrosity that has outgrown what is appropriate for Access.

    I mean, what is a different approach?  If I can't access a subform's controls in Datasheet view... what other approach could there be?

    Monday, August 15, 2016 8:33 PM
  • Without really knowing anything about your database, I can only offer general advise. For example, if you're trying to perform calculations against the records in the subform, then perhaps you might be able to perform those calculations against the  table records to which the subform is bound. Just a thought...
    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:30 AM
    • Marked as answer by David_JunFeng Tuesday, August 23, 2016 1:16 AM
    Monday, August 15, 2016 9:32 PM
  • What if I just wanted to set the value of a text box to say "Hello World?"  No calculation, just raw text.  If I can't reference the form, how am I going to reference the controls on the form?
    Monday, August 15, 2016 11:02 PM
  • Hi. If your subform is bound to a table, you should be able to "set" the value in the table. No?

    • Proposed as answer by David_JunFeng Monday, August 22, 2016 9:29 AM
    • Marked as answer by David_JunFeng Tuesday, August 23, 2016 1:16 AM
    Tuesday, August 16, 2016 1:49 AM