"0" in DLookup

[melanieh]

I keep getting a "0" on a DLOOKUP and cannot figure out why.....

=[OnGotFocus]=DLookUp("[Price]","Price1","[WidthxHeight_ID] = Form![WidthxHeight_ID]")


This is the control source of a text box called PriceForm.

The lookup goes out to the Price field in the Price1 table. WidthxHeight_ID is on the price table.

On the form.... [WidthxHeight_ID] is the text box that is uses for the lookup value.

The price table does have amounts in it, not just zero and the ID's match so I'm stumped as to what is happening.


Any ideas?

Thanks.

 

[melanieh]

fixed that..... now another question

I removed the on got focus and also added a missing bracket which helped to solve the problem. Just one little issue left.

I have the DLookup in a subform.

When I move from one main form to another, then go back, my subform price is updated. If I move from one subform to another, it doesn't update..... only when I go out of the main form then back.

Any ideas on what I need to do?

Thanks! This forum has great information that has helped me immensely.

 

[ajetrumpet]

=[O

 

[ajetrumpet]

If I move from one subform to another, it doesn't update.....

What is the syntax you are using again?

only when I go out of the main form then back.

Can you explain that a little better?

 

[melanieh]

Here's the syntax

=DLookUp("[Price]","Price1","[WidthxHeight_ID] = Form![WidthxHeight_ID]")

The dlookup is on a control in the subform.

It looks up "price" on the "price 1 table". (The table has the price for each widthxHeight_ID.)

On the form, it's going to the widthxheight_ID control to know what ID to lookup on the price table.
The WidthxHeight_ID is a text box. It's control source is =Form!Width & "x" & [Height] & [Type] & [Sizing] (All those things combined make up the unique ID that I need it to look up on the price table.)

Thanks.
Melanie

 

[ajetrumpet]

=DLookUp("[Price]","Price1","[WidthxHeight_ID] = Form![WidthxHeight_ID]")

Does this even work?

It's control source is =Form!Width & "x" & [Height] & [Type] & [Sizing](All those things combined make up the unique ID that I need it to look up on the price table.)

Does this work too? Are you looking up these combined values as a string, or as a reference to another Form control, plus field values, all combined into one?

And, what exactly is the problem again, moving between (sub)forms?

 

[melanieh]

It works.

The combined values come from other controls on the form.
---Width & "x" & [Height] (This comes from a width control and a height control on the form)
----& [Type] (This comes from whatever is selected in the Type combo box control) (The combo box gives them the options from a Type table.)
-----& [Sizing] (This comes from whatever is selected in the sizing combo box control) (The sizing combo box gives them the options from the sizing table.)

When I fill out the main form, then the subform, the price control with the dlookup only has the actual lookup amount when I move to another main form.
If I go to another main form, then back, then the price seems to "magically" get looked up and it appears.
If I move from subform to subform it doesn't appear, only when moving in and out of a main form.

Thanks again. I am new with the dlookup so I appreciate your help. Hopefully things makes more sense.

 

[ajetrumpet]

When I fill out the main form, then the subform, the price control with the dlookup only has the actual lookup amount when I move to another main form.

I'm sorry Melanie, but I'm not following this. I think you mean to say, "When I move from record to record". Are you sure you don't mean to say that (instead of "main form to main form")??

If I move from subform to subform it doesn't appear, only when moving in and out of a main form.

Again, can you word this any differently? I think you mean, "When I move from record to record in the subform".

I will blame the technology world "lingo" for my ignorance. I may know too much of it...

 

[melanieh]

Yes you are correct. It is record to record. (my terminology error!)

 

[ajetrumpet]

When I fill out the main form, then the subform, the price control with the dlookup only has the actual lookup amount when I move to another main form.
If I go to another main form, then back, then the price seems to "magically" get looked up and it appears.

You have DLookup in the controlsource line, and, IF it works, the value is only queried when you enter a record (I think). Either that, or requery a form or subform. If you're entering information in a form, and the control that has the CS in it is blank initially when you start all of this entry, it will not give you the (final) value right away, as soon as the controls that are needed are filled in by you. It would have already done the looking up of the information that is available (e.g. - First price in the "price1" table, without criteria filtering), because you entered (went to) the record at a previous point in time, before you started entering values in the other controls needed. Make sense?

This would explain why you're only seeing the value from the table that it is looking up. That is because the other control values that are in the CS are not present, so that portion of the function cannot be executed.

 

[melanieh]

Makes sense....... I'll work on it. Not sure exactly what to do as I'm new at this but I will work on it!
Thanks.

 

[ajetrumpet]

Do you want any help with it? I can give you some basic facts that might help you going about it the right way...

 

[melanieh]

I would love the help.
Thanks! I appreciate it.
Melanie

 

[ajetrumpet]

First thing's first...

If you want DLookup to automatically populate a value after you enter all of your relevant data in, use a form event to populate the boxes value, do not set a controlsource, as this is always "running", so to speak, and it doesn't track the changes that you have made on the current record. It only responds to certain changes like record changes, focuses, and requeries (I think).

 

[ajetrumpet]

A typical DLookup population would look something like...

Private Sub TxtBox_GotFocus

  me.txtbxname = DLookup statement here

End Sub

 

[boblarson]

I'm surprised that nobody noticed this:

Your code:
=DLookUp("[Price]","Price1","[WidthxHeight_ID] = Form![WidthxHeight_ID]")

CORRECT CODE:
=DLookUp("[Price]","Price1","[WidthxHeight_ID] = " & Form![WidthxHeight_ID])

 

[melanieh]

Yes!!! It worked. (woo-hoo!!) Thanks so much. I've read a lot on the DLookup and it has all come together finally in my mind now that you've explained the difference between putting it in the the control source and the VBA code.
Bless you. You made my day! )
Melanie

 

[melanieh]

I'm surprised that nobody noticed this:

Your code:
=DLookUp("[Price]","Price1","[WidthxHeight_ID] = Form![WidthxHeight_ID]")

CORRECT CODE:
=DLookUp("[Price]","Price1","[WidthxHeight_ID] = " & Form![WidthxHeight_ID])

Private Sub PriceForm_GotFocus()
Me.PriceForm = DLookup("[Price]", "Price1", "[WidthxHeight_ID] = Form![WidthxHeight_ID]")
End Sub

It worked without the " &

Do I need to put that in there?
(New at this of course)

 

[boblarson]

I would question that is is actually pulling the right information because, as far as I know, you need to separate out the form reference to get the value from that reference. I ALWAYS use the concatenated version (what I wrote) to get the value from a control. If it works in your case, be careful because it WON'T work in all cases and, in fact, it would be somewhat an anomaly.

 

[ajetrumpet]

Welcome. Good luck!

And BTW Bob, I have never used the quote mark and the ampersand in function context like that, and I've never had a problem with my syntax, but I have had a problem with yours. Was it up with that!?

Malanie's way was kind of odd too, I haven't seen that either, until now.

No Melanie, you don't need to add it in. The syntax that always has worked for me, no matter what domain function I am using, is this...

Function("[field]", "table", "[field] = forms!formname!control")

...I always use this syntax, even if the current and active form is the one i need to reference.