Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forms based on Queries (Access2003)

    I'm trying to create a form based on a query from two tables with a one-to-many relationship. Using four fields (two from each table) I find that, in some of the fields on the form, I can't enter data. Yet, when I create a query that contains all the fields from both tables, and then base the form on the same four fields, it works. Any explanation would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms based on Queries (Access2003)

    Have you set any additional properties in the problem query, such as Unique Values = Yes?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms based on Queries (Access2003)

    Hans: Thanks for the reply but no, no Unique Values were entered although we tried to use a criteria of less than $100 but that didn't affect the resulting query/form results.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms based on Queries (Access2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms based on Queries (Access2003)

    Hans: Here is a copy of the version of the database I'm talking about. Open the existing form "Items Over $100 Query" and try to add "Kitchen" (room), then try to add "Chairs" or anything else. Access won't allow you to do it. Thanks for any light you can shed on this problem.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms based on Queries (Access2003)

    You must use the RoomID field from the Item table in the query, not that from the Room table.
    Note: the Item Name column occurs twice.

    BTW: I would create a main form based on the Room table and a subform based on the Item table.

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forms based on Queries (Access2003)

    Thanks for the help Hans. It works like a charm. I imagine what you suggested is required because, otherwise, there is no link back to the "one" table. Is it safe to assume that?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Forms based on Queries (Access2003)

    Yes, that's it, basically.

Posting Permissions

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