Problem with DLookup value

[JB83]

Dear reader(s),

I have a problem with a DLookup value. I would like to use the value of a DLookup value to create a new record into another table. Is it true that if there is no control source for a DLookup value it cannot be given to another variable and if yes, how can this problem be solved?

Thanks in advance,

Jochen.

 

[jebry]

Hi!

You do not need to set the DLookUp as a control source to use it. It is perfectly legal to use:

MyVariable = DLookUp(etc) or
rst!MyField = DLookUp(etc)

If you will be using the same value in more than one spot then you might want to consider using it as a control source of a text box. You can set the box's visible property to no if it doesn't or shouldn't need to be seen by the user. Then you can use it from any code attached to the form.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JB83]

Jeff,

Thank you for your fast responding, but it is not exactly of what I meant. It's my fault, I should have given more information about the problem. Therefore, here is an example of the situation:

The DLookup method runs when the values of two combo boxes are selected; it has to look for a number of the unique combination of the two combo box values. When the value has retrieved, this value has to be pasted into another table as a new record.
When I use the DLookup function without a control source, it returns the value "Empty", but if I do use a control source it returns the value I need. I would like to use this value without a control source, because I only need it on temporary base.

---------------  ------------
| ProductName |  | UnitName |   
---------------  ------------
         \            /
          \          /
    -------------------------
    | Table: PRODUCTNUMBERS |
    -------------------------
    | ProductNumber         |
    ------------------------

                                ---------------------
-----------------               | Table: PRODUCTID  |
| ProductNumber |  ------>      ---------------------
-----------------               | ProductNumber     | 
                                ---------------------

P.S.: This is not the entire relationships model.

Thanks in advance,

Jochen.

 

[jebry]

Hi!

I guess I don't understand what you mean by control source in this case. The only use I am familiar with is the control source of a control on a form (where the information comes from and/or is stored). From that perspective, the DLookUp doesn't have a control source. Looking at your set up I would say that you should use the following code (probably from a button click):

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDyanset)

rst.AddNew
rst!Yourfield = DLookUp("ProductNumber", "ProductNumbers", "ProductName = '" & Me.txtProductName & "' And UnitName = '" & Me.txtUnitName & "'&quot
rst!NextField = Whatever etc.
rst.Update

Set rst = Nothing

This will add a new record to your table with out storing the productnumber anywhere but in that table. If I am still way off base, let me know.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[JB83]

Jeff,

Thanks for your fast responding again!

What I meant with a control source is a text box, list box, combo box, label, etc; all the things you can see on the screen.
The DLookup value is only a temporary value that is being used as a base value for a new record into another table and only being used in the Visual Basic code.
But what I don't understand is that I can't get the DLookup value without a control source. Is this a bug in MS Access or is it more complicated?

Thanks in advance,

Jochen.

 

[JB83]

Only 5 minutes after the previous post I've found a solution . . . Let's leave it on a bad start of the week

Thanks for your help anyway and keep up the good work!



Jochen. It never hurts to help and it never helps to hurt.