What is the proper sytax to reference a form on a tabbed navigation form in access 2010?

I have a query that has criteria based upon the users input for the field Invoice Number on the invoice entry form.  Everything works perfectly when done from the invoice entry form directly.  When accessed through the main navigation form (upon which the invoice entry form is a tab) I get the popup dialog asking for the invoice number because it doesn't understand the reference to it which was previously Forms!frmInvoiceEntry.[InvoiceNumber].  I quickly realized that error was because I was accessing the form through the navigation form and needed to change the reference.  I have changed it to the reference below but I still get the dialog.

[Forms]![frmPropertyNavigation]![frmInvoiceEntry].[Form].[InvoiceNumber]

My assumption is that this is because I am only referencing the form and not pointing to the appropriate tab but I don't know how to do that correctly.  I need the appropriate syntax for referencing the control [InvoiceNumber] on the form [frmInvoiceEntry] which is one "subtab" of my Data Entry tab on my access navigation form [frmPropertyNavigation].
yoducatiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
The tab is only a placeholder, thus:

[Forms]![frmInvoiceEntry].[Form]![InvoiceNumber]

where frmInvoiceEntry must the name of the subform control, not the form itself.

/gustav
yoducatiAuthor Commented:
I still get the same error saying it cant find the form.
Gustav BrockCIOCommented:
What are the names of the subform control and the subform?

/gustav
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

yoducatiAuthor Commented:
See above just to make sure we are on the same page.  Its not really a subform (unless access considers forms on a navigation form subforms).  

Navigation Form = frmPropertyNavigation
Invoice Entry Form = frmInvoiceEntry
Control = [InvoiceNumber]
Gustav BrockCIOCommented:
And the name of the subform control? That's what you need.

/gustav
yoducatiAuthor Commented:
Im not sure what you mean by 'subform control'.
Gustav BrockCIOCommented:
It's the control holding your subform.

> where frmInvoiceEntry must the name of the subform control, not the form itself.

/gustav
yoducatiAuthor Commented:
I don't have a subform control as far as I can tell.  I am using the built in access navigation form.  frmInvoiceEntry is the name of the data entry form.  [InvoiceNumber] is the control on that form I am trying to reference.  frmInvoiceEntry is on one page of the navigation form called frmPropertyNavigation.  There isn't another subform control like you would have with a normal form/subform.  Not that I can find anyway.
yoducatiAuthor Commented:
http://answers.microsoft.com/en-us/office/forum/office_2010-access/refer-to-form-control-from-navigation-form/11360cfa-057b-4787-a949-025cf2bb6b7b

I just found this link searching elsewhere.  Apparently this is a common issue.  Im still not sure I understand how to make the reference but this is the same problem I am having.  Im too far into development to change the user interface now so I need to figure out how to make it work.
Gustav BrockCIOCommented:
Create a button on the subform with this click event:

    MsgBox Me.Parent

That should reveal the name.

/gustav
Gustav BrockCIOCommented:
OK, then it should be:

Forms!frmPropertyNavigation![name of the subform control].Form!InvoiceNumber

/gustav
yoducatiAuthor Commented:
The response I get is

Run-time error '13':
Type mismatch
Rey Obrero (Capricorn1)Commented:
try this

 [Forms]![frmPropertyNavigation]![NavigationSubform].[Form]![frmInvoiceEntry]![InvoiceNumber]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
>   Type mismatch

You should click when the form has been opened from the navigation form.

/gustav
yoducatiAuthor Commented:
I did.
yoducatiAuthor Commented:
Rey, I tried your suggestion but I still get the same error.  For some reason from the navigation form it just doesn't understand what I am referencing.
Gustav BrockCIOCommented:
Strange. Is the form opened by itself? if so:

[Forms]![frmInvoiceEntry]![InvoiceNumber]

/gustav
yoducatiAuthor Commented:
That's what I have in the query criteria when the form is opened by itself and it works perfectly.  If I leave that as the criteria and open it in the navigation form it no longer works.  I didn't realize that the navigation form actually just inserts a control called "navigationsubform" so Gustav that's probably what you were looking for as me.parent.  Rey I think your syntax should be correct according to that article considering the name of the subform control is literally "navigationsubform" but it still isn't working.
Gustav BrockCIOCommented:
I see.
I don't use navigation forms, so except if you could upload a sample, I'm out of ideas.

/gustav
Rey Obrero (Capricorn1)Commented:
try this test, create a command button in the main form,

in the click event of the button, place this code

DoCmd.BrowseTo acBrowseToForm, "frmInvoiceEntry", "MainMenu.NavigationSubForm"


that should direct you to the subform "frmInvoiceEntry"
if not, then check for the name of the source control for your subform, and use it in your codes
yoducatiAuthor Commented:
I tried screen.ActiveForm![InvoiceNumber] and now it lets the code run without errors but it doesn't produce the correct query results.
Gustav BrockCIOCommented:
So what does Screen.ActiveForm.Name reveal? That would be the name to use.

/gustav
yoducatiAuthor Commented:
I don't know how to trap that.
yoducatiAuthor Commented:
Should I put that behind the button instead of the msgbox?
Rey Obrero (Capricorn1)Commented:
yoducati,

read my post at http:#a40783451
yoducatiAuthor Commented:
ok I put that behind the button and when I click it I get frmPropertyNavigation
yoducatiAuthor Commented:
Sorry Rey, Im working on that too.
yoducatiAuthor Commented:
Ok Rey, I tried your suggestion.  Here is a screenshot of the error I get.....
yoducatiAuthor Commented:
Rey Obrero (Capricorn1)Commented:
<ok I put that behind the button and when I click it I get frmPropertyNavigation >

then we are looking on a different sub form..

open the main form in design view,
locate the subform "frmInvoiceEntry"
select the subform Control and hit F4  (the control that house the subform)
look at item listed in the NAME property, use that name in your code
yoducatiAuthor Commented:
Its NavigationSubform
yoducatiAuthor Commented:
So now we know the main form is

frmPropertyNavigation

the subform control is

NavigationSubForm

the form with the control I want to reference is

frmInvoiceEntry

and the control name is

InvoiceNumber

Shouldn't the reference you posted previously work?  From the article it seems correct so I still don't understand why it doesn't work.
Gustav BrockCIOCommented:
Then it ought to be, though I believe you did try that:

Forms!frmPropertyNavigation!NavigationSubForm.Form!InvoiceNumber

/gustav
Rey Obrero (Capricorn1)Commented:
do a compact and repair
then, on the VBA window do
Debug > Compile
correct any errors raised
yoducatiAuthor Commented:
Did the compact and repair, and complied.  no errors.
Rey Obrero (Capricorn1)Commented:
my mistake in the code i posted above, try this one


DoCmd.BrowseTo acBrowseToForm, "frmInvoiceEntry", "frmPropertyNavigation.NavigationSubForm"
yoducatiAuthor Commented:
Well what a nightmare this has been.  Its working now.  I did try that reference before and got the same error.  I'm assuming something changed now that I did the compact and repair and compiled but I didn't get any errors at all.  I put that reference back in and tried the form from the navigation form and for the first time ever it worked.
Gustav BrockCIOCommented:
Great!
What reference did work in the end?

/gustav
Rey Obrero (Capricorn1)Commented:
try this too

Forms![NavigationForm].Form![NavigationSubform].Form![frmInvoiceEntry]![InvoiceNumber]
yoducatiAuthor Commented:
This is the one that worked :

[Forms]![frmPropertyNavigation]![NavigationSubForm].[Form]![InvoiceNumber]

If ever there was a team effort it was this one.  Im going to split the points right down the middle because you both helped a lot.  I think the biggest issue was not knowing that access created the subform control and named it "NavigationSubForm".  I made the navigation form with a wizard so I didn't realize it was really just a form with a subform control that then housed my data entry form.  Not that that makes it more straightforward but at least now I know how to refer to the controls.  Thanks again to you both!
Gustav BrockCIOCommented:
You are welcome!

/gustav
yoducatiAuthor Commented:
I am having the same problem again but on a different form.  Same scenario though.  The one you guys helps me with works fine from the navigation form but I cant get the references to work on the new one.  I cant even get it to put up a msgbox at the point I want it to so I can start narrowing down the problem.  None of my code runs at all.  Anyway I posted a new question here....

http://www.experts-exchange.com/Database/MS_Access/Q_28677794.html
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.