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?
Thanks.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!cboH
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.
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!cboH
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.
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]![frmSubFo
Me.Option1 = DLookup("BrickFront", "tblHouseName", "HouseName = Forms![frmCommunity]![frms
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! :)
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]![frmSubFo
Me.Option1 = DLookup("BrickFront", "tblHouseName", "HouseName = Forms![frmCommunity]![frms
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! :)
ASKER
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]![SubFor
I'm going take it for a spin.
Thanks so much again for your help.
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]![SubFor
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
Good luck
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
Good luck
ASKER
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!frmsubH
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???
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!frmsubH
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???
ASKER
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.
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.
ASKER
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.