Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2005
    Posts
    41

    Unanswered: Appending record from a form

    I have just stumbled into an issue and need your expert help once again. I have a form that allows the end user to enter a new client. Once they finish entering the client I have an append query that appends the information from the form to the master table. The issue is that when the append query runs it is appending 32000 records. I verified this by changing the query to select and viewing. In the past I would have just clicked on the sigma and set the group by but becuase this database is housed on a server it will not allow me to use the group by function. I also verified that the query is set to unique records only.

    Thanks,
    Jeff

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Sounds like there is a join missing. Can you post the SQL statement that is doing the append?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2005
    Posts
    41

    Append SQL

    Here is the SQL that is being used to append the record from the form.

    INSERT INTO HeaderInfo ( Policy, CustName, Product, CDH, PolEffDate, [Set], PayEng, ProdYr, MYUHC, PlanName, PC_EffDate, CustDate, [Foreign Key Id] )
    SELECT DISTINCTROW Forms![Add a Record]![New_Policy#] AS Expr1, Forms![Add a Record]!New_CustomerName AS Expr2, Forms![Add a Record]!New_Product AS Expr3, Forms![Add a Record]!New_CDH AS Expr4, Forms![Add a Record]!New_PolEffDate AS Expr5, Forms![Add a Record]![New_Set#] AS Expr6, Forms![Add a Record]!New_PayEng AS Expr7, Forms![Add a Record]!New_ProdYr AS Expr8, Forms![Add a Record]!New_MYUHC AS Expr9, Forms![Add a Record]!New_PlanName AS Expr10, Forms![Add a Record]!New_PC_EffDate AS Expr11, Forms![Add a Record]!New_CustDate AS Expr12, Forms![Add a Record]![New_Policy#] & Forms![Add a Record]![New_Set#] AS Expr13
    FROM HeaderInfo;

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Do away with the "From HeaderInfo" on the end. You don't need to pull any records "from" the table to make the insert.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Feb 2005
    Posts
    41

    Thank you!

    All I can say is thank you!

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Actually, you don't need the "distinctrow" either. Your form contains only one record.
    Inspiration Through Fermentation

Posting Permissions

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