Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I'm new toAccess and I can't get something to work that seems like it should be easy. I've been searching for an answer for this but I suspect it may be too basic for this forum. I hope you can help me.
    I have a database of contract information. One table is called "Contracts" (key is "Contract ID"). Another table is called "Referenced Contracts". This table contains 3 fields (Contract ID, RefKey ID, and RefCon ID). The concept is that one contract can refer to many other contracts. No problem so far. I have a form with a subform in it that allows me to build and view multiple "RefCon ID"s for any contract. The problem is, the ID isn't very descriptive and I can't figure out how to display other information.
    The "Contracts" table has fields like "Title", "Effective Date" and "Total Amount" that would help us understand what contracts we are referring to. I'd like to display them in the subform. I don't want to add these fields to the "Referenced Contracts" table because they would be redundant. I thought I could add 3 text fields to the subform and either bind them to the "Contracts" fields or build a query to retrieve them using "RefCon ID" as the key to the "Contracts" table. I have a feeling I need to use the Expression Builder to make this a calculated field but I can't make heads or tails of it for building a query. I keep expecting to see something like "select [Contracts].Title where [Contracts.Contract ID] = [Referenced Contracts].[RefCon ID] but it doesn't look anything like that! Can you help me display the Title and other fields and clear up my confusion about query VS expression builder?
    Thanks for any help you can give me!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd create a new query in design view.
    Add the Referenced Contracts and Contracts tables to the query.
    If Access automatically joins the tables on Contract ID vs Contract ID, delete this join by clicking on the line and pressing the Delete key.
    Join the tables on RefCon ID vs Contract ID by dragging the RefCon ID field from the Referenced Contracts table to the Contracts table and dropping it on the Contract ID field.
    Next, add the Contract ID, RefKey ID, and RefCon ID fields from Referenced Contracts to the query grid, as well as Title, Effective Date and Total Amount from the Contracts table.
    Save this query, and use it as record source for the subform (which is to be linked to the main form on Contract ID vs Contract ID).

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wow! Once again you came through with exactly the right answer!!! Thank you so much. I did as you said and it works perfectly!

    Naturally, I have a follow up question... I want to protect the fields from the Contracts table because it looks like I can change them, however when I go back into design mode I can't see the subforms as I did before. I've been working in the main form and making any changes to subforms from there. This happens periodically and I usually just click back to forms view and then to design view and it works but this time it isn't working. Does a query as the record source affect this? Do I now have to work directly in the subform (not that big a deal) or is there some way to resolve this behavior so I can see the subforms in design mode?

    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think that is caused by the query as record source. This happens sometimes when you switch between form view and design view. If you close the form, then open it in design view, you should be able to see and edit the subform.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Right again! It seems to be working now. I don't know why it wasn't working earlier.
    Thanks again

Posting Permissions

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