Failing DLookup on subform 1

[ppkgoldengun]

I have a subform with the following lookup field that works correctly:

=DLookUp("[FilmType]","qryFilmAll","[FilmID] = Forms![frmDiveFilmRollSubform]![cboFilmID]&quot

However, when this subform is executed from the main form (on a tabbed page) it fails with a flashing "error". I've tried full path syntax, the Parent. syntax etc. Any help would be appreciated. It's probably my tired eyes.

Thanks.

 

[Steve101]

Try the following:

If your cmbFilmId control is on the subform itself, then if you leave out the forms qualifier, it would appear to work in both cases:

ie. try

=DLookUp("FilmType","qryFilmAll","FilmID=" & cboFilmID)

Its curious, but I dont know why it does'nt work with the qualifier, though I'm sure there is a perfectly good explanation.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ppkgoldengun]

Appreciate the help but your code fails both on the subform and the main form. My code works for the subform by itself. Access seems to get more bizzare with each release. Any other ideas?

 

[FoxProProgrammer]

I hope that I'm not intruding here, but there would appear to be an error in the code:

DLookUp("[FilmType]","qryFilmAll","[FilmID] = Forms![frmDiveFilmRollSubform]![cboFilmID]&quot

The syntax for DLookUp is DLookup(expr, domain, criteria).

Since criteria must be a string expression, I would think that "[FilmID] = Forms![frmDiveFilmRollSubform]![cboFilmID]" will be evaluated literally, meaning that Access won't know to substitute the value for Forms![frmDiveFilmRollSubform]![cboFilmID] when evaluating the expression. This is why Steve suggested using "FilmID=" & cboFilmID to define the criteria expression. If Steve's code doesn't work, I would guess that the problem is it assumes that cboFilmID is a number. If it is a string, you would need to enclose it in quotes. If that's the case try this:

DLookUp("FilmType","qryFilmAll","FilmID = " & Chr(34)& cboFilmID & Chr(34))

dz
dzaccess@yahoo.com

 

[Steve101]

Try the following syntax:

=DLookUp("[FilmType]","qryFilmAll","[FilmID]=[cboFilmID]&quot

or

=DLookUp("[FilmType]","qryFilmAll","[FilmID]=" & cboFilmID)

I don't know why, but this seems to work, whereas with the forms qualifier, it does'nt.

PS. If this appears to be a duplicate post from me, its because I posted a response earlier, but it seems to have disappeared.

Cheers,




Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

It is kind of bizarre. The code I submitted does work for me (Access2000), for both the subform and simple form scenarios. Thanks for the qualification dz. I assumed that the cmbFilmId was a numeric field, based on the context of the original submission.

My submission was actually tested, and does work. If you like, I'll send it to you. If so, provide an email; it may throw some further light on the problem. Are you using Access XP? Sounds like you might be.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[FoxProProgrammer]

That is strange, Steve. I don't understand why DLookUp("[FilmType]","qryFilmAll","[FilmID]=[cboFilmID]&quot works because the criteria expression, "[FilmID]=[cboFilmID]", is entirely enclosed in quotes. I understand why "[FilmID]=1" or "[FilmID]=4" works, but how does VB know to retrieve the value of [cboFilmID] instead of treating it as a literal string? I am using Access 2000 like you. If you want to send your code, I'd like to check it out. My email address is in the signature block below.

I replied to another question last night about referring to the full qualifier of a control on a subform. The thread is thread702-344230. To summarize, Microsoft recommends that you don't refer to the full qualifer unless you are referring to a control on a form that doesn't have the focus. If the control that you are referring to is on a form that has the focus, performance can be degraded if you use the full qualifer. dz
dzaccess@yahoo.com

 

[ppkgoldengun]

Thanks Gentlemen,
Due to a early trip this morning to the right coast I won't have a chance to test until I am on the plane. Quick question. Since I am using a tab control TabCtl0 and a particular page on that control "Photo", do I further need to modify or will the simple main form/subform code work?

Again, very much appreciate the help.

 

[ppkgoldengun]

By the way I am using Access 2000. Refuse to use Access X tra P roblems edition.

 

[jebry]

Hi!

Part of the problem may be with how you are referring to the control on the subform. The general syntax for referring to a control on a subform is:

Forms!ParentForm!SubFormControlName.Form.Controls("ControlName&quot

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Steve101]

Jeff,

Yes, the format you've provided above is the unabreviated 'formal' way of referencing a control on a subform. The following are all legitimate variations of an example that I've put together and tested on a form. See comments later.

(1) =DLookUp("f1","Table1","f3 = [Text0]&quot
Note: Not really correct. See notes later.

(2) =DLookUp("f1","Table1","f3 = " & [Text0])

(3) =DLookUp("f1","Table1","f3 = " & [Forms]![frmMain]![frmSub].Form!Text0)

(4) =DLookUp("f1","Table1","f3 = " & [Forms]![frmMain]![frmSub].Form("Text0&quot)

(5) =DLookUp("f1","Table1","f3 = " & [Forms]![frmMain]![frmSub]("Text0&quot)

(6) =DLookUp("f1","Table1","f3 = " & [Forms]![frmMain]![frmSub].Controls("Text0&quot)


Notes:

(a) To set up this example, create a new database with a table called Table1, with two fields f1 (text) and f3 (numeric); record values as follows: a,1; b,2; c,3; d,4; e,5; f,6

(b) Create an unbound form called frmSub with an unbound
text control named Text0, with a default value of 4.

(c) Create 6 more unbound text controls in the above form
and past the 6 formula above (from and including the equals sign) as the controlsource properties of the respective controls.

(d) Open the subform directly. You should note that only the first two computed fields produce a value; the rest (correctly) produce a #name error.

(e) As dz has pointed out, its kindof curious that the first formula works at all. It evaluates the DLookup correctly, even though the whole thing is incorporated as a string. However, try changing the value of the Text0 control from its default value of 4 to say 5. Then press Shift F8 a couple of times to refresh the form. The second control refreshes to the correct lookup value; the first does'nt. Curious that it worked when the form was opened though, (although why would anyone do it this way anyway).

(f) Now create a new form called frmMain and place the subform in it.

(g) Open the new frmMain form, and you should observe that all 6 formula return the same value. Modify the text0 value and they should re-sync together (except the first), demonstrating that all have valid syntax

(h) My own preference is formula (2), which operates correctly on frmSub independent of whether the form is an embedded subform, or a standalone form.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ppkgoldengun]

Finally, tested and now works correctly. Still don't know why the Formal path doesn't. I guess more MS "features".

Thanks for all the help !!

 

[bllgnn]

I was having the same problem until I read the post from FoxProProgrammer whose code included Chr(34) before and after the reference to the control.

DLookUp("FilmType","qryFilmAll","FilmID = " & Chr(34)& cboFilmID & Chr(34))

Adding Chr(34) before and after the reference to the control on the subform made my code work for me.

Thanks

Bill

 

[FoxProProgrammer]

Hi Bill:

I'm amazed that you found this thread over a year later. I'm amazed that I'm still here over a year later! lol
Last but definitely not least, I'm glad that my example helped you out.

Take care,



dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Sorry, meant almost a year later!

dz
dzaccess@yahoo.com

 

[bllgnn]

Hi dz,

I'm amazed I found your reply almost 2 years later Are you still here?

Bill