B
Bob V
I have added a field to [tblHorseDetails] [Invoicing] Defalt True ,Yes/No
field
I want this code not distribute to that OwnerID/HorseID record if it is
False
10th line down is where it happens
Thanks if anyone can help me with this.........Bob
'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()
Dim recTmpOwner As New ADODB.Recordset, strTmp As String
recTmpOwner.Open "SELECT CompanyID FROM tblCompanyInfo WHERE CompanyName
LIKE '" _
& tbCompanyName.value & "'", cnnStableAccount, adOpenDynamic,
adLockOptimistic
lngInvoiceID = NextInvoiceID
With recInvoice
Dim recHorseOwners As New ADODB.Recordset, dblOwnerPercentAmount As
Double
Dim dblTotal As Double, dblGSTContentsValue As Double
recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& val(tbHorseID.value) & " AND OwnerID > 0 ORDER BY OwnerID ",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then
recHorseOwners.Close
Set recHorseOwners = Nothing
MsgBox "This Horse Has No Owner At ALL.", vbApplicationModal +
vbOKOnly + vbInformation
.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"), 0)
.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = val(tbHorseID.value)
.Fields("HorseName") = tbHorseName1.value
.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value
If tbDOB.value = "" Or IsNull(tbDOB.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDOB.value),
"mm/dd/yyyy")
.Fields("HorseDetailInfo") = tbFatherName.value & "--" &
tbMotherName.value & "--" & funCalcAge(Format(tbDOB.value, "dd-mmm-yyyy"),
Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & "-" & tbSex.value
End If
.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = tbGSTOptions.value
.Fields("GSTOptionsValue") = tbGSTOptionsValue.value
.Fields("SubTotal") = tbSubTotal.value
.Fields("TotalAmount") = tbTotalAmount.value
If tbInvoiceDate.value = "" Or IsNull(tbInvoiceDate.value) Then
Else
.Fields("InvoiceDate") = Format(tbInvoiceDate.value,
"dd/mm/yyyy")
End If
Exit Sub
End If
recHorseOwners.MoveFirst
Dim nloop As Long
Do Until recHorseOwners.EOF = True
Dim recOwnersInfo As New ADODB.Recordset
recOwnersInfo.Open "SELECT
OwnerID,IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & '
') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerFirstName),'',trim(Left(tblOwnerInfo.OwnerFirstName,1))
& ' ') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) AS
Name, " _
& "OwnerAddress FROM tblOwnerInfo WHERE OwnerID=" &
val(recHorseOwners.Fields("OwnerID")), _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recOwnersInfo.EOF = True And recOwnersInfo.BOF = True
Then
recOwnersInfo.Close
Set recOwnersInfo = Nothing
Else
'
dblTotal = IIf(tbTotalAmount.value = "" Or
IsNull(tbTotalAmount.value), 0, val(tbTotalAmount.value))
dblOwnerPercentAmount =
IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")
.Fields("OwnerName") = Nz(recOwnersInfo.Fields("Name"),
"")
.Fields("OwnerAddress") =
recOwnersInfo.Fields("OwnerAddress")
.Fields("OwnerPercent") =
IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount
dblGSTContentsValue = (dblOwnerPercentAmount / 9)
.Fields("GSTContentsValue") = dblGSTContentsValue
If dblGSTContentsValue > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf dblGSTContentsValue < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing
If chkByCheque.value = True Then
recInvoice.Fields("InvoiceNo") = NextInvoiceNo
Else
recInvoice.Fields("InvoiceNo") = 0
End If
.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"),
0)
.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = val(tbHorseID.value)
.Fields("HorseName") = tbHorseName1.value
.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value
If tbDOB.value = "" Or IsNull(tbDOB.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDOB.value),
"mm/dd/yyyy")
.Fields("HorseDetailInfo") = tbFatherName.value & " -- "
& tbMotherName.value & " -- " & funCalcAge(Format(tbDOB.value,
"dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & " -- "
& tbSex.value
End If
.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = tbGSTOptions.value
.Fields("GSTOptionsValue") = tbGSTOptionsValue.value
.Fields("SubTotal") = tbSubTotal.value
.Fields("TotalAmount") = tbTotalAmount.value
If tbInvoiceDate.value = "" Or IsNull(tbInvoiceDate.value)
Then
Else
.Fields("InvoiceDate") = Format(tbInvoiceDate.value,
"dd/mm/yyyy")
End If
strTmp = " tblInvoice.InvoiceID=" & lngInvoiceID
subSetInvoiceDetailsValues
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = NextInvoiceID
strExpressionOrgument = strExpressionOrgument & strTmp &
" OR "
Else
strExpressionOrgument = strExpressionOrgument & strTmp
End If
Loop
End With
recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
End Sub
field
I want this code not distribute to that OwnerID/HorseID record if it is
False
10th line down is where it happens
Thanks if anyone can help me with this.........Bob
'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()
Dim recTmpOwner As New ADODB.Recordset, strTmp As String
recTmpOwner.Open "SELECT CompanyID FROM tblCompanyInfo WHERE CompanyName
LIKE '" _
& tbCompanyName.value & "'", cnnStableAccount, adOpenDynamic,
adLockOptimistic
lngInvoiceID = NextInvoiceID
With recInvoice
Dim recHorseOwners As New ADODB.Recordset, dblOwnerPercentAmount As
Double
Dim dblTotal As Double, dblGSTContentsValue As Double
recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& val(tbHorseID.value) & " AND OwnerID > 0 ORDER BY OwnerID ",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then
recHorseOwners.Close
Set recHorseOwners = Nothing
MsgBox "This Horse Has No Owner At ALL.", vbApplicationModal +
vbOKOnly + vbInformation
.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"), 0)
.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = val(tbHorseID.value)
.Fields("HorseName") = tbHorseName1.value
.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value
If tbDOB.value = "" Or IsNull(tbDOB.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDOB.value),
"mm/dd/yyyy")
.Fields("HorseDetailInfo") = tbFatherName.value & "--" &
tbMotherName.value & "--" & funCalcAge(Format(tbDOB.value, "dd-mmm-yyyy"),
Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & "-" & tbSex.value
End If
.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = tbGSTOptions.value
.Fields("GSTOptionsValue") = tbGSTOptionsValue.value
.Fields("SubTotal") = tbSubTotal.value
.Fields("TotalAmount") = tbTotalAmount.value
If tbInvoiceDate.value = "" Or IsNull(tbInvoiceDate.value) Then
Else
.Fields("InvoiceDate") = Format(tbInvoiceDate.value,
"dd/mm/yyyy")
End If
Exit Sub
End If
recHorseOwners.MoveFirst
Dim nloop As Long
Do Until recHorseOwners.EOF = True
Dim recOwnersInfo As New ADODB.Recordset
recOwnersInfo.Open "SELECT
OwnerID,IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & '
') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerFirstName),'',trim(Left(tblOwnerInfo.OwnerFirstName,1))
& ' ') & " _
&
"IIf(isnull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName) AS
Name, " _
& "OwnerAddress FROM tblOwnerInfo WHERE OwnerID=" &
val(recHorseOwners.Fields("OwnerID")), _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recOwnersInfo.EOF = True And recOwnersInfo.BOF = True
Then
recOwnersInfo.Close
Set recOwnersInfo = Nothing
Else
'
dblTotal = IIf(tbTotalAmount.value = "" Or
IsNull(tbTotalAmount.value), 0, val(tbTotalAmount.value))
dblOwnerPercentAmount =
IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")
.Fields("OwnerName") = Nz(recOwnersInfo.Fields("Name"),
"")
.Fields("OwnerAddress") =
recOwnersInfo.Fields("OwnerAddress")
.Fields("OwnerPercent") =
IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount
dblGSTContentsValue = (dblOwnerPercentAmount / 9)
.Fields("GSTContentsValue") = dblGSTContentsValue
If dblGSTContentsValue > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf dblGSTContentsValue < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing
If chkByCheque.value = True Then
recInvoice.Fields("InvoiceNo") = NextInvoiceNo
Else
recInvoice.Fields("InvoiceNo") = 0
End If
.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"),
0)
.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = val(tbHorseID.value)
.Fields("HorseName") = tbHorseName1.value
.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value
If tbDOB.value = "" Or IsNull(tbDOB.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDOB.value),
"mm/dd/yyyy")
.Fields("HorseDetailInfo") = tbFatherName.value & " -- "
& tbMotherName.value & " -- " & funCalcAge(Format(tbDOB.value,
"dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & " -- "
& tbSex.value
End If
.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = tbGSTOptions.value
.Fields("GSTOptionsValue") = tbGSTOptionsValue.value
.Fields("SubTotal") = tbSubTotal.value
.Fields("TotalAmount") = tbTotalAmount.value
If tbInvoiceDate.value = "" Or IsNull(tbInvoiceDate.value)
Then
Else
.Fields("InvoiceDate") = Format(tbInvoiceDate.value,
"dd/mm/yyyy")
End If
strTmp = " tblInvoice.InvoiceID=" & lngInvoiceID
subSetInvoiceDetailsValues
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = NextInvoiceID
strExpressionOrgument = strExpressionOrgument & strTmp &
" OR "
Else
strExpressionOrgument = strExpressionOrgument & strTmp
End If
Loop
End With
recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
End Sub