text box that look up value from another table than the forms record source (1 Viewer)

zulu100

Member
Local time
Today, 22:29
Joined
Nov 3, 2021
Messages
54
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

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
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.

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

CJ_London

Super Moderator
Staff member
Local time
Today, 21:29
Joined
Feb 19, 2013
Messages
14,411
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

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
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

Member
Local time
Today, 22:29
Joined
Nov 3, 2021
Messages
54
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_ & "'")
 

Attachments

  • Error.png
    Error.png
    31.1 KB · Views: 274

cheekybuddha

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
Give a quick try like:
=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]=''" & Forms!frm_ctn_List![No_] & "'")
 

zulu100

Member
Local time
Today, 22:29
Joined
Nov 3, 2021
Messages
54
No luck either
If the syntax is correct then the expression builder should accept it - Correct ??
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:29
Joined
Sep 21, 2011
Messages
10,574
Shouldn't that be
Code:
=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]='" & Forms!frm_ctn_List!No_ & "'")
 

zulu100

Member
Local time
Today, 22:29
Joined
Nov 3, 2021
Messages
54
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

Enthusiastic Amateur
Local time
Today, 21:29
Joined
Sep 21, 2011
Messages
10,574
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

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
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! 😖
 

cheekybuddha

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
Go back to the original suggestion, but with the correction:
Code:
=DLookup("[AntalEmnerProduceret]","[tbl_TidsTabel]","[No_]='" & [Produktionsordre Nummer] & "'")
 

zulu100

Member
Local time
Today, 22:29
Joined
Nov 3, 2021
Messages
54
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

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
Code:
=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

Member
Local time
Today, 22:29
Joined
Nov 3, 2021
Messages
54
Code:
=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

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
Untested, but you can try:
Code:
=DMax("CLng(antalemnerproduceret)";"tbl_tidstabel";"[No_]='" & [Produktionsordre Nummer] & "'")


Edit: added code that was cut off
 
Last edited:

zulu100

Member
Local time
Today, 22:29
Joined
Nov 3, 2021
Messages
54
Untested, but you can try:
Code:
=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

AWF VIP
Local time
Today, 21:29
Joined
Jul 21, 2014
Messages
1,012
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)
Code:
?"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
Code:
SELECT [No_], CLng(antalemnerproduceret) AS antalemnerproduceret_nbr FROM tbl_tidstabel;

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

hth,

d
 

Users who are viewing this thread

Top Bottom