DLookup issue

[kryten]

Hi everyone,

I have a subform [frmOrderDetailsSubform] on which I am trying to use DLookup as the controlsource for a field to look up the [OnHand] field in my query [qryOnHand2].

=DLookUp("[OnHand]","[qryOnHand2]","

 = Forms![frmOrderDetailsSubform]![Code]")

This works fine when I open the subform as a standalone, however when I open the subform as part of the main form I get a flickering #Error.

Any advice would be greatly appreciated.

 

[KenHigg]

If code = number:

=DLookUp("[OnHand]","[qryOnHand2]","

 = " & Forms![frmOrderDetailsSubform].form![Code])

or If code = string:

=DLookUp("[OnHand]","[qryOnHand2]","[Code] = '" & Forms![frmOrderDetailsSubform].form![Code] & "'")


???
kh

 

[kryten]

Hi Ken,

Thanks for your reply.


This works only when I open the subform as a standalone. When it is opened as part of the main form I get #Name?

Any other advice you could give would be appreciated.

 

[KenHigg]

My Bad. Try:

=DLookUp("[OnHand]","[qryOnHand2]","

 = " & Forms!myMainformName![frmOrderDetailsSubform].form![Code])

Substituting myMainformName with the main form name...

???
kh

 

[kryten]

Hi Ken,

Tried that, this time I get #Name? when I open it as a standalone as well as on the main form.

Thanks for trying.

 

[KenHigg]

Let's see; You have a main form with a subform that has a text box and you want to look up an on hand qty using dlookup and place this 'on hand qty' in the text box. Once you get this on hand qty in the text box, do you want to store it in the subform's underlying record source table or just view it?

???
kh

 

[kryten]

I just want to view it

 

[KenHigg]

You must have something named wrong...

Can you put enough of this in a post?

kh

 

[kryten]

I have pasted the relevant forms etc into a new database and attatched it ( the whole database is too big)

 

[KenHigg]

=DLookUp("[OnHand]","[OnHand2]","

 = " & [Forms]![Orders3]![OrderDetails].[Form]![Code])


I didn't see a table or query called OnHand2. I assume you forgot to post it?


kh

 

[kryten]

Sorry Ken here it is again

 

[KenHigg]

OnHand2 is looking for OnHandQry and can't find it...

 

[kryten]

OnHand2 is based on OnHandQry which is in turn based on several other queries (gets complicated!)

I don't understand why the DLookup works when you open the subform on it's own but not when it is part of the main form?

Thanks for your help

 

[KenHigg]

Oh....

I see the problem. You can't do this with the control source when the form is in continuous forms view. And I don't think you can do it in the underlying query as it will make it an un-updatable query...

All you want to see is the qty on hand as you enter the details...

Hum... One last shot:

=DLookUp("[OnHand]","[OnHand2]","

 = " & me![Code])

kh

 

[kryten]

No joy - still get #Name?

Isn't there any other way to reference it than using DLookup?

Thanks for trying

 

[KenHigg]

Hum. Check this out:

kh

 

[kryten]

I couldn't open this file - unrecognised database format (I'm using Access 97)

 

[KenHigg]

Oh well. I got dlookup to work in a sub form...

I wonder if anyone else in the forum can convert this for you...

kh

 

[KenHigg]

This was the line:

=DLookUp("[name]","tblColors","

 = '" & Forms!form2!form1.Form!colorcode & "'")

kh

 

[joseph.shepherd]

I thought I'd take a shot at this and the code that I wrote should work except that your query OnHand2 is trying to get its information from a query called OnHandQry and this query doesn't seem to exist. The following code will access the information if you can figure out where that query went or redesign OnHand2 to gather it's data from another source and edit the code accordingly.

I just put this sode in the Form Load event for the subform. I hope this helps.
Joe

Private Sub Form_Load()
Dim SomeVariable As String

SomeVariable = DLookup("[OnHand]", "OnHand2", "

 = Forms!Orders3![OrderDetailSubform]!Form![code]")
OnHand.Value = SomeVariable

End Sub