Thanks, I would have posted it before but it was enormous. This is it with
the four expression fields and a couple of the straight field to field ones.
(sorry, about my poor terminology):
INSERT INTO [Quotes Sub] ( [Quote Number], Customer, [Order], [Sub Date],
[Design Number], [Unit Cost], [Unit Sale], [Unit Profit], Quantity, [QS
Number] )
SELECT DISTINCT [Forms]![Quotes New]![Quote Number] AS [Quote Number],
[Forms]![Quotes New]![Customer] AS Customer, Nz(DMax("[Order]","[Quotes
Sub]","[Quote Number]=Forms![Quotes New]![Quote Number]"),0)+1 AS [Order],
Date() AS [Sub Date], [Quotes Sub].[Design Number], [Quotes Sub].[Unit Cost],
[Quotes Sub].[Unit Sale], [Quotes Sub].[Unit Profit], [Quotes Sub].Quantity,
[Quotes Sub].[QS Number]
FROM [Quotes Sub]
WHERE ((([Quotes Sub].[QS Number])=[Forms]![Quotes New - Copy Sub]![QS
Number List]));
The two problem ones are [Quote Number] and [Customer]. Both of which are
just text fields in the form "Quotes New"
I have got around it by using a third SQL statement which uses the above
query name ("Copy Sub - Selected") adding the two problem fields separately
like this:
DoCmd.RunSQL "INSERT INTO [Quotes Sub] ( Customer, [Quote Number] )" & _
"SELECT [Copy Sub - Selected].*, [Forms]![Quotes New]![Customer]
AS Customer, [Forms]![Quotes New]![Quote Number] AS [Quote Number]" & _
"FROM [Copy Sub - Selected]"
It probably isn't the most efficient way to do it though. I did come across
an article here:
http://support.microsoft.com/kb/125259
which might have something to do with it.