Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2

    Red face Unanswered: Displaying fields from different tables on the same form

    Hi all,

    I have been struggling with a simple issue that hopefully you can help me out with.

    I have a form that displays data from a table (SoftwareKeys).

    The data is;
    KeyNo
    Supplier
    ProductName

    On the same form I also want to be able to display data from a second table based on KeyNo. (One-To-Many relationship)

    In a second table (SoftwareKeyCodes) I have the fields
    KeyNo
    ProductLevel
    SoftwareKeyCodes
    DateCreated

    On the form I want to display DateCreated from this table (SoftwareKeyCodes).

    I have been using the code
    =DLookUp("[DateCreated]","SoftwareKeyCodes","KeyNo='" & [KeyNo] & "'")

    but it returns the date of the last entry for the KeyNo not the date associated with the entry date of the record.

    Can someone kindly tell me where I am stuffing up

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What about putting the second table in a sub form on the main form
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Myal

    That's how I started but for the application it looked to messy. The idea is that the operator can just see what products etc a customer has. There is no editing or adding available on the form.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Bigger screen lol

    What about using tabs
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Myle

    Again I played with this. Again overkill for the app.

    The user has a form on which there is a lot of customer related informaton. Clicking a button opens a small pop up form that gives quick, clear information about products that the user can easily absorb. Tabs, sub forms and the like seem to confuse or unnecessarily complicate what should be a simple presentation of data.

    This is why I want to keep the screen simple.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by sheusz View Post
    I have been using the code
    =DLookUp("[DateCreated]","SoftwareKeyCodes","KeyNo='" & [KeyNo] & "'")

    but it returns the date of the last entry for the KeyNo not the date associated with the entry date of the record.
    If the DLookUp() function returns the last entry for the KeyNo this means that there are more than one row in [DateCreated] with the same KeyNo.

    Obviously you have a One ([SoftwareKeys]) to Many ([DateCreated]) relationship between both tables, so how can you uniquely identify a row in [DateCreated] with a row in [SoftwareKeys]?

    In other words, what can determine which row in [DateCreated] having a given KeyNo must be selected while several rows in that table can have the same KeyNo?
    Have a nice day!

  7. #7
    Join Date
    Sep 2010
    Posts
    85
    Provided Answers: 2
    Hi Sinndho

    You are correct. A key can have multiple keycodes associated with it created at different times, hence the separate table to track this.

    In the end I have sorted it out. I created a simple query on both table and linked the form to that. Should have thought of that before postng. Always more than one way to crack a nut!

    Thanks for your answers and time.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome ! Glad you could sort it out.
    Have a nice day!

Posting Permissions

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