none
Getting data onto a Form using a foreign key. RRS feed

  • Question

  • I'm a complete novice at Access, so this is probably a very basic question, but I've been trying (and failing) to get this to work for some days now, and I need help. Badly.

    I have display items on display boards, and the boards can be in various locations.

    The Item table (tblItem) has a foreign key to an entry in the Board table (tblBoard).

    The Board table has a foreign key to an entry in the Location table (tblLocation).

    I have a form (frmItem) which I am trying to use to display details about an Item.

    I wish to display the Item name, the name of the Board it is on, and the location of that Board.

    I'm using 

    =DLookUp("BoardName","tblBoard","ID = " & [Forms]![frmItem]![UIdBoard])

    to get the name of the Board which the item is on, but I'm stumped at how to get the name of the Location.

    I've tried recovering the foreign key from the Board table and putting that on the Item form as LocUid, then using LocUid in a subsequent DLookUp

    =DLookUp("UidLocation","tblBoard","ID = " & [Forms]![frmItem]![LocUId])

    but this has got to be wrong for all sorts of reasons (e.g. sequence of form field reconcilliation). I've searched the web for similar issues, but I must be getting my serach terms wrong, 'cos I can't reconcile the answers with my problem.

    Can any kind sould point me in the right direction? Any help would be gratefully received....

    Wednesday, May 3, 2017 11:11 AM

Answers

  • Hi Sorcerer13,

    I try to create tables as yours.

    TblItem:


    TblBoard:


    TblBoardLocation:


    then I try to create a query using design view and select the fields I want to display in result.



    Query in SQL View.

    SELECT tblboard.board_name, tblboardlocation.location_name, tblitem.item_name

    FROM (tblboard INNER JOIN tblboardlocation ON tblboard.board_location_id = tblboardlocation.board_location_id) INNER JOIN tblitem ON tblboard.board_id = tblitem.board_id;

    Result:


    Regards

    Deepak

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 4, 2017 3:19 AM
    Moderator
  • Sorcerer13,

    In addition to Deepak's detailed post, if you search "Access 2010 query tutorial" or similar, you will find a variety of blogs and videos that assume you are completely new to Access, and show you step by step how to bring up the query builder, add tables, create joins, etc... AND many of them are under five minutes, for people who need to ramp up quickly as seems to be your case.

    Puling the correct fields/data is the first step.  Once you have a query, to use it as your form's recordsource:

    - Open your form in design view

    -  In your form's property sheet, under the Data tab, select your query name as the Recordsource

    An alternative to building and saving a query separately is as follows:

    - Open your form in design view

    - In the property sheet under the Data tab, click the "..." next to recordsource.  (This will bring up the query builder)

    - Add tables and joins as shown in Deepak's post

    - Close the Query Builder window, and save.

    I've never personally benchmarked this, but my understanding from forum posts, articles etc, is that the first method (creating and saving your query separately from your form) provides better performance.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, May 4, 2017 10:46 AM added detail
    • Marked as answer by Sorcerer13 Friday, May 5, 2017 8:35 AM
    Thursday, May 4, 2017 10:44 AM
  • It is perhaps worth expressing a word of caution at this point.  While the query described by Deepak will do what you say you want, i.e. 'to display the Item name, the name of the Board it is on, and the location of that Board', a form based on such a query should not be used for data entry.  For that you would need only include the tblItem table in the query used as the form's RecordSource, e.g.

    SELECT *
    FROM tblitem
    ORDER BY item_name;

    The form would then include the following controls:

    1.  A text box bound to item_name.

    2.  A combo box bound to board_id, set up as follows:

    Name:   cboBoard

    ControlSource:    board_id

    RowSource:    SELECT board_id, location_name, board_name
                           FROM tblboard INNER JOIN tblboardlocation
                           ON tblboard.board_location_id =tblboardlocation.board_location_id
                           ORDER BY board_name;

    BoundColumn:   1
    ColumnCount:    3
    ColumnWidths:  0cm;0cm;8cm

    If your units of measurement are imperial rather than metric Access will automatically convert the last one to inches.  The important thing is that the first two dimensions are zero to hide the first two columns.

    3.  An unbound text box with a ControlSource property of:

    =cboBoard.Column(1)

    The column property is zero-based, so this references the second hidden column, location_name, and will consequently show the location of the selected board.  This cannot be edited of course.  If you need to change the location of a board this would be done by a separate form bound to the tblboard table.  That form would include a combo box bound to the board_location_id foreign key column, set up in the same way to show the location name by hiding the first column.  If you wished, you could include a button on the Items form to open this Boards form.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, May 4, 2017 10:10 PM
    • Marked as answer by Sorcerer13 Friday, May 5, 2017 8:35 AM
    Thursday, May 4, 2017 10:09 PM

All replies

  • Try creating a query to join the tables you need, and use that as the recordsource for your form.  Doing that should give you all of the fields you need for your form, without the DLookups.

    Miriam Bizup Access MVP

    Wednesday, May 3, 2017 1:04 PM
  • Miriam....

    It’s my first venture into Access 2010 – could you please tell me how to “create a query to join the tables you need”, and “use that as the recordsource” using my tables as below?

    The Form is frmItem, with a Control Source of tblItem.

    I know I should research this further, and I will when I get some” spare” time, but I’m currently under a lot of pressure to progress this Project. Any help will be truly appreciated….

    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

    Thanks in advance....

    Wednesday, May 3, 2017 2:02 PM
  • Hi Sorcerer13,

    I try to create tables as yours.

    TblItem:


    TblBoard:


    TblBoardLocation:


    then I try to create a query using design view and select the fields I want to display in result.



    Query in SQL View.

    SELECT tblboard.board_name, tblboardlocation.location_name, tblitem.item_name

    FROM (tblboard INNER JOIN tblboardlocation ON tblboard.board_location_id = tblboardlocation.board_location_id) INNER JOIN tblitem ON tblboard.board_id = tblitem.board_id;

    Result:


    Regards

    Deepak

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 4, 2017 3:19 AM
    Moderator
  • Sorcerer13,

    In addition to Deepak's detailed post, if you search "Access 2010 query tutorial" or similar, you will find a variety of blogs and videos that assume you are completely new to Access, and show you step by step how to bring up the query builder, add tables, create joins, etc... AND many of them are under five minutes, for people who need to ramp up quickly as seems to be your case.

    Puling the correct fields/data is the first step.  Once you have a query, to use it as your form's recordsource:

    - Open your form in design view

    -  In your form's property sheet, under the Data tab, select your query name as the Recordsource

    An alternative to building and saving a query separately is as follows:

    - Open your form in design view

    - In the property sheet under the Data tab, click the "..." next to recordsource.  (This will bring up the query builder)

    - Add tables and joins as shown in Deepak's post

    - Close the Query Builder window, and save.

    I've never personally benchmarked this, but my understanding from forum posts, articles etc, is that the first method (creating and saving your query separately from your form) provides better performance.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, May 4, 2017 10:46 AM added detail
    • Marked as answer by Sorcerer13 Friday, May 5, 2017 8:35 AM
    Thursday, May 4, 2017 10:44 AM
  • It is perhaps worth expressing a word of caution at this point.  While the query described by Deepak will do what you say you want, i.e. 'to display the Item name, the name of the Board it is on, and the location of that Board', a form based on such a query should not be used for data entry.  For that you would need only include the tblItem table in the query used as the form's RecordSource, e.g.

    SELECT *
    FROM tblitem
    ORDER BY item_name;

    The form would then include the following controls:

    1.  A text box bound to item_name.

    2.  A combo box bound to board_id, set up as follows:

    Name:   cboBoard

    ControlSource:    board_id

    RowSource:    SELECT board_id, location_name, board_name
                           FROM tblboard INNER JOIN tblboardlocation
                           ON tblboard.board_location_id =tblboardlocation.board_location_id
                           ORDER BY board_name;

    BoundColumn:   1
    ColumnCount:    3
    ColumnWidths:  0cm;0cm;8cm

    If your units of measurement are imperial rather than metric Access will automatically convert the last one to inches.  The important thing is that the first two dimensions are zero to hide the first two columns.

    3.  An unbound text box with a ControlSource property of:

    =cboBoard.Column(1)

    The column property is zero-based, so this references the second hidden column, location_name, and will consequently show the location of the selected board.  This cannot be edited of course.  If you need to change the location of a board this would be done by a separate form bound to the tblboard table.  That form would include a combo box bound to the board_location_id foreign key column, set up in the same way to show the location name by hiding the first column.  If you wished, you could include a button on the Items form to open this Boards form.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Thursday, May 4, 2017 10:10 PM
    • Marked as answer by Sorcerer13 Friday, May 5, 2017 8:35 AM
    Thursday, May 4, 2017 10:09 PM
  • Thanks to Miriam, Deepak and Ken - I've learned a lot from you guys, and I'm deeply impressed by the thoroughness of your replies.

    I should by now have enough info to get me through this stage of the project.

    Due to the helpful and informative nature of your replies, I've marked them all as answers.

    Thanks again for all your help....

    Friday, May 5, 2017 8:39 AM