Proper Syntax for Referencing forms/controls/etc from VBA

I am using a navigation form in Access and have had trouble referencing controls because access view the navigation form as a subform when it is created.  With extensive help from this site previously I was able to get the references working on one of the forms contained in the navigation form.  Now that I have moved on to the next form which was also working perfectly prior to being added into the navigation form, it now does not run any of the code I had programmed and my assumption is its because it doesn't understand what control I am referencing.  This time however I don't get a pop up asking for the criteria, instead the code just doesn't run.  I don't know why this is so hard but I need help again figuring out why this stupid thing doesn't understand what I am saying anymore.
Who is Participating?

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

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.

yoducatiAuthor Commented:
One slight update.  I put in a msgbox in the OnClick event of the sumform I am working with in front of all the code I am trying to get working.  At first I thought it was still not working until I clicked repeatedly (possibly in frustration because I am ready to throw this computer out the window) and discovered that the OnClick event is only running if you click in a particular place on the subform.  It doesn't just run when you click into one of the controls you have to actually click the form outside the controls.  Moved the code to the OnGotFocus event and now nothing.
Rey Obrero (Capricorn1)Commented:
do you have [Event Procedure]  selected in the Click Event in the property sheet?
The best reference resource I know of is on The Access Web
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Certain form level events such as click do not fire if a form has controls that are able to accept the focus.  That is why the form's click event wasn't responding for you.

When code in a form references controls on that form, use the "Me.mycontrolname" syntax.  The only time you use the specific Forms!yourform!yoursubform.form!control reference is to refer to controls that are on some form/report other than the one running the code.

So, moving a form onto the navigation form would technically make it a subform, it will not impact your code if you use "Me." to reference controls.
yoducatiAuthor Commented:
yes, [event procedure] is in the event in the property sheet.  I have the access web reference sheet but none of the combinations I tried works.  I think I have narrowed the issues with this form down to two problems.

First is getting the code to run when the user clicks on the subform, or a control in it.  That's not happening now.  OnGotFocus of the subform does nothing.  OnCLick of the subform only runs when the user clicks the subform outside of any of the controls on it.

Second is getting access to run my query with the criteria from the form like I was trying to do on the other form.  

When I delete the references from the query criteria the query returns results and vba simply grabs the results of the first record but that's not the correct record.  

With the appropriate reference from the form as the criteria the query will always return only one record.

With the reference in the criteria as I have it now returns no records at all incorrectly.    

The only thing I can think of is that the difference with this form being on the navigationsubform is that this one is itself a form/subform and the one you guys helped me with before was just the navigationsubform with a form.  I think I need to drill down one more level in the reference but I cant figure out how to do it.  So for this situation I have as follows


For the query criteria I need to reference the field [Combo32] on frmOrders, and the field [Combo16] on sfrmOrderDetails
Rey Obrero (Capricorn1)Commented:
try this one first


post back the result..
yoducatiAuthor Commented:
Ok this is correct.  When I put this in as the criteria it filters the query by the correct value.  Just to make sure the same value was being passed from the form I added a text box to the form to temporarily show me what was being used for the query criteria and its passing the correct number using that syntax.
Rey Obrero (Capricorn1)Commented:
ok, now try this


post back the result
yoducatiAuthor Commented:
I get the error I was getting before on this one.  Access thinks "navigationSubform" is a field and throws the error

"can't find the field 'navigationSubform' referenced in your expression"
yoducatiAuthor Commented:
oh there are two navigationsubforms in the reference so should the second one be sfrmOrderDetails?
Rey Obrero (Capricorn1)Commented:
how is "sfrmOrderDetails" nested in "frmOrders", just a normal subform?

is th name of the subform control the same as "sfrmOrderDetails" ?


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
yoducatiAuthor Commented:
yep that was it.  I changed it when I saw your post above had [navigationSubform] twice.  I figured the second one must need to be the name of the form with the control [Combo16] so I switched it and it worked.  Im not sure what I was doing before but something about my syntax kept throwing the same error saying it was thinking the form I was trying to reference was a "field".  I still don't know what I was typing wrong but its working now.

Any ideas on the event part of this?  Pat I saw your post above which is good to know.  I assumed that as soon as the user clicked in the subform whether it was inside or outside of a control that the onclick would fire.  Here is what I am trying to do...

I have the orderform with the details subform.  On the ordfer form there is only [OrderNumber] [Vendor] and [Property] for the user to select the property for which they are placing the order and the vendor from whom they want to select items.  

When the user clicks into the details subform I want the code to check and see if [Vendor] and [Property] are blank and if so it gives them a message and sets the focus back to the appropriate field so they cant enter anything in the subform with either of those fields blank.  

When they click into the subform if those fields are not blank they become locked.

All of this was working perfectly until I added the orderform to the navigation form.  Now none of those references are working.  I tried "me.fieldname", "me.parent.fieldname".  I even tried "me.parent.parent.fieldname" but I knew that wasn't right because it would be going up too many levels but I wanted to see if I could get a reaction at all.  None of that code is running correctly right now from the navigation form.

When I put the references back to me.fieldname and run it from the regular form it checks for nulls as it should but the event still doesn't work exactly right.
yoducatiAuthor Commented:
Ok I think I have been staring at this screen too long.  I figured out part of it.  When I moved the form to the navigation form I had to change from me.fieldname to me.parent.fieldname but what I had written was me.parent.[fieldname] so it wasn't working.  When I took the brackets out it started working again.  

So now I have the crazy references for the query working correctly (Thanks again Rey!) and now I've got the much more simple me.parent.fieldname references working.  

I still can't get the event part to work right though.  Unless I put the code in the onClick event of subform, and each field on the subform, which sounds cumbersome, I can't figure out where to get this to fire so the interaction for the user is correct.
Rey Obrero (Capricorn1)Commented:
I figured the second one must need to be the name of the form with the control [Combo16]

it is not the name of the form with the control that you will use,

it is the name of the subform control that you will use

it just so happen that the name of the subform is the same as the name of the subform control
Use the BeforeInsert event of the subform.  Since the subform itself will not respond to "clicking", you need to use some other event that will always run when you need it to.  Since the BeforeInsert runs as soon as the first character is typed in the subform AND it only runs for NEW records, it is a good place to check the primary key on the parent form to ensure that it is populated.  If it is, the parent record exists and so it is OK to add a child record.  I use this technique on every subform that allows ADDS.  It allows me to give the user a meaningful message rather than the strange errors that Access will throw if the user opens an empty form and starts typing in the subform rather than in the main form.
If IsNull(Me.Parent.myPK) Then
    Msgbox "Please add a parent record first",vbokonly
    Cancel = True
    Exit Sub
End If

Open in new window

yoducatiAuthor Commented:
Works much better in the beforeInsert event!  Thanks!
You're welcome:)
Events are not arbitrary.  The Access development team created each of them to serve specific purposes.  Learning what each event is intended for will go a long way toward making your apps easier to create and more effective at validating data and preventing errors.  Using the wrong event either leads to holes you can drop an elephant through or duplication of code to try to plug all those holes.  I came to Access from a procedural language (COBOL) and so getting the hang of how to use events was a challenge at first but once I understood what caused them to fire I had a better understanding of what they should be used for and why code would work in one event but not another.
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.