Double Data Type Problem

B

Bob Vance

I am trying to add in OwnerPercentAmount so it will calculate while my form
is open at the moment it only calculate when I close my form.
SubSetInvoiceValues() does this for me, here is part of the code that does
it can this be added into SubCalculate()............Thanks for any
help............Bob
-------------------------------------------------------------------------
dblOwnerPercentAmount = IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount

--------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double, dblOwnerPercentAmount
As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If


dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4


dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2) '12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
 
C

Clifford Bass

Hi Bob,

What should trigger the computation while the form is open? Why does
it only caculate it when you close the form? Maybe more importantly, why
calculate it at all from the form? Usually these things are calculated on
the fly for queries and reports since any change in the data that goes into
the calucations makes any prior calculations invalid.

Clifford Bass

Bob Vance said:
I am trying to add in OwnerPercentAmount so it will calculate while my form
is open at the moment it only calculate when I close my form.
SubSetInvoiceValues() does this for me, here is part of the code that does
it can this be added into SubCalculate()............Thanks for any
help............Bob
-------------------------------------------------------------------------
dblOwnerPercentAmount = IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount

--------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double, dblOwnerPercentAmount
As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If


dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4


dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2) '12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
Click to expand...
 
B

Bob Vance

Thanks Clifford , I just added a label [This Total will re-calculate when
the form is closed] just would have been nice to see this total change like
[TotalAmount] Regards Bob



Clifford Bass said:
Hi Bob,

What should trigger the computation while the form is open? Why does
it only caculate it when you close the form? Maybe more importantly, why
calculate it at all from the form? Usually these things are calculated on
the fly for queries and reports since any change in the data that goes
into
the calucations makes any prior calculations invalid.

Clifford Bass

Bob Vance said:
I am trying to add in OwnerPercentAmount so it will calculate while my
form
is open at the moment it only calculate when I close my form.
SubSetInvoiceValues() does this for me, here is part of the code that
does
it can this be added into SubCalculate()............Thanks for any
help............Bob
-------------------------------------------------------------------------
dblOwnerPercentAmount = IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount

--------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double,
dblOwnerPercentAmount
As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or
IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or
IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or
IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or
IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If


dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4


dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2)
'12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
Click to expand...
Click to expand...
 
B

Bob Vance

Clifford Bass said:
Hi Bob,

What should trigger the computation while the form is open? Why does
it only caculate it when you close the form? Maybe more importantly, why
calculate it at all from the form? Usually these things are calculated on
the fly for queries and reports since any change in the data that goes
into
the calucations makes any prior calculations invalid.

Clifford Bass

Bob Vance said:
I am trying to add in OwnerPercentAmount so it will calculate while my
form
is open at the moment it only calculate when I close my form.
SubSetInvoiceValues() does this for me, here is part of the code that
does
it can this be added into SubCalculate()............Thanks for any
help............Bob
-------------------------------------------------------------------------
dblOwnerPercentAmount = IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount

--------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double,
dblOwnerPercentAmount
As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or
IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or
IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or
IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or
IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If


dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4


dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2)
'12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
Click to expand...
Click to expand...
 
B

Bob Vance

Thanks Clifford....Regards Bob
Clifford Bass said:
Hi Bob,

What should trigger the computation while the form is open?
Click to expand...
Public Sub SubCalculate()

Why does
it only caculate it when you close the form?
Click to expand...
Private Sub subSetInvoiceValuesModifyMode()
Dim recTmpOwner As New ADODB.Recordset
recTmpOwner.Open "SELECT CompanyID FROM tblCompanyInfo WHERE CompanyName
LIKE '" _
& tbCompanyName.value & "'", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

Dim dblTotal As Double, dblOwnerPercentAmount As Double, dblGSTContentsValue
As Double
Dim recHorseOwners As New ADODB.Recordset

With recInvoice
.Fields("CompanyID") = Nz(recTmpOwner.Fields("CompanyID"), 0)
.Fields("InvoiceID") = IIf(tbInvoiceID.value = "" Or
IsNull(tbInvoiceID.value), 0, tbInvoiceID.value)

.Fields("HorseID") = cbHorseName.value
.Fields("HorseName") = cbHorseName.Column(1)

.Fields("FatherName") = tbFatherName.value
.Fields("MotherName") = tbMotherName.value

If tbDateOfBirth.value = "" Or IsNull(tbDateOfBirth.value) Then
Else
.Fields("DateOfBirth") = Format(CDate(tbDateOfBirth.value),
"mm/dd/yyyy")

.Fields("HorseDetailInfo") = tbFatherName.value & "--" &
tbMotherName.value & "--" & funCalcAge(Format(tbDateOfBirth.value,
"dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & "-" &
tbSex.value
End If

.Fields("Sex") = tbSex.value
.Fields("GSTOptionsText") = cbGSTOptions.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(CDate(tbInvoiceDate.value),
"dd/mm/yyyy")
End If

recHorseOwners.Open "SELECT OwnerPercent FROM tblHorseDetails" _
& " WHERE HorseID=" & cbHorseName.value & " and OwnerID=" _
& Nz(Form_frmModify.lstModify.Column(2), 0) & ";" _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then

recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
Exit Sub
End If
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("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 With
recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
End Sub
Maybe more importantly, why
calculate it at all from the form? Usually these things are calculated on
the fly for queries and reports since any change in the data that goes
into
the calucations makes any prior calculations invalid.

Clifford Bass

Bob Vance said:
I am trying to add in OwnerPercentAmount so it will calculate while my
form
is open at the moment it only calculate when I close my form.
SubSetInvoiceValues() does this for me, here is part of the code that
does
it can this be added into SubCalculate()............Thanks for any
help............Bob
-------------------------------------------------------------------------
dblOwnerPercentAmount = IIf(recHorseOwners.Fields("OwnerPercent") = "" Or
IsNull(recHorseOwners.Fields("OwnerPercent")), 0, dblTotal *
recHorseOwners.Fields("OwnerPercent"))
.Fields("OwnerPercentAmount") = dblOwnerPercentAmount

--------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double,
dblOwnerPercentAmount
As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or
IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or
IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or
IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or
IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If


dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4


dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2)
'12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Bob,

I am thinking that you could have it calculated in the form's Before
Update event if you need it to calculate for each record that is added and/or
updated.

Anyway, you are welcome!

Clifford Bass
 
B

Bob Vance

Clifford, But how come all my other fields can be updated by
SubCalculate()..Thanks bob

Clifford Bass said:
Hi Bob,

I am thinking that you could have it calculated in the form's Before
Update event if you need it to calculate for each record that is added
and/or
updated.

Anyway, you are welcome!

Clifford Bass

Bob Vance said:
Thanks Clifford , I just added a label [This Total will re-calculate when
the form is closed] just would have been nice to see this total change
like
[TotalAmount] Regards Bob
Click to expand...
Click to expand...
 
B

Bob Vance

Clifford, What I am trying to do is incorperate this code below into
SubCalculate()............Thanks Bob
---------------------------------------------
recHorseOwners.Open "SELECT OwnerPercent FROM tblHorseDetails" _
& " WHERE HorseID=" & cbHorseName.value & " and OwnerID=" _
& Nz(Form_frmModify.lstModify.Column(2), 0) & ";" _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then

recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
Exit Sub
End If
------------------------------------------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If

dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4



dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2) '12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
 
C

Clifford Bass

Hi Bob,

In looking at this and at your initial post it dawned on me that maybe
this is easily solved. How about just adding a call to SubSetInvoiceValues()
in SubCalculate() at the appropriate point? Beginning, somewhere in the
middle, or at the end?

If that is not the solution, I need more information in order to help
you. Such as where do SubCalculate() and SubSetInvoiceValues() get called
and what is the entire code for SubSetInvoiceValues(). And when should the
new stuff be done in SubCalculate(). And should it always be done or only if
specific parts of SubCalculate() succeed.

Clifford Bass

Bob Vance said:
Clifford, What I am trying to do is incorperate this code below into
SubCalculate()............Thanks Bob
---------------------------------------------
recHorseOwners.Open "SELECT OwnerPercent FROM tblHorseDetails" _
& " WHERE HorseID=" & cbHorseName.value & " and OwnerID=" _
& Nz(Form_frmModify.lstModify.Column(2), 0) & ";" _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseOwners.EOF = True And recHorseOwners.BOF = True Then

recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
Exit Sub
End If
------------------------------------------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If

dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4



dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2) '12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
Click to expand...
 
B

Bob Vance

Thanks Clifford , I will try adding SubSetInvoiceValues() to
SubCalculate()
What should I write for the code for it to activate SubSetInvoiceValues()
Thanks Bob


Clifford Bass said:
Hi Bob,

In looking at this and at your initial post it dawned on me that maybe
this is easily solved. How about just adding a call to
SubSetInvoiceValues()
in SubCalculate() at the appropriate point? Beginning, somewhere in the
middle, or at the end?

If that is not the solution, I need more information in order to help
you. Such as where do SubCalculate() and SubSetInvoiceValues() get called
and what is the entire code for SubSetInvoiceValues(). And when should
the
new stuff be done in SubCalculate(). And should it always be done or only
if
specific parts of SubCalculate() succeed.

Clifford Bass

Bob Vance said:
Clifford, What I am trying to do is incorperate this code below into
SubCalculate()............Thanks Bob
---------------------------------------------
recHorseOwners.Open "SELECT OwnerPercent FROM tblHorseDetails" _
& " WHERE HorseID=" & cbHorseName.value & " and OwnerID=" _
& Nz(Form_frmModify.lstModify.Column(2), 0) & ";" _
, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseOwners.EOF = True And recHorseOwners.BOF = True
Then

recHorseOwners.Close
Set recHorseOwners = Nothing
Set recTmpOwner = Nothing
Exit Sub
End If
------------------------------------------------------------------------------------------
Public Sub SubCalculate()
Dim dblSubTotal As Double, dblTotalAmount As Double
Dim dblWithoutDailyAmount As Double
Dim dblMonthlyChargeAmount As Double, dblAdditionChargeAmount As Double
Dim dblWithDailyChargeAmount1 As Currency, dblGSTOptionsValue As Double
Dim dblWithDailyChargeAmount2 As Currency, dblWithDailyChargeAmount3 As
Currency
Dim dblWithDailyChargeAmount4 As Currency

If tbDailyChargeAmount1.value = "" Or
IsNull(tbDailyChargeAmount1.value)
Then
dblWithDailyChargeAmount1 = 0
Else
dblWithDailyChargeAmount1 = tbDailyChargeAmount1.value
End If

If tbDailyChargeAmount2.value = "" Or
IsNull(tbDailyChargeAmount2.value)
Then
dblWithDailyChargeAmount2 = 0
Else
dblWithDailyChargeAmount2 = tbDailyChargeAmount2.value
End If

If tbDailyChargeAmount3.value = "" Or
IsNull(tbDailyChargeAmount3.value)
Then
dblWithDailyChargeAmount3 = 0
Else
dblWithDailyChargeAmount3 = tbDailyChargeAmount3.value
End If

If tbDailyChargeAmount4.value = "" Or
IsNull(tbDailyChargeAmount4.value)
Then
dblWithDailyChargeAmount4 = 0
Else
dblWithDailyChargeAmount4 = tbDailyChargeAmount4.value
End If

dblAdditionChargeAmount = Nz(DSum("AdditionChargeAmount",
"TmpAdditionCharge"), 0)

dblSubTotal = dblAdditionChargeAmount + _
dblWithDailyChargeAmount1 + dblWithDailyChargeAmount2 +
dblWithDailyChargeAmount3 + dblWithDailyChargeAmount4



dblWithoutDailyAmount = dblAdditionChargeAmount

tbSubTotal.value = dblSubTotal

If Len([cbGSTOptions]) = 0 Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
Exit Sub
End If

Dim recGSTOptions As New ADODB.Recordset, sngGstPercentage As Single
Set recGSTOptions = New ADODB.Recordset
recGSTOptions.Open "SELECT * FROM tblGSTOptions WHERE GSTOptionsText
LIKE '" _
& cbGSTOptions.value & "'", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

If recGSTOptions.EOF = True And recGSTOptions.BOF = True Then
dblGSTOptionsValue = 0
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)
tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount
MsgBox "Invalid GSTOption.", vbApplicationModal + vbInformation +
vbOKOnly
Exit Sub
End If
sngGstPercentage = CSng(Nz(recGSTOptions.Fields("GSTPercentage"), 0))

If recGSTOptions.Fields("ynIncludeDaily") = True Then
dblGSTOptionsValue = Round((dblSubTotal * sngGstPercentage), 2)
'12
Sept 2005,Pallavi
Else
dblGSTOptionsValue = Round((dblWithoutDailyAmount *
sngGstPercentage), 2) '12 Sept 2005,Pallavi
End If
dblTotalAmount = Round((dblGSTOptionsValue + dblSubTotal), 2)

tbGSTOptionsValue.value = dblGSTOptionsValue
tbTotalAmount.value = dblTotalAmount

Set recGSTOptions = Nothing
End Sub
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Bob,

Just place "SubSetInvoiceValue" (without the quotes) on a separate line
at the point where it should do the calculations.

Clifford Bass
 
B

Bob Vance

Thanks Clifford, This might help. This is the Close button code for my form
that must update my OwnerPercentAmount. This Form has nothing to do with the
2 codes of"Holding Invoices" that is something else.............Regards Bob

Private Sub cmdClose_Click()
Me.Dirty = False

If CurrentProject.AllForms("frmMain").IsLoaded Then
With Forms("frmMain")
!lbTaxChange.Requery
!tbTaxChange.Requery
End With
End If
If Len([tbInvoiceDate]) = 0 Then
MsgBox "Please Insert The Invoice Date.", vbApplicationModal +
vbInformation + vbOKOnly, "Intellisoft"
Exit Sub
End If

If Len([cbHorseName]) = 0 Then
MsgBox "Please Select The Horse Name From The List.",
vbApplicationModal + vbInformation + vbOKOnly, "Intellisoft"
Exit Sub
End If

If Len([cbGSTOptions]) = 0 Then
MsgBox "Please Select The GST Options From The List.",
vbApplicationModal + vbInformation + vbOKOnly, "Intellisoft"
Exit Sub
End If


If bModify = True Then

If Me.OpenArgs = "ModifyOldInvoice" Then
CurrentProject.Connection.Execute "DELETE * FROM
tblDailyCharge WHERE InvoiceID=" & tbInvoiceID.value

CurrentProject.Connection.Execute "DELETE * FROM
tblAdditionCharge WHERE InvoiceID=" & tbInvoiceID.value
subSetInvoiceValuesModifyMode
subSetInvoiceDetailsValues
recInvoice.Update
ElseIf Me.OpenArgs = "ModifyHoldingInvoice" Then 'Not on my
form
subSetInvoiceModifyItMdtValues
subSetInvoiceModifyItMdtDetailsValues
recInvoice_ItMdt.Update
ElseIf Me.OpenArgs = "ModifyHoldingInvoiceFromMain" Then
'Not on my form

subSetInvoiceModifyItMdtValues
subSetInvoiceModifyItMdtDetailsValues
recInvoice_ItMdt.Update
Else
subSetInvoiceItMdtValues
subSetInvoiceItMdtDetailsValues
recInvoice_ItMdt.Update
End If

bModify = False
End If


Set recInvoice = Nothing
Set recInvoice_ItMdt = Nothing
DoCmd.Close acForm, Me.Name
End Sub
 
B

Bob Vance

I tried getting around it this way in my AfterUpdate of my TotalAmount I
tried to get OwnerPercentAmount to show

tbTotalAmount (Divided by) MelbOwnerListSS = OwnerPercentAmount
The only thing is that .lbOwnerListSS is a list box with the percentage
being the first Column..........Regards Bob

Private Sub tbTotalAmount_AfterUpdate()
Me.tbOwnerPercentAmount = Me.tbTotalAmount / (1 + (Me.lbOwnerListSS))
End Sub
 
C

Clifford Bass

Hi Bob,

For some reason I never was notified by your further posts. Did you
get the issues solved?

Clifford Bass
 
B

Bob Vance

Thanks Clifford for your response, No I never did get it Solved unless
putting a label next to the box saying "This Total will be Calculated after
the form is closed!" :) ...Thanks Bob
 
C

Clifford Bass

Hi Bob,

To get at the values in a particular column of the currently selected
row in a list box you have to use the Column property. This uses a
zero-based index. So try this:

Private Sub tbTotalAmount_AfterUpdate()

tbOwnerPercentAmount = tbTotalAmount / (1 + lbOwnerListSS.Column(0))

End Sub

If you need to reference a specific row and you know which one, you can
add that. It also is zero-based. So to get the item in column 1 of row 3
you would use:

lbOwnerListSS.Column(0, 2)

Let me know it that does the trick.

Clifford Bass
 
B

Bob Vance

Thanks Clifford still cant get it to work maybe the query that controls my
list box is the problem, This is the Field to get Owner Percentage:
Expr1: Format(tblHorseDetails.OwnerPercent,"0.0%")
I tried adding Ownerpercentage to the query and changed the Column Row to no
avail....Thanks Bob
 
C

Clifford Bass

Hi Bob,

Yes, trying to do the calculation with a percent text string will cause
trouble. In which case use the Val() function and divide by 100:

tbOwnerPercentAmount = tbTotalAmount / (1 + Val(lbOwnerListSS.Column(0)) /
100)

Clifford Bass
 

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

SubCalculate() Problem 0
Field Size Dilemma 12
Adding True/False condition to my code 2

Top