Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: Fill unbound field via indirect lookup?

    I need some help with an Access 2007 DB creation. I made a form that has one field that has to populated via some lookup or query. For the life of me this simple task still evades me. Here are the details

    Table TA:
    TA-ID: automumber
    TA-Name: Text (unique)
    TA-Type: Text (a lookup into Type)

    Table Type:
    TypeID: autonumber
    TypeName: Text (unique)

    Table TB:
    TB-ID: autonumber
    TB-Name: Text (unique)
    TB-Descr: Text
    TA-Name: Text (a lookup into Type)

    Relationships:
    For each TA there are 0 to many TBs.
    For each TB there are 0 to 1 TA.
    For each TA there are 0 to 1 Type.

    The objective is to create a main form to create or edit one TB. The form must allow the user to select the TA-Name from TA and store in in TB.TA-Name. The form contains an unbound field that lets the user select the TypeName from Type and store the result in TA.TA-Type. The user CAN change this selection.
    The direct look-ups are easy, but the indirect ones are killing me. How the hell do I do this? Of course the SQL queries in Access always seem to change. And Old columns names seem to appear everywhere. I will try V-Tools for that.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What precisely to you mean by "indirect lookup"?
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Your scenario is kind of hard to follow, but this kind of thing is normally done in Access using the DLoopup() function.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    May 2011
    Posts
    3
    Quote Originally Posted by Sinndho View Post
    What precisely to you mean by "indirect lookup"?
    In the creating table TA, I used the lookup wizard to create a direct lookup: TA-Type displays the TypeName of the Type table. TB.TA-Name is also a direct lookup into TA.

    The form will display the TA-Name value, but it must also display the correcponding Type.TypeName, an so this is indirect (TB.TA-Name -> TA.TA-Name -> TA.TA-Type -> Type.TypeID -> Type.TypeName).

  5. #5
    Join Date
    May 2011
    Posts
    3
    Quote Originally Posted by Missinglinq View Post
    Your scenario is kind of hard to follow, but this kind of thing is normally done in Access using the DLoopup() function.

    Linq ;0)>
    Yiou may be correct. I just have no idea how to write that. I thought a query with a double inner join would work, but I cannot formulate it to test it. Access seems to find OLD column names in tables and inject those.

    And, if I change that unbound field to a combobox, I'm not sure what would also let the user change the TypeName that should come up there.

    Let me clarify the desired actionW When the form opens, it should show TB.TB-Name, TB.TB-Desc, TB.TA-Name, and Type.TypeName. The user should be able to change TB.TA-Name and have the corresponding TypeTypeName come up. The user should also be change the Type.TypeName for the TB.TA-Name.
    Last edited by cobracus; 05-06-11 at 16:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •