Solved

DLookup in a subform

Posted on 2004-10-04
8
897 Views
Last Modified: 2012-08-13
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?

Thanks.
0
Comment
Question by:mbath20110
  • 5
  • 3
8 Comments
 
LVL 5

Accepted Solution

by:
kemp_a earned 250 total points
ID: 12223560
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.

Cheers
0
 

Author Comment

by:mbath20110
ID: 12226110
Kemp,

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.
0
 

Author Comment

by:mbath20110
ID: 12226745
Kemp,

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.
0
 
LVL 5

Expert Comment

by:kemp_a
ID: 12227192
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! :)
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:mbath20110
ID: 12227850
Kemp,

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.
0
 
LVL 5

Expert Comment

by:kemp_a
ID: 12228301
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
0
 

Author Comment

by:mbath20110
ID: 12228764
Yup,

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
Forms!frmCommunity!frmsubHouseType.Form.cboHouseType"

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???
0
 

Author Comment

by:mbath20110
ID: 12230405
Kemp,

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.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now