Adding Records using a Form

[walbr02]

I am trying to add a new Expense to my Expense table using an unbound form:

INSERT INTO Expenses ( ProjectID, Employee, [Expense Type], [Total Expense], [Date Purchased], [Date Submitted], [Payment Method], [Purpose of Expense] )

SELECT Projects.ProjectID, Forms![New Expense]!Combo2 AS Employee, Forms![New Expense]!Combo4 AS [Expense Type], Forms![New Expense]!Text6 AS [Total Expense], Forms![New Expense]!Text10 AS [Date Purchased], Forms![New Expense]!Text8 AS [Date Submitted], Forms![New Expense]!Combo12 AS [Payment Method], Forms![New Expense]!Text14 AS [Purpose of Expense]

FROM Projects INNER JOIN Expenses ON Projects.ProjectID = Expenses.ProjectID
WHERE (((Projects.ProjectID)=[Forms]![New Expense]![List20]));

The problem that I am running into is that instead of adding just one new record, I end up adding duplicate records that increase exponentially based on the number of times a ProjectID is in the Expense table.

I think that the problem is the relationships between my Project Table and my Expense Table, but I do not know how to fix it.

Any help would be GREATLY appreciated.
Thanks!

 

[RickSpr]

When an INSERT INTO statement contains a SELECT clause (a subquery), you insert one row into the target table for each row selected in the subquery. Your subquery is an inner join, which returns one row for every Expenses table row that has the given project id.

One way to fix it would be to eliminate the inner join, and just select from the Projects table. You aren't selecting any columns from the Expenses table anyway, so you don't need it.

But really, all you're selecting from the Projects table is the ProjectID, which you already have in your List20 control. So you don't actually need the subquery at all. Just insert the values you already have into the table. The syntax is:
INSERT INTO Expenses ( ProjectID, Employee, [Expense Type], [Total Expense], [Date Purchased], [Date Submitted], [Payment Method], [Purpose of Expense] )

Forms![New Expense]!List20 As ProjectID, Forms![New Expense]!Combo2 AS Employee, Forms![New Expense]!Combo4 AS [Expense Type], Forms![New Expense]!Text6 AS [Total Expense], Forms![New Expense]!Text10 AS [Date Purchased], Forms![New Expense]!Text8 AS [Date Submitted], Forms![New Expense]!Combo12 AS [Payment Method], Forms![New Expense]!Text14 AS [Purpose of Expense]
Rick Sprague

 

[walbr02]

Worked like a charm!
Thanks for your help!