How can I set the textbox on a subform in Datasheet mode?

[HillChris7714]

So, I have a (Split) Form that contains a SubForm. On that SubForm, I have a text box that is assigned by a VBA module, like so:

Private Sub Form_Current()
    Dim frm As Form
    Set frm = Forms![Form1]![Table2 subform].Form
    frm.txtCalculated = "SUCCESS!"
End Sub

The Field is calculated on the bottom portion of the split form, but not in the top portion:



My 2 Questions are...

1. Why? What do I have to do to set the text box on the top portion as well? Is that even possible?

2. How come, when I go into Datasheet view (by clicking View > Datasheet), I get the following error on the following line of code:

[ranman256]

You dont create a new form in vb, it's already there physically.
so assign the subForm object to the existing. But you don't even need vb. just assign it in form design.
use full paths.

[HillChris7714]

You dont create a new form in vb, it's already there physically.
so assign the subForm object to the existing. But you don't even need vb. just assign it in form design.
use full paths.

Well see, for a variety of reasons which are all very boring, the field has to be assigned in VBA... let's just say there's an über-complicated calculation that assigns the value of that field. Essentially, I'm just trying to figure out how to make sure the form that appears in the top portion behaves the same way it does in the bottom half.

As far as the error is concerned, for now I'm just disabling Datasheet view. Which is kind of hammy to me... they're trying to use Datasheet View, they're getting an error, I can't fix it, so I just disable Datasheet View. I mean, that doesn't fix anything really. They still can't use Datasheet View - but, now it's by design.

[ItsMe]

1. Why? What do I have to do to set the text box on the top portion as well? Is that even possible?

What is this top portion you are referring to? If you have a control in the main form that you want to assign a value to, use something like the following in the main form's module.
Me.ControlName.Value = "Some Value"

2. How come, when I go into Datasheet view (by clicking View > Datasheet), I get the following error on the following line of code:

Apparently, that feature/option is not available while in DS view.

[HillChris7714]

What is this top portion you are referring to? If you have a control in the main form that you want to assign a value to, use something like the following in the main form's module.
Me.ControlName.Value = "Some Value"
Apparently, that feature/option is not available while in DS view.

Can you not see the screenshots attached to the OP? The big red question mark with the arrow points to exactly what I'm talking about. Not to be a smart aleck, but I don't know how to make it more clear...

And, as far as the feature/option not being available... you can see the subform on datasheet view just like you can on the top portion of the split form. It's there. So, it must be available. All you have to do is open a Form that has a subform and switch to Datasheet view. You see the plus sign on the left? That expands to show the subform. The subform is there. Why can't I reference it? That's the gist of the issue.

[HillChris7714]

Maybe this will help. Here's a link to a test Database where you can actually see what I'm talking about: http://expirebox.com/download/bbf0dc...edc14cec5.html

Load Form1. Notice the Calculated Field is set to "SUCCESS!!!" Yet, if you expand the record above, you'll see the same field is not populated.

Also, click View > Datasheet. You will then see the error in question.

[ItsMe]

Can you not see the screenshots attached to the OP?...

Yeah, your pictures are posting OK. I understood that control as the one you referenced in your VBA code, where you assigned a value. If there is another control you need to assign a value to, you just need to get the correct namespace. That is why I included the example using the Me shortcut (Me.ControlName.Value = "Hello world")

I did not have time to test the Datasheet thing. IIRC, you will not be able to reference a container in a form while in DS view.

[HillChris7714]

Well, that sucks. I mean, I appreciate you telling me straight up that it can't be done. I've gone through a bit of a headache trying to find an answer to this question. And, to this point, what I've gotten is a bunch of attitude from people about how I'm asking the question, and "why would you want to do that?" and... basically trying to make it MY fault that I'm asking the question. When, as I suspected, it's probably because they just don't want to admit that Access isn't sufficient to accomplish what I want, so somehow it's my fault.

I mean, I'm not the architect of this solution. I'm not the Project Manager... basically I'm a .Net MVC developer who was handed this to basically patch up until we can do a full re-write. So, "It can't be done" is totally acceptable in my eyes. I can just go back to my PM, tell them it can't be done, and people will just have to deal until we can rewrite the app in a more comprehensive development framework. Personally, I would never recommend Access to do pretty much anything. If it's too big for Excel, it needs to be in SQL with a .Net or PHP front-end. I didn't recommend the app be written in Access, so I'm not accepting responsibility for its limitations.

They would probably actually be upset if they found out how much time I burned trying to fix this (seemingly) simple problem.

[orange]

I'm not sure I follow what you are trying to do.
I did download your database, and established referential integrity through relationships (1 to many between table1 and table2 id-->TID)

I added a textbox Text5 on form1 and modified code as below

Private Sub Form_Current()
    Dim frm As Form
    Set frm = Forms![form1]![Table2 subform].Form
    frm.txtcalculated = "SUCCESS!!!"
    Forms!form1.Text5 = frm.txtcalculated
End Sub

and form displays