text box that look up value from another table than the forms record source

[zulu100]

Hi
Totally new to access vba so bare over with me if I ask stupid questions

I have a text box on a continuous form where I would like to show a value from another table that the forms record source.

I would like to look up "AntalEmnerProduceret" from the table "tbl_TidsTabel" where field "No_" equals the field "Produktionsordre Nummer" on my form which is named "frm_ctn_List"

"AntalEmnerProduceret" is a number field and both "No_" and "Produktionsordre Nummer" are short text fields.

So far I have this:

=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]=" & Forms!frm_ctn_List!Produktionsordre Nummer)

Maybe I am messing something up with missing quotation marks
I get an error about my expression contains an invalid syntax

Best regards.

 

[cheekybuddha]

Since No_ is a text field then you must surround the value being passed in quote marks.

Also, since you include a space in your control name Produktionsordre Nummer (a bad idea!) you must surround it with square brackets when referring to it.

=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]=''" & Forms!frm_ctn_List![Produktionsordre Nummer] & "'")

 

[CJ_London]

and if this control is on the frm_ctn_List form then you don't need to reference the form

=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]=''" & [Produktionsordre Nummer] & "'")

 

[cheekybuddha]

The other alternative would be to join tbl_TidsTabel to the table in your RecordSource and include AntalEmnerProduceret in the field list.

Then you can just set the ControlSource of [Produktionsordre Nummer] to AntalEmnerProduceret

 

[zulu100]

Thanks for your reply...
hmmm. Still cant get it to work though. I get the same error.
The field [Produktionsordre Nummer] is a query field name but actually comes from a Production order table where the field name is [No_].
Could that cause this error ?

I have tried deleting the field name change in my query so the reference name is the same as in the table where it came from like below, but still no luck.

=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]=''" & Forms!frm_ctn_List!No_ & "'")

 

[cheekybuddha]

Give a quick try like:
=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]=''" & Forms!frm_ctn_List![No_] & "'")

 

[zulu100]

No luck either
If the syntax is correct then the expression builder should accept it - Correct ??

 

[Gasman]

Shouldn't that be

=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]='" & Forms!frm_ctn_List!No_ & "'")

 

[Gasman]

No luck either
If the syntax is correct then the expression builder should accept it - Correct ??

Syntax is incorrect though?

 

[zulu100]

Yes - Just tried to rule out some wrong spelling
What I mean is... if for example there is an error in the referenced fields I wouldn't get this kind of error (Invalid character or comma etc...)
This error relates to characters "'[]& misplaced, missing or maybe added where it shouldn't - Correct ???

 

[Gasman]

Yes - Just tried to rule out some wrong spelling
What I mean is... if for example there is an error in the referenced fields I wouldn't get this kind of error (Invalid character or comma etc...)
This error relates to characters "'[]& misplaced, missing or maybe added where it shouldn't - Correct ???

Have you tried my attempt?

 

[cheekybuddha]

Syntax is incorrect though?

Good spot - I need to go to the opticians!

Edit: just realised the dang editor tries to be helpful and adds a closing quote when you type an opening one which I missed!

 

[zulu100]

Have you tried my attempt?

Yes - unfortunately same error.
Anything else I could be doing wrong... maybe in the referenced Form or table ?

 

[cheekybuddha]

Go back to the original suggestion, but with the correction:

=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]='" & [Produktionsordre Nummer] & "'")

 

[zulu100]

Now I dont get any errors... But I also don't get the result I want..
I tried to use ; as a separator. I use a Danish version of Access... Maybe it translates differently.
However... My text box only show #Name?

=DLookUp([antalemnerproduceret];"tbl_tidstabel";"[No_]='" & [Forms]![frm_ctn_List]![Produktionsordre Nummer] & "'")

 

[cheekybuddha]

=DLookUp([antalemnerproduceret];"tbl_tidstabel";"[No_]='" & [Forms]![frm_ctn_List]![Produktionsordre Nummer] & "'")
                  ^                                                   ^
                  |                                                   |
        This must be a string             Don't use Form reference unless control is on different form
        
=DLookUp("antalemnerproduceret";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

 

[zulu100]

=DLookUp([antalemnerproduceret];"tbl_tidstabel";"[No_]='" & [Forms]![frm_ctn_List]![Produktionsordre Nummer] & "'")
                  ^                                                   ^
                  |                                                   |
        This must be a string             Don't use Form reference unless control is on different form
       
=DLookUp("antalemnerproduceret";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

Thanks a lot... That did the trick.
I little additional question.
If there are duplicate values in "tbl_tidstabel" "No_" and I would like to only return the highest value of "antalemnerproduceret". Would that be possible??
Remember both "No_" and "Produktionsordre Nummer" are short text fields. Not numeric values though that what stored in them
"antalemnerproduceret" is a number field.

 

[cheekybuddha]

Untested, but you can try:

=DMax("CLng(antalemnerproduceret)";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

Edit: added code that was cut off

 

[zulu100]

Untested, but you can try:

=DMax("CLng(antalemnerproduceret)";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

Edit: added code that was cut off

That gave me an #Error in the textbox
However the below code seem to work.
The CLng conversion doesn't seem necessary


=DMax("antalemnerproduceret";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

Thank you very much for all your help. Saved me a lot of time

 

[cheekybuddha]

OK, glad it works for you.

*** BUT ***
Double check your results - numbers stored as strings sort differently.
E.g Test in Immediate Window (Ctrl+G)

?"1000" > "99"
False

Surely 1000 > 99 so we would expect the result to be True!!!

Perhaps DMax() makes an implicit conversion, but I doubt it (though I haven't tested).

You may need to create a query based on tbl_tidstabel that casts the string as a numeric, and base your DMax() on that query instead.
Eg qry_tidstabel

SELECT [No_], CLng(antalemnerproduceret) AS antalemnerproduceret_nbr FROM tbl_tidstabel;

Then,
=DMax("antalemnerproduceret_nbr";"qry_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")

hth,

d