• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 956
  • Last Modified:

DLookup in a subform

I have a subform embedded in a form.

I want to use the DLookup function on a field contained in the subform.  I'm getting various errors that I believe are due to the fact that I am incorrectly referencing the fields in my subform.

I'm reasonably certain this is the case because if I open the subform by itself, my DLookup function works correctly.  It's only when I open the master form that I run into problems.

I believe my question may not even be specific to the use of the DLookup function so could someone guide me on how to properly reference fields when using/creating functions that reference fields in a subform. i.e. do I need to (and if so, how) reference the master form in my function?

And is there is difference in referencing fields when the subform is formatted as a datasheet vs. continuous forms or even single form?

  • 5
  • 3
1 Solution
What you have to remember is that a subform is primarily a control on the container form, and that it is a control of type Form.

From the container form, you can access controls on the sub form using [frmSubForm1].Form![Value_Y] syntax, which means with frmSubForm1 control's Form property get Value_Y control's value.

From the sub form you can reference the container form using Forms![FormName]![ControlName] in most places, you can also use Me.Parent in VBA.

Hope that helps, do you want to post your DLookup, sure we can assit in helping make it work the way you want it to.

mbath20110Author Commented:

Thanks so much for the quick answer.  Let me take a crack at putting your solution to work.  If I'm still struggling, I'll certainly call on you for added assistance.

I've got til this Thursday to provide my client with the latest progress so hopefully I can get your solution to work by then.

Thanks so much.
mbath20110Author Commented:

Decided to take you up on your offer.  Here's my DLookup up situation and details:

Main Form: frmCommunity
SubForm: frmsubHouseType

The table that I want to use my Dlookup function on is called: tblHouseName and the two fields being used are [HouseName] and [BrickFront].

In frmsubHouseType, I have a field called [cboHouseType].

In frmsubHouseType, I'd like to be able to select a value from [cboHouseName] and in the AfterUpdate event, run the DLookup that goes out to tblHouseName, finds the corresponding value in [HouseName] (the value that matches [cboHouseName]) and return the value contained in [BrickFront].

I then want to take the value in [BrickFront] (numeric) and assign it to a field called [Option1], which is also contained in frmsubHouseType (although I think this is the easy part. I'm sure that if I can get my DLookup to work, I'll have no problem assigning the value to [Option1].

As I said in my previous post, my Dlookup function works like a charm if I open my subform by itself.  It looks as follows:

Me.Option1 = DLookup("BrickFront", "tblHouseName", "HouseName = Forms!frmsubHouseType!cboHouseType")

But when I open the master form [frmCommunity] I get all manner of runtime errors.  So, as you stated, it seems that I am not correctly referencing my subform fields when they are embeded in frmCommunity.

please let me know if I've left out any pertinent details.

Thanks a mil.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Sorry, somewhat overwhelmed by your description. From what I can gather the field [HouseName] and the data in cboHouseType are the same as you have informed that frmsubHouseType works when its opened on its own.

A subform, being a control of its container, and the method of referencing a controls form property to see its controls, as outlined above can be concatenated, to become Forms![FormName]![frmSubForm1].Form![Value_Y]

Me.Option1 = DLookup("BrickFront", "tblHouseName", "HouseName = Forms![frmCommunity]![frmsubHouseType].Form![cboHouseType]")

This is because you want Access to look for the instance of the frmsubHouseType that is inside frmCommunity.  The original was looking for an separate instance of the form!

Hope this works, I haven't tested it by the way! :)
mbath20110Author Commented:

Sorry for the overload but, yes, I think you've interpreted the jist of what I'm trying to do.

I think what's throwing me or what I'm missing is the syntax of the subform when embeded.  You may or may not agree, but it doesn't seem to me to be intuitive to append the word 'form' after frmsubHouseType.

I've been working on the assumption that Access knows I'm talking about a subform when I start out the string with Form so I've been trying to say [Forms]![MainForm]![SubForm]![Control] but I see you've included 'form' twice.  That appears to be the piece I'm missing.

I'm going take it for a spin.

Thanks so much again for your help.
I do agree with you, but there is logic behind the way that it works, because a Form can only contain Controls. So if you want a Sub Form, then it has to be a Control which is an instance of a  Form, hence the additional .Form! which is telling Access a "control which is a form"! Dumb, but necessary.

One of the easiest ways to get it right is to try use the Build functionality as often as you can, so then you need to refer to a control in this way Right Click, select Build, then you get this wonderful dialog that allows you to pick Forms;LoadedForms;FormName;ControlName etc etc etc It's so handy!

Good luck
mbath20110Author Commented:

Am a frequent user of the Build dialog box.  Great for simpletons like me who try and force their own will on Access as opposed to playing by the rules.

Well, I think I'm closing in on getting this right, but I'm getting another error message which may or may not be outside the scope of this thread.

If so, I'd be happy to open this latest twist as another thread and assign more points.

Put in your form string as you recommended but when I test it from the Master Form (frmCommunity), the following error message is generated when I select an item from my drop down in the subform:

'the expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object

I have checked and rechecked my form and field names and am confident I've got them right.

Researched this error in this forum and found entries suggesting that the problem may be related to Master & Child field links.  I DO have a relationship created between the Master and Child table and have enabled all referential integrities.

any thoughts???
mbath20110Author Commented:

Never Mind!! Never Mind!!  Figured it out.

But the answer is kind of puzzling.   I used the Expression Builder as you suggested and, come to find out, the name of the subform that the Expression Builder uses is not how it appears in my Database window.

In the database window, the subform is named, 'frmsubHouseType'.  BUT, when I use the Expression builder, it comes out as, 'frmsubtblHouseType'.

Seems odd.  But in any event, it worked!!!

Thanks again for all your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now