Results 1 to 7 of 7
  1. #1
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239

    Show view as table value from record

    Hi,



    I have joined tables to my master form (views in other word). When I add existing fields to form, these textboxes shows me only indexes of joined
    tables (1,2,3 etc.), but I want them to display values from table record - in my case I want to display City name, instead of record index.

    I know this can be accomplished by adding Combobox on form that shows whatever value you want, but I want Textbox on form.

    Setting Textbox control source property to desired column also doesn't work:

    Code:
    =[ID_City].[Column](4)
    So, how to accomplish this in Access ?
    Last edited by Lukael; 02-09-2016 at 08:01 AM.

  2. #2
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    I've also tried DLookUp, but still nothing:

    Code:
    =DLookUp([CITY];[City_Name];[ID_MasterForm] & "=" & [ID_City])

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have joined tables to my master form (views in other word)
    I'm not sure I'm following the idea in the above.

    Here is info re bound and unbound forms

    Are you using Lookup fields at the table field level?

    I think this is one of those cases where a picture/jpg and example would help readers understand your situation.

  4. #4
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Thanks for response Orange,

    I'll explain little bit more....Here are both table designs:

    MasterTable
    ID_MasterTable(PK)
    ID_City (number field-FK)

    CityTable
    ID_CIty(PK)
    CityName

    So, those two tables are linked in one-to-many relationships.

    Now, I want to create a form where User would see what Cities are entered in each MasterTable record....But, since ID_City(FK) is a number field, Textbox (which is added by "add existing fields" on form) displays number value - that is record ID (or index) from CityTable.

    And bottom line : I want to display CityName value, not record ID.

    I hope now you understand...

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As in my previous post
    I think this is one of those cases where a picture/jpg and example would help readers understand your situation.
    What else is in the master table?

    In a couple of sentences, WHAT is this database about?

  6. #6
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Ok,

    no problem. I attached sample DB to see exactly what I want, and .jpg image if you don't have time

    In a couple of sentences, WHAT is this database about?
    I just want to create form where Users could see records (no edits allowed), and all Textboxes which have joined table records to display actual values from tables, NOT record ID's.
    Attached Thumbnails Attached Thumbnails picture.jpg  
    Attached Files Attached Files

  7. #7
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Figured out on my own.

    You have to use Dlookup function like this:

    =DLookUp("CityName";"CityTable";"ID_City= MasterTable.ID_City")

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-08-2014, 12:47 PM
  2. Expand Button in Datasheet View Doesn't show related table
    By CementCarver in forum Database Design
    Replies: 5
    Last Post: 07-17-2013, 11:48 AM
  3. Replies: 1
    Last Post: 04-11-2012, 03:30 PM
  4. Replies: 3
    Last Post: 08-17-2010, 02:49 PM
  5. Replies: 9
    Last Post: 03-24-2009, 09:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums