Code:
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