Referencing a control in the Record Source of a Subform

[Kluaoha]

I have a continuous subform (FormB) within a form (FormA), and I also have a text box in FormA. I want to use a WHERE clause in the Record Source of FormB to filter records based on the text in the text box, like so:

SELECT * FROM [MyTable] WHERE [MyTable].[MyField] LIKE Me.Parent![MyTextBox].Text & "*";

Unfortunately, Access isn't finding the control, prompting me to input the value of Me.Parent![MyTextBox].Text


I also tried indexing from Forms, with the same result.
Any help is appreciated. Thanks!

[aytee111]

Use the full name - Forms!FormA!MyTextBox

[pbaldy]

Two additional thoughts. "Me" is only valid in VBA code, will error anywhere else. I wouldn't use the .Text property, which requires focus. More here:

http://www.baldyweb.com/ValueText.htm

[Kluaoha]

Hmm I tried indexing from Forms as well. Perhaps I am doing it wrong. FormA is inside a Navigation Control of yet another form:

... LIKE Forms![MainForm]!NavigationSubform.Form![FormA].Form![MyTextBox].Text & "*";

Also, the reason why I use the Text property is because this is requeried on the text box's Changed event. However, it is also requeried on the Current event of a different subform (sibling to FormB); there is another WHERE condition I have left out dealing with that subform. So the question is: how can I get the "text" from the Changed event while the user is typing, but the "value" from the Current event (without getting the text box's focus)?

[Micron]

Access isn't finding the control

When? The subform loads first. It can't get a value from a form that isn't loaded yet. But that can't be it since you seem to be trying to get a control's Text value.
Parent is a reserved word and shouldn't be used in this manner. Suggest you bookmark this page http://allenbrowne.com/AppIssueBadWord.html
Not sure I get the last question. You don't need to give the focus to a control to get its value (which is the default property for a textbox) so what's significant about a Current event and getting a .Value?

FormA is inside a Navigation Control of yet another form

Don't get this either. You don't mean navigation form do you? To me, navigation controls are what you use to move from one record to another and are built in. You can't put anything in them, can you? Have to ask as I'm just playing with 2016 lately and try not to take too much for granted.

[Kluaoha]

I'll try to explain this better.

When? The subform loads first. It can't get a value from a form that isn't loaded yet.

This might also be an issue, but regardless it still can't find the control: when the form loads and Access prompts for the text box's text since it can't find the control (perhaps because it isn't loaded yet, as you say), if I type in whatever text I want and hit Ok, I then proceed to enter text in the actual textbox and the prompt shows up again with each character I type (requeried from the Changed event).

You don't need to give the focus to a control to get its value (which is the default property for a textbox) so what's significant about a Current event and getting a .Value?

According to pbaldy's response, you must focus on the text box to read its Text property. For the Changed event of the text box, this is no problem since the user is already typing in the text box anyway. However, I have another means of requerying FormB's Record Source through the Current event of a different continuous subform. But when this event fires, the user is focused on that subform and therefore not focused on the text box, so the Text property can't be fetched and I would have to use the Value property instead.
The only two solutions I can think of are to

a. Have an entirely separate query that is identical to the first except change "Text" to "Value" in the LIKE clause, and then switch between these queries depending on which event occurs.

b. Store the text box's Text as a global variable, update it in the Changed event, point the LIKE clause to a function that retrieves this variable.

Neither is that great, but I prefer b over a.

Don't get this either. You don't mean navigation form do you?

Yeah, I think I meant Navigation Form. I was unsure how to index this because for each "tab" the form is still called "NavigationSubform", even when they contain different subforms.

[Micron]

I guess I'm just not following your form's process. I'm saying the subform of a main form opens and loads first. When either form loads, there is no text to get since it's just opening/opened so I don't get why you're trying to retrieve that. If you're getting prompted on form opening, then a reference being used cannot be resolved. Usually that's because of
- misnamed form controls
- incorrect query references to a control
- the form containing the needed parameter (control value or otherwise) isn't open at the time of the call
- the Text property of a control used for a parameter doesn't have the focus
If it were a matter of the Text property being empty or null (assuming the control has the focus) then you wouldn't get a parameter prompt.

You should be wary of using the Text property since a bound control can actually have 3 different data properties - OldValue, Text and Value, and they can all be different.

As I mentioned, I've just started using 2016, and there are probably design features that I will never use given the problems I've read about their use. I see now that M$ uses the terms "navigation control" and "navigation form" interchangeably or at least in the same sort of context. Navigation controls were always the back/forth record navigation buttons where record number and count showed. It's my understanding that a nav form can only display one form at a time (which means you open one, you close the other whether you want to or not) and problems can arise when using these where forms have subforms. You end up with subforms on forms on subcontrols on a nav form. I'd make my own switchboard and forget all that junk, but that's just my opinion.

Did you try setting the parent/child linking fields between the main form control and the subform containing that same data and just letting form properties do the work?

[Kluaoha]

I hope this helps:

So I essentially want to display records on FormB that are related to the selected record in FormC and whose NameB fields start with the text in the textbox:

SELECT *
FROM [TableB] INNER JOIN [TableC] ON TableB.C = GetGlobalVar_idC()
WHERE TableB.BName LIKE [TextBox].Text & "*";

'Globals
Dim GlobalVar_idC As Long
Public Function GetGlobalVar_idC() As Long
    GetGlobalVar_idC = GlobalVar_idC
End Function

'FormC
Private Sub FormC_Current()
    GlobalVar_idC = Me.idC
    [FormB].Requery
End Sub

'FormA
Private Sub TextBox_Change()
    FormB.Form.Requery
End Sub

Since this query depends on both of these values, I have the Current event of FormC and the Change event of the textbox requerying FormB's Record Source.