Run an append query from a form control event procedure

G

Guest

I have a rather involved append query that I need to run from a form's
Command Button on click event procedure. The SQL statement has many string
expressions and the syntax is tough to get right using the DoCmd.RunSQL
Method.

New and Feeling Dumb Developer looking for a better Way. Could use some
advice.

The Query Generates Orders with minimal user input on a form.

The SQL Version of the working append Query is as Follows.

SQL Statement: INSERT INTO Orders ( ClientID, ClientAssigned, EventID,
ContractID, CustomerID, OrganizationID, PackageStructureID, ErrorPSID,
ReOrderPSID, PackageOrderTypeID, PhotoTypeID, PaymentType1ID, Payment1Amount,
PackageOrderTotal, DifferenceOrder, PhotographerID, DataEntryByID,
DataEntryDate, TaxIncluded, CompositeCount8x10, GroupCount5x7 )
SELECT Clients.ClientID, True AS Exp2,
[Forms].[GrouporCompositeSeperateSale].[EventID] AS Exp3,
[Forms].[GrouporCompositeSeperateSale].[ContractID] AS Exp23,
DLookUp("CustomerID","Contracts","ContractID = " & [RosterID]) AS Expr4,
DLookUp("OrganizationID","Customers","CustomerID = " &
DLookUp("CustomerID","Contracts","ContractID = " & [RosterID])) AS Expr5,
DLookUp("PackageStructureID","Contracts","ContractID = " & [RosterID]) AS
Expr6, DLookUp("ErrorPSID","Contracts","ContractID = " & [RosterID]) AS
Expr7, DLookUp("ReOrderPSID","Contracts","ContractID = " & [RosterID]) AS
Expr8, 9 AS Exp9, 4 AS Exp10, IIf([Check],2,1) AS Expr11, ([Qty])*4 AS Exp12,
([Qty])*4 AS Exp13, 0 AS Exp14,
[Forms].[GrouporCompositeSeperateSale].[Photographer] AS Exp15,
[Forms].[Logon].[User] AS Exp16, Date() AS Exp17, True AS Expr18,
IIf(DLookUp("Composites","PackageStructures","PackageStructureID = " &
DLookUp("PackageStructureID","Contracts","ContractID = " &
[RosterID])),[Qty],0) AS Expr19,
IIf(DLookUp("Groups","PackageStructures","PackageStructureID = " &
DLookUp("PackageStructureID","Contracts","ContractID = " &
[RosterID])),[Qty],0) AS Expr21
FROM Clients
WHERE (((Clients.Submitted)=False) AND ((Clients.CompositeGroup)=True) AND
((Clients.RosterID)=[Forms]![GrouporCompositeSeperateSale]![ContractID]));
 
M

[MVP] S.Clark

Egad.

Is there a reason that the Contracts and PackageStructures tables can't be
linked to the Orders table? (Thus requiring this infestation of Dlookups?)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access MS Access: Avoiding duplicates from an append query using DLookup? 3
Appending data from a form in an Append Values Query 1
Run Query usinf Data from a form 6
run append query from form 3
Can I run an append query from inside a form? 6
How do I run an append query from a button like northwind sw/brd 1

Top