Adding True/False condition to my code

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
 
M

MikeB

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()
[snip]

End Sub

I don't think it's an Access problem, more a VB problem. Ialso don't
see which line of code you think is "line 10", since I suspect some
line folding happened when you pasted the code.

However, I think it would be safe to check your field early on, say
before

lngInvoiceID = NextInvoiceID

And simply exit the Sub if the value is set to false.
 
B

Bob V

Thanks Mike but cant really do a complete out as there might be 4 Owners in
a Horse
and just one of them may have a False and the 3 Others True
Thanks for helping..........Bob

MikeB said:
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()
[snip]

End Sub

I don't think it's an Access problem, more a VB problem. Ialso don't
see which line of code you think is "line 10", since I suspect some
line folding happened when you pasted the code.

However, I think it would be safe to check your field early on, say
before

lngInvoiceID = NextInvoiceID

And simply exit the Sub if the value is set to false.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Field Size Dilemma 12
Ok This one is Tuff 3
Using a Check Box as a Condition 4
Change Message Box vb 5

Top