M
MarkCameraKid
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]));
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]));