Help on using Foreign Keys to look up foreign data on a Form (1 Viewer)

I am trying to show a value on a form, but can’t work out how to do it.
I have been using Excel (cell formulae and VBA) for years, but this is my first venture into Access.
I’m not looking for a VBA solution, just trying to work out how to do what I’m sure should be a simple routine task.

I have the following tables, with the stated fields

Item Table (tblItem)
ID
Other data fields
fkItemToBoard

Board table (tblBoard)
ID
BoardName
Other data fields
fkBoardToLocation

Location table (tblLocation)
ID
LocationName
Other data fields

I have a form (frmItem) on which I have text boxes showing details for each of the Items.
The form’s Control Source is tblItem.

To show the Board on which the Item lives, I’ve entered
as the Text Box’s Control Source. This works fine.

How do I show the value LocationName on the Form?
I can’t work out how to “nest” a DLookUp inside another DLookUp.
I’ve tried showing the fkBoardToLocation on the Form in a Text Box (tbxLocId) then using
, but this just gives me a “#Name?” error.
Can any kind soul help me?

Is tbxlocid a text string?
If so modify the last part of your code as follows

Colin....
You were correct when pointing out that a text box probably contains text (doh!!!), but sadly adding the extra quotes didn't have any discernable effect. I still get the "#Name?" error.
The relevant control source statements are
For the LocUid textbox
=DLookUp("fkBoardToLocation","tblBoard","ID = " & [Forms]![frmItem]![fkItemToBoard])
N.B. fkBoardToLocation is defined as Number
For the Location textbox
=DLookUp([“LocationName”],[”tblLocation”],[”ID = ‘“] & [Forms]![frmItem]![LocUid] & [“’”])
Also, Access seems to insist on adding "[]" to every clause, regardless of whether the clause contains a space. It's not exactly user-friendly for reading.
OK.I'm partially sighted, but it must be awkward for normal folk - can this be overridden?

That's definitely not right.

Take out all the [] brackets. You don't need them
Also check the single and double quotes used as they look odd to me
However I am typing this on my phone

Finally check your control name as it was different in each of your posts

ridders....

I'll try re-entering the single and double quotes - as for the "[]"s, every time I enter the Control Source statements without them, Access 2010 puts them back in!
It's so frustrating!
As I said, I'm partially sighted so clutter like the square brackets is certainly unhelpful (but then, that's Microsoft for you!).

Hi

That's odd - it may be something to do with the 'sloping quotes'

When Access insists on doing something unnecessary & unwanted I use one of the following work-rounds
a) type the code in the Immediate window
b) type it in Notepad
and then paste it into the main VBE window

Hopefully I've got your control name correct below
If so, paste this into the VBE & see if it now works


Notice how the quotes aren't 'sloping' back or forward in my version
If you need to edit it, suggest you do a) or b) above to do so

Interesting!

The rogue "[]"s vanished on a cut-n-paste from the post, but curiously I now get "#Error", even though the "[]"s don't reappear!
when going from Design View to Form View then back to Design View.
I'll try the Notepad route, and see if I can fix it.
Thanks for all the advice!....