Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-03-2017, 09:19 AM   #1
sja13
Professional Idiot
 
sja13's Avatar
 
Join Date: May 2017
Location: God's Own County
Posts: 63
Thanks: 26
Thanked 0 Times in 0 Posts
sja13 is on a distinguished road
Help on using Foreign Keys to look up foreign data on a Form

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
Code:
=DLookUp("BoardName","tblBoard","ID = " & [Forms]![frmItem]![fkItemToBoard])
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
Code:
=DLookUp("LocationName","tblLocation","ID = " & [Forms]![frmItem]![tbxLocId])
, but this just gives me a “#Name?” error.
Can any kind soul help me?

sja13 is offline   Reply With Quote
Old 05-03-2017, 09:54 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,940
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Help on using Foreign Keys to look up foreign data on a Form

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

Code:
ID = '" & [Forms]![frmitem].[tbxlocid] & "'")
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 05-03-2017 at 01:08 PM.
isladogs is offline   Reply With Quote
Old 05-04-2017, 02:02 AM   #3
sja13
Professional Idiot
 
sja13's Avatar
 
Join Date: May 2017
Location: God's Own County
Posts: 63
Thanks: 26
Thanked 0 Times in 0 Posts
sja13 is on a distinguished road
Re: Help on using Foreign Keys to look up foreign data on a Form

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?

sja13 is offline   Reply With Quote
Old 05-04-2017, 02:57 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,940
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Help on using Foreign Keys to look up foreign data on a Form

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 05-05-2017, 12:52 AM   #5
sja13
Professional Idiot
 
sja13's Avatar
 
Join Date: May 2017
Location: God's Own County
Posts: 63
Thanks: 26
Thanked 0 Times in 0 Posts
sja13 is on a distinguished road
Re: Help on using Foreign Keys to look up foreign data on a Form

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!).
sja13 is offline   Reply With Quote
Old 05-05-2017, 06:03 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,940
Thanks: 114
Thanked 2,993 Times in 2,721 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Help on using Foreign Keys to look up foreign data on a Form

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

Code:
=DLookUp("LocationName","tblLocation","ID = '" & Forms!frmItem!LocUId & "'")
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 05-05-2017, 07:00 AM   #7
sja13
Professional Idiot
 
sja13's Avatar
 
Join Date: May 2017
Location: God's Own County
Posts: 63
Thanks: 26
Thanked 0 Times in 0 Posts
sja13 is on a distinguished road
Re: Help on using Foreign Keys to look up foreign data on a Form

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!....


sja13 is offline   Reply With Quote
Reply

Tags
dlookup access forms

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto populating foreign keys - Bulk Data entry into Access from Excel mkav General 2 02-25-2016 09:15 AM
Moving data from one database to another - foreign keys question treeman Queries 5 11-13-2015 06:29 PM
Importing data where foreign keys are involved David8 Queries 1 07-10-2014 05:40 AM
representing a table in a form that uses foreign keys benoize Forms 3 11-30-2012 08:15 AM
Form with foreign keys Swedude Forms 13 01-12-2010 03:56 PM




All times are GMT -8. The time now is 07:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World