Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform confusion (Access 2K, Win 2KPro, SQL Server 2K)

    this should be easy but I am getting befuddled...

    I have an access db with linked tables to SQL Server. I want to build a form on the linked table and place a subform off a query to hold multiple records tied to the linked table info. Therefore, I built a table to store the multiple records to support a query and constructed the subform off a query that links the unique ID (RequestID) on the SQL Table to an ID column in the supporting table. in the subform, I set the default to the ID to equal the RequestID displayed on the main form.

    The main table pulls info from the SQL Table and the subform pulls data from the one-to-many query. This all sounds good, at least to me, but when I try to add a record in the subform I get the 'can't save record as it will produce duplicate values'. warning. This seems to me to indicate that I am somehow attempting to write the same RequestID twice to the SQL Table, but I am not even including the Request ID field in the subform.

    I seem to recall there being some trick to using subforms that addresses this involving a hidden field with the unique ID from the main form on the subform and I'll look around at some other projects I've done in the past, but if you guys have some ideas please let me know.

    thanks!

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

    Re: Subform confusion (Access 2K, Win 2KPro, SQL Server 2K)

    The subform should be based on the table you constructed for the multiple records, not on a query that also includes fields from the main table.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform confusion (Access 2K, Win 2KPro, SQL Server 2K)

    yes I am starting to figure that out. i guess I went to a query with the thinking this is a one-to-many deal.

Posting Permissions

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