Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DLookup in a subform

Posted on 2004-10-04
8
930 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

839 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