Query Failing but if i run manually it works

[jazzy]

Run-time error '3067' Query or Input must contain at least one table or query. i created 2 manual queries Contract Guests and Contract Guests for Ceremony to help diagnose the issue. it appears that there is no value when it gets to DoCmd.RunSQL sqlOrigCeremonyCount if i end the code and manually run the Query values are there and they work fine. if i manually run the queries while in debug the queries have no values. any help woudl be appreciated

Private Sub PostDeposits_Click()Dim sqlDeposit1 As String
Dim sqlDeposit2 As String
Dim sqlDeposit3 As String
Dim sqlBalance As String
Dim sqlOrigGuestCount As String
Dim sqlOrigCeremonyCount As String


If DCount("ContractsID", "tbl_Invoice", "[ContractsID] = [Forms]![frm_Contract]![ContractsID]") = 0 Then


sqlDeposit1 = "INSERT INTO tbl_Invoice ( ContractsID, InvoiceDate, AmountDue, Description)" & _
"SELECT tbl_Contracts.ContractsID, [Forms]![frm_Contract]![1stDepositDue] AS 1stDepositDue, [Forms]![frm_Contract]![1stDepositAmount] AS 1stDepositAmount, '1st Deposit'" & _
" FROM tbl_Contracts WHERE(tbl_Contracts.ContractsID) = [Forms]![frm_Contract]![ContractsID]"


sqlDeposit2 = "INSERT INTO tbl_Invoice ( ContractsID, InvoiceDate, AmountDue, Description)" & _
"SELECT tbl_Contracts.ContractsID, [Forms]![frm_Contract]![2ndDepositDue] AS 2ndDepositDue, [Forms]![frm_Contract]![2ndDepositAmount] AS 2ndDepositAmount, '2nd Deposit'" & _
" FROM tbl_Contracts WHERE(tbl_Contracts.ContractsID) = [Forms]![frm_Contract]![ContractsID]"


sqlDeposit3 = "INSERT INTO tbl_Invoice ( ContractsID, InvoiceDate, AmountDue, Description)" & _
"SELECT tbl_Contracts.ContractsID, [Forms]![frm_Contract]![3rdDepositDue] AS 3rdDepositDue, [Forms]![frm_Contract]![3rdDepositAmount] AS 3rdDepositAmount, '3rd Deposit'" & _
" FROM tbl_Contracts WHERE(tbl_Contracts.ContractsID) = [Forms]![frm_Contract]![ContractsID]"


sqlBalance = "INSERT INTO tbl_Invoice ( ContractsID, InvoiceDate, AmountDue, Description)" & _
"SELECT tbl_Contracts.ContractsID, [Forms]![frm_Contract]![BalanceDue] AS BalanceDue, [Forms]![frm_Contract]![BalanceAmount] AS BalanceAmoun, 'Final Balance'" & _
" FROM tbl_Contracts WHERE(tbl_Contracts.ContractsID) = [Forms]![frm_Contract]![ContractsID]"


sqlOrigGuestCount = "INSERT INTO tbl_InvoiceLineItem ( ContractsID, InvoiceIDDate, InvoiceID, InvoiceLineItemDesc, InvoiceLineItemQty, InvoiceLineItemCost, Tax, ServiceCharge )" & _
"SELECT tbl_Invoice.ContractsID, Date() AS InvoiceIDDate, Last(tbl_Invoice.InvoiceID) AS LastOfInvoiceID, ""Contract Guests"" AS InvoiceLineItemDesc, tbl_Contracts.MinGuaranteed, 0 AS InvoiceLineItemCost, -1 AS Tax, -1 AS ServiceCharge " & _
"FROM tbl_Invoice INNER JOIN tbl_Contracts ON tbl_Invoice.ContractsID = tbl_Contracts.ContractsID " & _
"GROUP BY tbl_Invoice.ContractsID, Date(), ""Contract Guests"", tbl_Contracts.MinGuaranteed, 0, -1 " & _
"HAVING (((tbl_Invoice.ContractsID)=[Forms]![frm_Contract]![ContractsID]));"


sqlOrigCeremonyCount = "INSERT INTO tbl_InvoiceLineItem ( ContractsID, InvoiceIDDate, InvoiceID, InvoiceLineItemDesc, InvoiceLineItemQty, InvoiceLineItemCost, Tax, ServiceCharge )" & _
"SELECT tbl_Invoice.ContractsID, Date() AS InvoiceIDDate, Last(tbl_Invoice.InvoiceID) AS LastOfInvoiceID, ""Contract Guests for Ceremony"" AS InvoiceLineItemDesc, tbl_Contracts.MinGuaranteed, 0 AS InvoiceLineItemCost, -1 AS Tax, -1 AS ServiceCharge" & _
"FROM tbl_Invoice INNER JOIN tbl_Contracts ON tbl_Invoice.ContractsID = tbl_Contracts.ContractsID" & _
"GROUP BY tbl_Invoice.ContractsID, Date(), ""Contract Guests for Ceremony"",tbl_Contracts.MinGuaranteed, 0, -1, tbl_Contracts.CeremonyPPFee" & _
"HAVING (((tbl_Invoice.ContractsID)=[Forms]![frm_Contract]![ContractsID]) AND ((tbl_Contracts.CeremonyPPFee)>0));"
        
DoCmd.SetWarnings False
DoCmd.RunSQL sqlDeposit1
DoCmd.RunSQL sqlDeposit2
DoCmd.RunSQL sqlDeposit3
DoCmd.RunSQL sqlBalance
DoCmd.RunSQL sqlOrigGuestCount
DoCmd.RunSQL sqlOrigCeremonyCount
DoCmd.SetWarnings True


Else
MsgBox "Payments have already been posted", vbCritical, "Payments Already Posted"


End If


Me.Refresh


End Sub

[ranman256]

just run them as queries... don't use sql.

[CJ_London]

looks like you are missing spaces before your select, group by and having

but is also the case for all your other sql selects as well

recommend you develop the principle of always putting your spaces at the start of a line rather than at the end - easier to see if they are there

[Gicu]

Hi Jason,
You need to isolate the variables, otherwise the SQL gets their literal value, here is the first one:

sqlDeposit1 = "INSERT INTO tbl_Invoice ( ContractsID, InvoiceDate, AmountDue, Description)" & _
"SELECT tbl_Contracts.ContractsID, #" & [Forms]![frm_Contract]![1stDepositDue] & "# AS 1stDepositDue, " & [Forms]![frm_Contract]![1stDepositAmount] & " AS 1stDepositAmount, '1st Deposit'" & _
" FROM tbl_Contracts WHERE(tbl_Contracts.ContractsID) = " & [Forms]![frm_Contract]![ContractsID]

Cheers,

[June7]

@Gicu, RunSql should be able to process the embedded control references. CurrentDb.Execute requires concatenation.

[jazzy]

running them as queries fixed the issue.

Vlad i isolated the variables but still got same error.

[CJ_London]

still got same error.

see post #3

[June7]

As noted, the SQL in VBA is missing spaces. This means text runs together. Do a Debug.Print sqlOrigCeremonyCount to see this.

Your other SQL statements have the spaces.