Adding a Condition from Check Box

B

Bob Vance

Part of my code for Distributing Invoices is to give Invoice numbers, What I
am trying to do is give a zero to a Invoice that check box
[ckHoldingInvoice] = True
-------------------------------------------------
If (SubForm)subAdditionChargeChild (Checkbox)ckHoldingInvoice is True Then
InvoiceNo =0 Else
-----------------------------------------------
Part of my Code below!
Private Sub subSetInvoiceValues()
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1


If lngIntermediateID > lngItMdt Then
.AddNew

lngInvoiceNo = lngInvoiceNo + 1
lngItMdt = lngIntermediateID
 
A

Arvin Meyer [MVP]

Bob Vance said:
Part of my code for Distributing Invoices is to give Invoice numbers, What
I am trying to do is give a zero to a Invoice that check box
[ckHoldingInvoice] = True
-------------------------------------------------
If (SubForm)subAdditionChargeChild (Checkbox)ckHoldingInvoice is True Then
InvoiceNo =0 Else
-----------------------------------------------
Part of my Code below!
Private Sub subSetInvoiceValues()
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1


If lngIntermediateID > lngItMdt Then
.AddNew
Click to expand...

AddNew to what? In order to AddNew, you need a recordset to add it to.
Additionally, you need a With/End With construct to use AddNew the way you
show it. Sometimes you need the entire relevant code rather than a tiny
snippet.
 
B

Bob Vance

Sorry Arvin this is the whole code...............Thanks Bob

'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()
Dim recInvoice As ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim recInvoice_ItMdt As ADODB.Recordset
Set recInvoice_ItMdt = New ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim lngInvoiceID As Long
Dim lngInvoiceNo As Long
Dim lngIntermediateID As Long
recInvoice_ItMdt.Open "Select * from tblInvoice_ItMdt;",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

recInvoice.Open "Select * from tblInvoice;", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
'************************************************
lngInvoiceID = DMax("InvoiceID", "tblInvoice") + 1
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1
'************************************************
If recInvoice.BOF = False And recInvoice.EOF = False Then
recInvoice.MoveLast
End If

recInvoice.AddNew
Dim lngItMdt As Long
If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then
lngItMdt = recInvoice_ItMdt.Fields("IntermediateID")
End If
Do While Not recInvoice_ItMdt.EOF = True
lngIntermediateID = recInvoice_ItMdt.Fields("IntermediateID")

With recInvoice


Dim recHorseOwners As New ADODB.Recordset, curOwnerPercentAmount As
Currency
Dim curTotal As Currency, curGSTContentsValue As Currency

recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0) _
& " AND OwnerID > 0 AND Invoicing = False 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("InvoiceID") = lngInvoiceID
.Fields("HorseID") = Nz(val(recInvoice_ItMdt.Fields("HorseID")),
0)
.Fields("HorseName") = Nz(recInvoice_ItMdt.Fields("HorseName"),
"")
.Fields("FatherName") =
Nz(recInvoice_ItMdt.Fields("FatherName"), "")
.Fields("MotherName") =
Nz(recInvoice_ItMdt.Fields("MotherName"), "")

.Fields("DateOfBirth") =
Format(CDate(recInvoice_ItMdt.Fields("DateOfBirth")), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(recInvoice_ItMdt.Fields("DateOfBirth") _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"), 0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")
End If
recHorseOwners.MoveFirst
Do Until recHorseOwners.EOF = True

If lngIntermediateID > lngItMdt Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1
lngItMdt = lngIntermediateID
End If
Dim recOwnersInfo As New ADODB.Recordset


recOwnersInfo.Open "SELECT OwnerID," _
&
"IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ')" _
& " &
IIf(isnull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
')" _
& " &
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
'
curTotal = DSum("TotalAmount", "tblInvoice_ItMdt",
"HorseID=" _
& Nz(recInvoice_ItMdt.Fields("HorseID"), 0))
curOwnerPercentAmount = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or _
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
Format(curTotal, "#0.00") _
* recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")

.Fields("OwnerName") = 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") =
Format(curOwnerPercentAmount, "#0.00")

curGSTContentsValue = (Format(curOwnerPercentAmount,
"#0.00") / 9)
.Fields("GSTContentsValue") = Format(curGSTContentsValue,
"#0.00")

If Format(curGSTContentsValue, "#0.00") > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf Format(curGSTContentsValue, "#0.00") < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing

.Fields("InvoiceNo") = lngInvoiceNo


.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = recInvoice_ItMdt.Fields("HorseID")
.Fields("HorseName") = recInvoice_ItMdt.Fields("HorseName")
.Fields("FatherName") = recInvoice_ItMdt.Fields("FatherName")
.Fields("MotherName") = recInvoice_ItMdt.Fields("MotherName")
.Fields("DateOfBirth") =
Format(CDate(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0)), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(Nz(recInvoice_ItMdt.Fields("DateOfBirth"),
0) _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"), 0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")


Application.SysCmd acSysCmdSetStatus, "Invoice No=" &
..Fields("InvoiceNo") _
& " Horse Name=" & .Fields("HorseName") & " Owner Name=" _
& .Fields("OwnerName")

funSetInvoiceDetailValues lngIntermediateID, lngInvoiceID,
lngInvoiceNo
.Fields("CompanyID") = DLookup("CompanyID", "tblCompanyInfo")
recInvoice.Update
.Requery
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1

End If
Loop
.Update
End With
CurrentProject.Connection.Execute "Delete * from tblAddition_ItMdt where
IntermediateID=" _
& lngIntermediateID

CurrentProject.Connection.Execute "Delete * from tblDaily_ItMdt where
IntermediateID=" _
& lngIntermediateID

recHorseOwners.Close
recInvoice_ItMdt.MoveNext
Loop

Set recHorseOwners = Nothing

CurrentProject.Connection.Execute "Delete * from tblInvoice_ItMdt;"
End Sub
Arvin Meyer said:
Bob Vance said:
Part of my code for Distributing Invoices is to give Invoice numbers,
What I am trying to do is give a zero to a Invoice that check box
[ckHoldingInvoice] = True
-------------------------------------------------
If (SubForm)subAdditionChargeChild (Checkbox)ckHoldingInvoice is True
Then
InvoiceNo =0 Else
-----------------------------------------------
Part of my Code below!
Private Sub subSetInvoiceValues()
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1


If lngIntermediateID > lngItMdt Then
.AddNew
Click to expand...

AddNew to what? In order to AddNew, you need a recordset to add it to.
Additionally, you need a With/End With construct to use AddNew the way you
show it. Sometimes you need the entire relevant code rather than a tiny
snippet.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
 
A

Arvin Meyer [MVP]

Hi Bob,

I looked carefully (at least I think so) but I couldn't find
ckHoldingInvoice or any reference to it, anywhere in the current code. So if
you are trying to set it to zero (0) in response to a condition, something
else has to happen.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Bob Vance said:
Sorry Arvin this is the whole code...............Thanks Bob

'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()
Dim recInvoice As ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim recInvoice_ItMdt As ADODB.Recordset
Set recInvoice_ItMdt = New ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim lngInvoiceID As Long
Dim lngInvoiceNo As Long
Dim lngIntermediateID As Long
recInvoice_ItMdt.Open "Select * from tblInvoice_ItMdt;",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

recInvoice.Open "Select * from tblInvoice;", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
'************************************************
lngInvoiceID = DMax("InvoiceID", "tblInvoice") + 1
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1
'************************************************
If recInvoice.BOF = False And recInvoice.EOF = False Then
recInvoice.MoveLast
End If

recInvoice.AddNew
Dim lngItMdt As Long
If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then
lngItMdt = recInvoice_ItMdt.Fields("IntermediateID")
End If
Do While Not recInvoice_ItMdt.EOF = True
lngIntermediateID = recInvoice_ItMdt.Fields("IntermediateID")

With recInvoice


Dim recHorseOwners As New ADODB.Recordset, curOwnerPercentAmount As
Currency
Dim curTotal As Currency, curGSTContentsValue As Currency

recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0) _
& " AND OwnerID > 0 AND Invoicing = False 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("InvoiceID") = lngInvoiceID
.Fields("HorseID") =
Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0)
.Fields("HorseName") = Nz(recInvoice_ItMdt.Fields("HorseName"),
"")
.Fields("FatherName") =
Nz(recInvoice_ItMdt.Fields("FatherName"), "")
.Fields("MotherName") =
Nz(recInvoice_ItMdt.Fields("MotherName"), "")

.Fields("DateOfBirth") =
Format(CDate(recInvoice_ItMdt.Fields("DateOfBirth")), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(recInvoice_ItMdt.Fields("DateOfBirth") _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")
End If
recHorseOwners.MoveFirst
Do Until recHorseOwners.EOF = True

If lngIntermediateID > lngItMdt Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1
lngItMdt = lngIntermediateID
End If
Dim recOwnersInfo As New ADODB.Recordset


recOwnersInfo.Open "SELECT OwnerID," _
&
"IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ')" _
& " &
IIf(isnull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
')" _
& " &
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
'
curTotal = DSum("TotalAmount", "tblInvoice_ItMdt",
"HorseID=" _
& Nz(recInvoice_ItMdt.Fields("HorseID"), 0))
curOwnerPercentAmount = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or _
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
Format(curTotal, "#0.00") _
* recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")

.Fields("OwnerName") = 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") =
Format(curOwnerPercentAmount, "#0.00")

curGSTContentsValue = (Format(curOwnerPercentAmount,
"#0.00") / 9)
.Fields("GSTContentsValue") = Format(curGSTContentsValue,
"#0.00")

If Format(curGSTContentsValue, "#0.00") > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf Format(curGSTContentsValue, "#0.00") < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing

.Fields("InvoiceNo") = lngInvoiceNo


.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = recInvoice_ItMdt.Fields("HorseID")
.Fields("HorseName") = recInvoice_ItMdt.Fields("HorseName")
.Fields("FatherName") = recInvoice_ItMdt.Fields("FatherName")
.Fields("MotherName") = recInvoice_ItMdt.Fields("MotherName")
.Fields("DateOfBirth") =
Format(CDate(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0)), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(Nz(recInvoice_ItMdt.Fields("DateOfBirth"),
0) _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")


Application.SysCmd acSysCmdSetStatus, "Invoice No=" &
.Fields("InvoiceNo") _
& " Horse Name=" & .Fields("HorseName") & " Owner Name=" _
& .Fields("OwnerName")

funSetInvoiceDetailValues lngIntermediateID, lngInvoiceID,
lngInvoiceNo
.Fields("CompanyID") = DLookup("CompanyID", "tblCompanyInfo")
recInvoice.Update
.Requery
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1

End If
Loop
.Update
End With
CurrentProject.Connection.Execute "Delete * from tblAddition_ItMdt
where IntermediateID=" _
& lngIntermediateID

CurrentProject.Connection.Execute "Delete * from tblDaily_ItMdt where
IntermediateID=" _
& lngIntermediateID

recHorseOwners.Close
recInvoice_ItMdt.MoveNext
Loop

Set recHorseOwners = Nothing

CurrentProject.Connection.Execute "Delete * from tblInvoice_ItMdt;"
End Sub
Arvin Meyer said:
Bob Vance said:
Part of my code for Distributing Invoices is to give Invoice numbers,
What I am trying to do is give a zero to a Invoice that check box
[ckHoldingInvoice] = True
-------------------------------------------------
If (SubForm)subAdditionChargeChild (Checkbox)ckHoldingInvoice is True
Then
InvoiceNo =0 Else
-----------------------------------------------
Part of my Code below!
Private Sub subSetInvoiceValues()
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1


If lngIntermediateID > lngItMdt Then
.AddNew
Click to expand...

AddNew to what? In order to AddNew, you need a recordset to add it to.
Additionally, you need a With/End With construct to use AddNew the way
you show it. Sometimes you need the entire relevant code rather than a
tiny snippet.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
 
B

Bob Vance

Sorry Arvin that was my older version of my DB, You will see that I have
added a check Box [ckbNoNumber]
What am trying to do is IF [ckbNoNumber] is ticked (True) it will give the
Invoice a "0" Zero
'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 AND Invoicing = False
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
.Fields("NoNumber") =
ckbNoNumber.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,21))
& ' ') & " _
&
"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 chkHoldingCharge.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
Arvin Meyer said:
Hi Bob,

I looked carefully (at least I think so) but I couldn't find
ckHoldingInvoice or any reference to it, anywhere in the current code. So
if you are trying to set it to zero (0) in response to a condition,
something else has to happen.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Bob Vance said:
Sorry Arvin this is the whole code...............Thanks Bob

'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()
Dim recInvoice As ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim recInvoice_ItMdt As ADODB.Recordset
Set recInvoice_ItMdt = New ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim lngInvoiceID As Long
Dim lngInvoiceNo As Long
Dim lngIntermediateID As Long
recInvoice_ItMdt.Open "Select * from tblInvoice_ItMdt;",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

recInvoice.Open "Select * from tblInvoice;", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
'************************************************
lngInvoiceID = DMax("InvoiceID", "tblInvoice") + 1
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1
'************************************************
If recInvoice.BOF = False And recInvoice.EOF = False Then
recInvoice.MoveLast
End If

recInvoice.AddNew
Dim lngItMdt As Long
If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then
lngItMdt = recInvoice_ItMdt.Fields("IntermediateID")
End If
Do While Not recInvoice_ItMdt.EOF = True
lngIntermediateID = recInvoice_ItMdt.Fields("IntermediateID")

With recInvoice


Dim recHorseOwners As New ADODB.Recordset, curOwnerPercentAmount
As Currency
Dim curTotal As Currency, curGSTContentsValue As Currency

recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0) _
& " AND OwnerID > 0 AND Invoicing = False 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("InvoiceID") = lngInvoiceID
.Fields("HorseID") =
Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0)
.Fields("HorseName") =
Nz(recInvoice_ItMdt.Fields("HorseName"), "")
.Fields("FatherName") =
Nz(recInvoice_ItMdt.Fields("FatherName"), "")
.Fields("MotherName") =
Nz(recInvoice_ItMdt.Fields("MotherName"), "")

.Fields("DateOfBirth") =
Format(CDate(recInvoice_ItMdt.Fields("DateOfBirth")), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(recInvoice_ItMdt.Fields("DateOfBirth") _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")
End If
recHorseOwners.MoveFirst
Do Until recHorseOwners.EOF = True

If lngIntermediateID > lngItMdt Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1
lngItMdt = lngIntermediateID
End If
Dim recOwnersInfo As New ADODB.Recordset


recOwnersInfo.Open "SELECT OwnerID," _
&
"IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ')" _
& " &
IIf(isnull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName &
' ')" _
& " &
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
'
curTotal = DSum("TotalAmount", "tblInvoice_ItMdt",
"HorseID=" _
& Nz(recInvoice_ItMdt.Fields("HorseID"), 0))
curOwnerPercentAmount = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or _
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
Format(curTotal, "#0.00") _
* recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")

.Fields("OwnerName") = 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") =
Format(curOwnerPercentAmount, "#0.00")

curGSTContentsValue = (Format(curOwnerPercentAmount,
"#0.00") / 9)
.Fields("GSTContentsValue") = Format(curGSTContentsValue,
"#0.00")

If Format(curGSTContentsValue, "#0.00") > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf Format(curGSTContentsValue, "#0.00") < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing

.Fields("InvoiceNo") = lngInvoiceNo


.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = recInvoice_ItMdt.Fields("HorseID")
.Fields("HorseName") = recInvoice_ItMdt.Fields("HorseName")
.Fields("FatherName") = recInvoice_ItMdt.Fields("FatherName")
.Fields("MotherName") = recInvoice_ItMdt.Fields("MotherName")
.Fields("DateOfBirth") =
Format(CDate(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0)),
"mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(Nz(recInvoice_ItMdt.Fields("DateOfBirth"),
0) _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")


Application.SysCmd acSysCmdSetStatus, "Invoice No=" &
.Fields("InvoiceNo") _
& " Horse Name=" & .Fields("HorseName") & " Owner Name=" _
& .Fields("OwnerName")

funSetInvoiceDetailValues lngIntermediateID, lngInvoiceID,
lngInvoiceNo
.Fields("CompanyID") = DLookup("CompanyID", "tblCompanyInfo")
recInvoice.Update
.Requery
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1

End If
Loop
.Update
End With
CurrentProject.Connection.Execute "Delete * from tblAddition_ItMdt
where IntermediateID=" _
& lngIntermediateID

CurrentProject.Connection.Execute "Delete * from tblDaily_ItMdt where
IntermediateID=" _
& lngIntermediateID

recHorseOwners.Close
recInvoice_ItMdt.MoveNext
Loop

Set recHorseOwners = Nothing

CurrentProject.Connection.Execute "Delete * from tblInvoice_ItMdt;"
End Sub
Arvin Meyer said:
Part of my code for Distributing Invoices is to give Invoice numbers,
What I am trying to do is give a zero to a Invoice that check box
[ckHoldingInvoice] = True
-------------------------------------------------
If (SubForm)subAdditionChargeChild (Checkbox)ckHoldingInvoice is True
Then
InvoiceNo =0 Else
-----------------------------------------------
Part of my Code below!
Private Sub subSetInvoiceValues()
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1


If lngIntermediateID > lngItMdt Then
.AddNew

AddNew to what? In order to AddNew, you need a recordset to add it to.
Additionally, you need a With/End With construct to use AddNew the way
you show it. Sometimes you need the entire relevant code rather than a
tiny snippet.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Vance

Thanks for the help Arvin, I have it working now from this distribute button
with this code added in:
Regards Bob
------------------------------------------
If ckbNoNumber.value = False Then
recInvoice.Fields("InvoiceNo") = NextInvoiceNo
Else
recInvoice.Fields("InvoiceNo") = 0
End If
--------------------------------------
But now once I distribute a record that is True my other code is not
requering the original list box to show that it has gone if it is False the
record dissapears from the list, here is my code: frmModify.lstModify is
the list box
---------------------------------------
Private Sub cmdSave_Click()
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 Client Select, [CLOSE] " & vbCrLf &
vbCrLf & " Go to Horses and enter a Client before Distributing.",
vbApplicationModal + vbNo + vbInformation
Exit Sub
End If

If MsgBox("Do You Want To Distribute?", vbQuestion + vbApplicationModal
+ vbYesNo + vbDefaultButton1, "Intellisoft") = vbYes Then
recInvoice.AddNew
subSetInvoiceValues
recInvoice.Update


subDeleteInvoiceItmdt

subBlankForm
lbOwnerlist1.value = ""
lbOwnerlist1.RowSource = ""

lbOwnerlist1.SetFocus
cmdSave.Enabled = False
bModify = False
End If

Forms!frmModify![lstModify] = Null
Form_frmModify.lstModify.Requery

DoCmd.Close acForm, Me.Name
End With
End Sub
--------------------------------------

Bob Vance said:
Sorry Arvin that was my older version of my DB, You will see that I have
added a check Box [ckbNoNumber]
What am trying to do is IF [ckbNoNumber] is ticked (True) it will give the
Invoice a "0" Zero
'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 AND Invoicing =
False 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
.Fields("NoNumber") =
ckbNoNumber.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,21))
& ' ') & " _
&
"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 chkHoldingCharge.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
Arvin Meyer said:
Hi Bob,

I looked carefully (at least I think so) but I couldn't find
ckHoldingInvoice or any reference to it, anywhere in the current code. So
if you are trying to set it to zero (0) in response to a condition,
something else has to happen.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Bob Vance said:
Sorry Arvin this is the whole code...............Thanks Bob

'Code TO Distribute Charges Into Owners
Private Sub subSetInvoiceValues()
Dim recInvoice As ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim recInvoice_ItMdt As ADODB.Recordset
Set recInvoice_ItMdt = New ADODB.Recordset
Set recInvoice = New ADODB.Recordset
Dim lngInvoiceID As Long
Dim lngInvoiceNo As Long
Dim lngIntermediateID As Long
recInvoice_ItMdt.Open "Select * from tblInvoice_ItMdt;",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

recInvoice.Open "Select * from tblInvoice;", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
'************************************************
lngInvoiceID = DMax("InvoiceID", "tblInvoice") + 1
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1
'************************************************
If recInvoice.BOF = False And recInvoice.EOF = False Then
recInvoice.MoveLast
End If

recInvoice.AddNew
Dim lngItMdt As Long
If recInvoice_ItMdt.BOF = False And recInvoice_ItMdt.EOF = False Then
lngItMdt = recInvoice_ItMdt.Fields("IntermediateID")
End If
Do While Not recInvoice_ItMdt.EOF = True
lngIntermediateID = recInvoice_ItMdt.Fields("IntermediateID")

With recInvoice


Dim recHorseOwners As New ADODB.Recordset, curOwnerPercentAmount
As Currency
Dim curTotal As Currency, curGSTContentsValue As Currency

recHorseOwners.Open "SELECT OwnerID,OwnerPercent FROM
tblHorseDetails" _
& " WHERE HorseID=" _
& Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0) _
& " AND OwnerID > 0 AND Invoicing = False 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("InvoiceID") = lngInvoiceID
.Fields("HorseID") =
Nz(val(recInvoice_ItMdt.Fields("HorseID")), 0)
.Fields("HorseName") =
Nz(recInvoice_ItMdt.Fields("HorseName"), "")
.Fields("FatherName") =
Nz(recInvoice_ItMdt.Fields("FatherName"), "")
.Fields("MotherName") =
Nz(recInvoice_ItMdt.Fields("MotherName"), "")

.Fields("DateOfBirth") =
Format(CDate(recInvoice_ItMdt.Fields("DateOfBirth")), "mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
& funCalcAge(Format(recInvoice_ItMdt.Fields("DateOfBirth") _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")
End If
recHorseOwners.MoveFirst
Do Until recHorseOwners.EOF = True

If lngIntermediateID > lngItMdt Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1
lngItMdt = lngIntermediateID
End If
Dim recOwnersInfo As New ADODB.Recordset


recOwnersInfo.Open "SELECT OwnerID," _
&
"IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle & ' ')"
_
& " &
IIf(isnull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName &
' ')" _
& " &
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
'
curTotal = DSum("TotalAmount", "tblInvoice_ItMdt",
"HorseID=" _
& Nz(recInvoice_ItMdt.Fields("HorseID"), 0))
curOwnerPercentAmount = IIf(recHorseOwners.Fields _
("OwnerPercent") = "" Or _
IsNull(recHorseOwners.Fields("OwnerPercent")), 0,
Format(curTotal, "#0.00") _
* recHorseOwners.Fields("OwnerPercent"))

.Fields("OwnerID") = recOwnersInfo.Fields("OwnerID")

.Fields("OwnerName") = 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") =
Format(curOwnerPercentAmount, "#0.00")

curGSTContentsValue = (Format(curOwnerPercentAmount,
"#0.00") / 9)
.Fields("GSTContentsValue") = Format(curGSTContentsValue,
"#0.00")

If Format(curGSTContentsValue, "#0.00") > 0 Then
.Fields("GSTContentsText") = "Tax Contents"
ElseIf Format(curGSTContentsValue, "#0.00") < 0 Then
.Fields("GSTContentsText") = "Credit"
Else
.Fields("GSTContentsText") = ""
End If
End If
recOwnersInfo.Close
Set recOwnersInfo = Nothing

.Fields("InvoiceNo") = lngInvoiceNo


.Fields("InvoiceID") = lngInvoiceID
.Fields("HorseID") = recInvoice_ItMdt.Fields("HorseID")
.Fields("HorseName") = recInvoice_ItMdt.Fields("HorseName")
.Fields("FatherName") = recInvoice_ItMdt.Fields("FatherName")
.Fields("MotherName") = recInvoice_ItMdt.Fields("MotherName")
.Fields("DateOfBirth") =
Format(CDate(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0)),
"mm/dd/yyyy")

.Fields("HorseDetailInfo") =
recInvoice_ItMdt.Fields("FatherName") _
& "--" & recInvoice_ItMdt.Fields("MotherName") & "--" _
&
funCalcAge(Format(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0) _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1) _
& "-" & recInvoice_ItMdt.Fields("Sex")

.Fields("Sex") = recInvoice_ItMdt.Fields("Sex")
.Fields("GSTOptionsText") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsText"), 0)
.Fields("GSTOptionsValue") =
Nz(recInvoice_ItMdt.Fields("GSTOptionsValue"), 0)
.Fields("SubTotal") = Nz(recInvoice_ItMdt.Fields("SubTotal"),
0)
.Fields("TotalAmount") =
Nz(recInvoice_ItMdt.Fields("TotalAmount"), 0)
.Fields("InvoiceDate") = Format(Now(), "dd/mm/yyyy")


Application.SysCmd acSysCmdSetStatus, "Invoice No=" &
.Fields("InvoiceNo") _
& " Horse Name=" & .Fields("HorseName") & " Owner Name=" _
& .Fields("OwnerName")

funSetInvoiceDetailValues lngIntermediateID, lngInvoiceID,
lngInvoiceNo
.Fields("CompanyID") = DLookup("CompanyID", "tblCompanyInfo")
recInvoice.Update
.Requery
recHorseOwners.MoveNext
If recHorseOwners.EOF = False Then
.AddNew
lngInvoiceID = lngInvoiceID + 1
lngInvoiceNo = lngInvoiceNo + 1

End If
Loop
.Update
End With
CurrentProject.Connection.Execute "Delete * from tblAddition_ItMdt
where IntermediateID=" _
& lngIntermediateID

CurrentProject.Connection.Execute "Delete * from tblDaily_ItMdt where
IntermediateID=" _
& lngIntermediateID

recHorseOwners.Close
recInvoice_ItMdt.MoveNext
Loop

Set recHorseOwners = Nothing

CurrentProject.Connection.Execute "Delete * from tblInvoice_ItMdt;"
End Sub

Part of my code for Distributing Invoices is to give Invoice numbers,
What I am trying to do is give a zero to a Invoice that check box
[ckHoldingInvoice] = True
-------------------------------------------------
If (SubForm)subAdditionChargeChild (Checkbox)ckHoldingInvoice is True
Then
InvoiceNo =0 Else
-----------------------------------------------
Part of my Code below!
Private Sub subSetInvoiceValues()
lngInvoiceNo = DMax("InvoiceNo", "tblInvoice") + 1


If lngIntermediateID > lngItMdt Then
.AddNew

AddNew to what? In order to AddNew, you need a recordset to add it to.
Additionally, you need a With/End With construct to use AddNew the way
you show it. Sometimes you need the entire relevant code rather than a
tiny snippet.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
Click to expand...
 

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

Ok This one is Tuff 3
Really Need Help with this Function 5
Using a Check Box as a Condition 4
Adding True/False condition to my code 2
query - sum up and assign records to category 1
Check Box Question 6
Check Box Binding 6
protect check box and cells 2

Top