Private Sub cmbCreateInvoice_Click()
'to turn of warning for running sql statement
DoCmd.SetWarnings False
'to post the book: update tbDiagServMat and insert tbLedger, tbInvoice_Status
'update tbDiagServMat
'InvoiceDate = TempVars!pass_InvoiceDate
InvoiceDate = Me.txtInvoiceDate
PartnerID = TempVars!pass_PartnerID
HorseID = TempVars!pass_HorseID
InvoiceLast = DMax("InvoiceID", "tbDiagServMat")
InvoiceSerial = InvoiceLast + 1
invoicetotal = TempVars!pass_InvoiceTotal
Partner_Name = TempVars!Partner
If Me.OpenArgs = "Horse_Selection" Then
For i = 0 To hlRC
HorseID = HLforInvoice(0, i)
strUpdate = "UPDATE tbDiagServMat SET Status='accounted', InvoiceID=" & InvoiceSerial & ", " & _
"InvoiceDate=#" & InvoiceDate & "& WHERE OwnerID=" & PartnerID & " AND Status Is Null " & _
" AND HorseID=" & HorseID & ";"
DoCmd.RunSQL strUpdate
Next i
Else
strUpdate = "UPDATE tbDiagServMat SET Status='accounted', InvoiceID=" & InvoiceSerial & ", " & _
"InvoiceDate=#" & InvoiceDate & "# WHERE OwnerID=" & PartnerID & " AND Status Is Null;"
End If
MsgBox strUpdate
DoCmd.RunSQL strUpdate
'to make entry into tbLedger depending on option buttons
If Me.obTransfer = True Then
If Me.cmbTotalCurrency = "HUF" Then
AccID = 3
Else
AccID = 31
End If
Invoice_Status = "open"
InvMethod = "Bank"
ElseIf Me.obCash = True Then 'cash payment
'check if payment received is complete or partial
If Me.txtInvoiceTotal.Value = Me.txtReceived.Value Then 'full payment received
'check currency of payment
If Me.cmbTotalCurrency = "HUF" Then
AccID = 1 'HUF
Else
AccID = 11 'EUR
End If
For i = 1 To 2 'double entry with Cash/Revenue account
strLedger = "INSERT INTO tbLedger (Acc_Date,InvoiceDate,PartnerID,InvoiceID,Sum," & _
"Payment_Currency,AccId,Acc_Text) " & _
"VALUES (#" & InvoiceDate & "#,#" & InvoiceDate & "#, " & PartnerID & ", " & _
"" & InvoiceSerial & "," & invoicetotal & ",'" & Me.cmbTotalCurrency & "', " & _
"" & AccID & ",'" & Partner_Name & "');"
MsgBox strLedger
DoCmd.RunSQL strLedger
If Me.cmbTotalCurrency = "HUF" Then
AccID = 3 'HUF
Else
AccID = 31 'EUR
End If
'invoiceTotal = Me.txtReceived
invoicetotal = -invoicetotal
Next i
ElseIf Me.txtInvoiceTotal > Me.txtReceived Then 'partially payment received
'check currency of payment
If Me.cmbTotalCurrency = "HUF" Then
AccID = 3 'HUF
Else
AccID = 31 'EUR
End If
For i = 1 To 2 'double entry with AccRec/Revenue account
strLedger = "INSERT INTO tbLedger (Acc_Date,InvoiceDate,PartnerID,InvoiceID,Sum," & _
"Payment_Currency,AccId,Acc_Text) " & _
"VALUES (#" & InvoiceDate & "#,#" & InvoiceDate & "#, " & PartnerID & ", " & _
"" & InvoiceSerial & "," & invoicetotal & ",'" & Me.cmbTotalCurrency & "', " & _
"" & AccID & ",'" & Partner_Name & "');"
MsgBox strLedger
DoCmd.RunSQL strLedger
If Me.cmbTotalCurrency = "HUF" Then
AccID = 2 'HUF
Else
AccID = 21 'EUR
End If
'invoiceTotal = Me.txtReceived
invoicetotal = -invoicetotal
Next i
'check currency of payment
If Me.cmbTotalCurrency = "HUF" Then
AccID = 1 'HUF
Else
AccID = 11 'EUR
End If
invoicetotal = Me.txtReceived
For i = 1 To 2 'double entry with Cash/AccR account
strLedger = "INSERT INTO tbLedger (Acc_Date,InvoiceDate,PartnerID,InvoiceID,Sum," & _
"Payment_Currency,AccId,Acc_Text) " & _
"VALUES (#" & InvoiceDate & "#,#" & InvoiceDate & "#, " & PartnerID & ", " & _
"" & InvoiceSerial & "," & invoicetotal & ",'" & Me.cmbTotalCurrency & "', " & _
"" & AccID & ",'" & Partner_Name & "');"
MsgBox strLedger
DoCmd.RunSQL strLedger
If Me.cmbTotalCurrency = "HUF" Then
AccID = 3 'HUF
Else
AccID = 31 'EUR
End If
invoicetotal = -invoicetotal
Next i
End If
'invoicetotal = Me.txtInvoiceTotal - Me.txtReceive
Invoice_Status = "closed"
InvMethod = "Cash"
End If
'to make entry into tbInvoice_Status depending on option button
strInvoiceStatus = "INSERT INTO tbInvoice_Status (InvoiceID,Invoice_Status) " & _
"VALUES (" & InvoiceSerial & ",'" & Invoice_Status & "');"
DoCmd.RunSQL strInvoiceStatus
'to make entry into tbInvoices (global invoice table)
InvoiceDate = TempVars!pass_InvoiceDate
InvoiceDate = Me.txtInvoiceDate
PartnerID = TempVars!pass_PartnerID
HorseID = TempVars!pass_HorseID
InvoiceLast = DMax("InvoiceID", "tbDiagServMat")
InvoiceSerial = InvoiceLast
invoicetotal = TempVars!pass_InvoiceTotal
Partner_Name = TempVars!Partner
Dim strInvSQL As String
strInvSQL = "INSERT INTO tbInvoices (InvDate,InvID,PartnerID,InvTotal,InvCurrency, InvMethod) " & _
"VALUES (#" & InvoiceDate & "#, " & InvoiceSerial & "," & PartnerID & "," & invoicetotal & "," & _
" '" & Me.cmbTotalCurrency & "','" & InvMethod & "');"
MsgBox strInvSQL
DoCmd.RunSQL strInvSQL
'turn on warning after sql statement executed
DoCmd.SetWarnings True
Me.cmbCreateInvoice.Enabled = False
Me.fsubInvoice_Creator.Form.Requery
End Sub