Public or Global Variable????

[CJ]

Hi Groupies

I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set up a
Public or Global Variable, I am not sure which I need and I do not
understand what my reference book (or the internet) is telling me.

I would like to be able to use the word GST in a number of calculations in
the code and to have the correct GST value selected based on the following
IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed in a
module called Functions, I think this is where my GST needs to go....... I
guess I do not know what or how to declare the correct things.......and I'm
tired or errors.

Many, many thanks for help on this one.
CJ

 

[Allen Browne]

Use the word Public. Global is an out-of-date synonym for a Public variable
in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here. The
purpose of a public variable is that you can use it anywhere in any routine.
But your example shows that the value should be specific to a particular set
of conditions. If you set the public variable to meet the conditions you are
currently working with, and then some other routine tries to use it as well,
it might be set wrongly. Looks like a recipe for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your table with
one that stores the GST rate for that particular record - say a field named
GST, of type Number, size Double. Use an Update query to populate the field
for existing values, e.g. 0 where txtEmempt is No, 0.6 where the ToDate is
prior to July 1, and 0.7 after that date. Since each record now has the
correct GST, there is no chance of public variables interfering with each
other, and no problem when they change the rate on you again in years to
come.

 

[Powderfinger]

Hi Groupies

I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set up a
Public or Global Variable, I am not sure which I need and I do not
understand what my reference book (or the internet) is telling me.

I would like to be able to use the word GST in a number of calculations in
the code and to have the correct GST value selected based on the following
IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed in a
module called Functions, I think this is where my GST needs to go....... I
guess I do not know what or how to declare the correct things.......and I'm
tired or errors.

Many, many thanks for help on this one.
CJ

Just add:

Public GST As Double

In your Functions module.

 

[CJ]

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered the fact
that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing properly,
however, in this database it is going to take me a very, very long time to
correct and test all of the queries, forms and reports.

I was hoping that you could help me out with the Public set up so that I can
correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here. The
purpose of a public variable is that you can use it anywhere in any
routine. But your example shows that the value should be specific to a
particular set of conditions. If you set the public variable to meet the
conditions you are currently working with, and then some other routine
tries to use it as well, it might be set wrongly. Looks like a recipe for
inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your table
with one that stores the GST rate for that particular record - say a field
named GST, of type Number, size Double. Use an Update query to populate
the field for existing values, e.g. 0 where txtEmempt is No, 0.6 where the
ToDate is prior to July 1, and 0.7 after that date. Since each record now
has the correct GST, there is no chance of public variables interfering
with each other, and no problem when they change the rate on you again in
years to come.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set up a
Public or Global Variable, I am not sure which I need and I do not
understand what my reference book (or the internet) is telling me.

I would like to be able to use the word GST in a number of calculations
in the code and to have the correct GST value selected based on the
following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed in a
module called Functions, I think this is where my GST needs to go.......
I guess I do not know what or how to declare the correct things.......and
I'm tired or errors.

Many, many thanks for help on this one.
CJ

 

[Allen Browne]

Perhaps you could create a public function by pasting the code below into a
standard module. Then anywhere in your database you can call the function to
get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date field named
InvoiceDate, then you could type this expression into the Field row in query
design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered the
fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very, very
long time to correct and test all of the queries, forms and reports.

I was hoping that you could help me out with the Public set up so that I
can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here. The
purpose of a public variable is that you can use it anywhere in any
routine. But your example shows that the value should be specific to a
particular set of conditions. If you set the public variable to meet the
conditions you are currently working with, and then some other routine
tries to use it as well, it might be set wrongly. Looks like a recipe for
inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your table
with one that stores the GST rate for that particular record - say a
field named GST, of type Number, size Double. Use an Update query to
populate the field for existing values, e.g. 0 where txtEmempt is No, 0.6
where the ToDate is prior to July 1, and 0.7 after that date. Since each
record now has the correct GST, there is no chance of public variables
interfering with each other, and no problem when they change the rate on
you again in years to come.

I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set up a
Public or Global Variable, I am not sure which I need and I do not
understand what my reference book (or the internet) is telling me.

I would like to be able to use the word GST in a number of calculations
in the code and to have the correct GST value selected based on the
following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed in a
module called Functions, I think this is where my GST needs to go.......
I guess I do not know what or how to declare the correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.
CJ

 

[CJ]

Wow, thanks for the huge input Allen!!

So, I gave your solution a try. I entered in the Public Function, corrected
the field names and then referred to the Function elsewhere in the
code....as shown:
<snip>
Set Db = CurrentDb()
Set qdf = Db.QueryDefs("pqryPayment")
qdf.Parameters("WhichProjectSub") = ProjectSubID
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) * RateWithGst, 2)
Else
ProjectSubPayment = 0
End If
<snip>

I am getting a compile error at the RateWithGST reference......"Argument Not
Optional" is what appears. The code used to be * 1.07, 2 obviously, I
need the correct GST value to be in place.

Thanks again Allen
CJ

Perhaps you could create a public function by pasting the code below into
a standard module. Then anywhere in your database you can call the
function to get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date field
named InvoiceDate, then you could type this expression into the Field row
in query design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered the
fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very, very
long time to correct and test all of the queries, forms and reports.

I was hoping that you could help me out with the Public set up so that I
can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here. The
purpose of a public variable is that you can use it anywhere in any
routine. But your example shows that the value should be specific to a
particular set of conditions. If you set the public variable to meet the
conditions you are currently working with, and then some other routine
tries to use it as well, it might be set wrongly. Looks like a recipe
for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your table
with one that stores the GST rate for that particular record - say a
field named GST, of type Number, size Double. Use an Update query to
populate the field for existing values, e.g. 0 where txtEmempt is No,
0.6 where the ToDate is prior to July 1, and 0.7 after that date. Since
each record now has the correct GST, there is no chance of public
variables interfering with each other, and no problem when they change
the rate on you again in years to come.


I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set up
a Public or Global Variable, I am not sure which I need and I do not
understand what my reference book (or the internet) is telling me.

I would like to be able to use the word GST in a number of calculations
in the code and to have the correct GST value selected based on the
following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed in a
module called Functions, I think this is where my GST needs to
go....... I guess I do not know what or how to declare the correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.
CJ

 

[Allen Browne]

The idea was to leave the function intact, and pass in the actual field
names where needed. Try leaving the function exactly as it was, and then put
your actual field names into the query or text box. The function should
return answers to the query/text box. You should not have to change anything
in the function, except perhaps to add some quotes (depending on your field
types.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wow, thanks for the huge input Allen!!

So, I gave your solution a try. I entered in the Public Function,
corrected the field names and then referred to the Function elsewhere in
the code....as shown:
<snip>
Set Db = CurrentDb()
Set qdf = Db.QueryDefs("pqryPayment")
qdf.Parameters("WhichProjectSub") = ProjectSubID
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) * RateWithGst, 2)
Else
ProjectSubPayment = 0
End If
<snip>

I am getting a compile error at the RateWithGST reference......"Argument
Not Optional" is what appears. The code used to be * 1.07, 2
obviously, I need the correct GST value to be in place.

Thanks again Allen
CJ

Perhaps you could create a public function by pasting the code below into
a standard module. Then anywhere in your database you can call the
function to get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date field
named InvoiceDate, then you could type this expression into the Field row
in query design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered the
fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very, very
long time to correct and test all of the queries, forms and reports.

I was hoping that you could help me out with the Public set up so that I
can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here. The
purpose of a public variable is that you can use it anywhere in any
routine. But your example shows that the value should be specific to a
particular set of conditions. If you set the public variable to meet
the conditions you are currently working with, and then some other
routine tries to use it as well, it might be set wrongly. Looks like a
recipe for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your table
with one that stores the GST rate for that particular record - say a
field named GST, of type Number, size Double. Use an Update query to
populate the field for existing values, e.g. 0 where txtEmempt is No,
0.6 where the ToDate is prior to July 1, and 0.7 after that date. Since
each record now has the correct GST, there is no chance of public
variables interfering with each other, and no problem when they change
the rate on you again in years to come.


I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set up
a Public or Global Variable, I am not sure which I need and I do not
understand what my reference book (or the internet) is telling me.

I would like to be able to use the word GST in a number of
calculations in the code and to have the correct GST value selected
based on the following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed in
a module called Functions, I think this is where my GST needs to
go....... I guess I do not know what or how to declare the correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.
CJ

 

[CJ]

.....And so that is why you are the MVP.....and I am not!!

One other little hickup......When the following event code runs, I get a
"Data Mismatch Error" at the RateWithGst, the ClaimQty I entered was 10:
(If I use RateWithGST() I get "Argument Not Optional")

Private Sub ClaimQty_AfterUpdate()
On Error GoTo PROC_ERR
If Me.DetailID = 0 Then Exit Sub
Me.ClaimAmt = RoundOff(Nz(Me.ClaimQty) * Nz(Me.UnitPrice), 2)
Me.Qty_to_Date = Nz(Me.Prev_Qty) + Nz(Me.ClaimQty)
Me![$ to Date] = Nz(Me.ClaimAmt) + Nz(Me.Prev_Amt)
SaveDetailLine
With Me.Parent.subProgress
.Controls![HoldBack] = RoundOff(.Controls![HBPercent] * CurrClaim()
* "RateWithGST" / 100, 2)
End With
Exit Sub

PROC_ERR:
msgbox "The following error occured: " & Error$
Resume Next
End Sub

Thanks Allen

The idea was to leave the function intact, and pass in the actual field
names where needed. Try leaving the function exactly as it was, and then
put your actual field names into the query or text box. The function
should return answers to the query/text box. You should not have to change
anything in the function, except perhaps to add some quotes (depending on
your field types.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Wow, thanks for the huge input Allen!!

So, I gave your solution a try. I entered in the Public Function,
corrected the field names and then referred to the Function elsewhere in
the code....as shown:
<snip>
Set Db = CurrentDb()
Set qdf = Db.QueryDefs("pqryPayment")
qdf.Parameters("WhichProjectSub") = ProjectSubID
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) * RateWithGst, 2)
Else
ProjectSubPayment = 0
End If
<snip>

I am getting a compile error at the RateWithGST reference......"Argument
Not Optional" is what appears. The code used to be * 1.07, 2 obviously,
I need the correct GST value to be in place.

Thanks again Allen
CJ

Perhaps you could create a public function by pasting the code below
into a standard module. Then anywhere in your database you can call the
function to get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date field
named InvoiceDate, then you could type this expression into the Field
row in query design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered the
fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very, very
long time to correct and test all of the queries, forms and reports.

I was hoping that you could help me out with the Public set up so that
I can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here. The
purpose of a public variable is that you can use it anywhere in any
routine. But your example shows that the value should be specific to a
particular set of conditions. If you set the public variable to meet
the conditions you are currently working with, and then some other
routine tries to use it as well, it might be set wrongly. Looks like a
recipe for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your
table with one that stores the GST rate for that particular record -
say a field named GST, of type Number, size Double. Use an Update
query to populate the field for existing values, e.g. 0 where
txtEmempt is No, 0.6 where the ToDate is prior to July 1, and 0.7
after that date. Since each record now has the correct GST, there is
no chance of public variables interfering with each other, and no
problem when they change the rate on you again in years to come.


I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set
up a Public or Global Variable, I am not sure which I need and I do
not understand what my reference book (or the internet) is telling
me.

I would like to be able to use the word GST in a number of
calculations in the code and to have the correct GST value selected
based on the following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed in
a module called Functions, I think this is where my GST needs to
go....... I guess I do not know what or how to declare the correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.
CJ

 

[Allen Browne]

You must supply 2 numbers inside the brackets when you use the function:
- the first is True or False (it is tax exempt, or it is not);
- the second is the date (so it can determine which rate to use.)

So, if your fields were named IsExempt and ClaimDate, you would use it like
this:

.Controls![HoldBack] = RoundOff(.Controls![HBPercent] * CurrClaim() *
RateWithGST([IsExempt], [ClaimDate]) / 100, 2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

....And so that is why you are the MVP.....and I am not!!

One other little hickup......When the following event code runs, I get a
"Data Mismatch Error" at the RateWithGst, the ClaimQty I entered was 10:
(If I use RateWithGST() I get "Argument Not Optional")

Private Sub ClaimQty_AfterUpdate()
On Error GoTo PROC_ERR
If Me.DetailID = 0 Then Exit Sub
Me.ClaimAmt = RoundOff(Nz(Me.ClaimQty) * Nz(Me.UnitPrice), 2)
Me.Qty_to_Date = Nz(Me.Prev_Qty) + Nz(Me.ClaimQty)
Me![$ to Date] = Nz(Me.ClaimAmt) + Nz(Me.Prev_Amt)
SaveDetailLine
With Me.Parent.subProgress
.Controls![HoldBack] = RoundOff(.Controls![HBPercent] * CurrClaim()
* "RateWithGST" / 100, 2)
End With
Exit Sub

PROC_ERR:
msgbox "The following error occured: " & Error$
Resume Next
End Sub

Thanks Allen

The idea was to leave the function intact, and pass in the actual field
names where needed. Try leaving the function exactly as it was, and then
put your actual field names into the query or text box. The function
should return answers to the query/text box. You should not have to
change anything in the function, except perhaps to add some quotes
(depending on your field types.)

Wow, thanks for the huge input Allen!!

So, I gave your solution a try. I entered in the Public Function,
corrected the field names and then referred to the Function elsewhere in
the code....as shown:
<snip>
Set Db = CurrentDb()
Set qdf = Db.QueryDefs("pqryPayment")
qdf.Parameters("WhichProjectSub") = ProjectSubID
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) * RateWithGst, 2)
Else
ProjectSubPayment = 0
End If
<snip>

I am getting a compile error at the RateWithGST reference......"Argument
Not Optional" is what appears. The code used to be * 1.07, 2
obviously, I need the correct GST value to be in place.

Thanks again Allen
CJ

Perhaps you could create a public function by pasting the code below
into a standard module. Then anywhere in your database you can call the
function to get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date field
named InvoiceDate, then you could type this expression into the Field
row in query design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered the
fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very,
very long time to correct and test all of the queries, forms and
reports.

I was hoping that you could help me out with the Public set up so that
I can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here.
The purpose of a public variable is that you can use it anywhere in
any routine. But your example shows that the value should be specific
to a particular set of conditions. If you set the public variable to
meet the conditions you are currently working with, and then some
other routine tries to use it as well, it might be set wrongly. Looks
like a recipe for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your
table with one that stores the GST rate for that particular record -
say a field named GST, of type Number, size Double. Use an Update
query to populate the field for existing values, e.g. 0 where
txtEmempt is No, 0.6 where the ToDate is prior to July 1, and 0.7
after that date. Since each record now has the correct GST, there is
no chance of public variables interfering with each other, and no
problem when they change the rate on you again in years to come.


I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set
up a Public or Global Variable, I am not sure which I need and I do
not understand what my reference book (or the internet) is telling
me.

I would like to be able to use the word GST in a number of
calculations in the code and to have the correct GST value selected
based on the following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed
in a module called Functions, I think this is where my GST needs to
go....... I guess I do not know what or how to declare the correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.

 

[CJ]

OK, the fog is lifting.....those replace the two variables that were
identified in the RateWithGST function.

Now, I get the compile error: "External Name Not Defined" in my code:

If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) *
RateWithGst([ysnGSTExempt], [ToDate]), 2)

This is what I have trouble with.....where and how do I declare the fields
from my Table/Query/Form: ysnGSTExempt and ToDate?

Thanks......

You must supply 2 numbers inside the brackets when you use the function:
- the first is True or False (it is tax exempt, or it is not);
- the second is the date (so it can determine which rate to use.)

So, if your fields were named IsExempt and ClaimDate, you would use it
like this:

.Controls![HoldBack] = RoundOff(.Controls![HBPercent] * CurrClaim() *
RateWithGST([IsExempt], [ClaimDate]) / 100, 2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

....And so that is why you are the MVP.....and I am not!!

One other little hickup......When the following event code runs, I get a
"Data Mismatch Error" at the RateWithGst, the ClaimQty I entered was 10:
(If I use RateWithGST() I get "Argument Not Optional")

Private Sub ClaimQty_AfterUpdate()
On Error GoTo PROC_ERR
If Me.DetailID = 0 Then Exit Sub
Me.ClaimAmt = RoundOff(Nz(Me.ClaimQty) * Nz(Me.UnitPrice), 2)
Me.Qty_to_Date = Nz(Me.Prev_Qty) + Nz(Me.ClaimQty)
Me![$ to Date] = Nz(Me.ClaimAmt) + Nz(Me.Prev_Amt)
SaveDetailLine
With Me.Parent.subProgress
.Controls![HoldBack] = RoundOff(.Controls![HBPercent] *
CurrClaim() * "RateWithGST" / 100, 2)
End With
Exit Sub

PROC_ERR:
msgbox "The following error occured: " & Error$
Resume Next
End Sub

Thanks Allen

The idea was to leave the function intact, and pass in the actual field
names where needed. Try leaving the function exactly as it was, and then
put your actual field names into the query or text box. The function
should return answers to the query/text box. You should not have to
change anything in the function, except perhaps to add some quotes
(depending on your field types.)

Wow, thanks for the huge input Allen!!

So, I gave your solution a try. I entered in the Public Function,
corrected the field names and then referred to the Function elsewhere
in the code....as shown:
<snip>
Set Db = CurrentDb()
Set qdf = Db.QueryDefs("pqryPayment")
qdf.Parameters("WhichProjectSub") = ProjectSubID
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) * RateWithGst, 2)
Else
ProjectSubPayment = 0
End If
<snip>

I am getting a compile error at the RateWithGST
reference......"Argument Not Optional" is what appears. The code used
to be * 1.07, 2 obviously, I need the correct GST value to be in
place.

Thanks again Allen
CJ

Perhaps you could create a public function by pasting the code below
into a standard module. Then anywhere in your database you can call
the function to get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date field
named InvoiceDate, then you could type this expression into the Field
row in query design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered
the fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very,
very long time to correct and test all of the queries, forms and
reports.

I was hoping that you could help me out with the Public set up so
that I can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here.
The purpose of a public variable is that you can use it anywhere in
any routine. But your example shows that the value should be
specific to a particular set of conditions. If you set the public
variable to meet the conditions you are currently working with, and
then some other routine tries to use it as well, it might be set
wrongly. Looks like a recipe for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your
table with one that stores the GST rate for that particular record -
say a field named GST, of type Number, size Double. Use an Update
query to populate the field for existing values, e.g. 0 where
txtEmempt is No, 0.6 where the ToDate is prior to July 1, and 0.7
after that date. Since each record now has the correct GST, there is
no chance of public variables interfering with each other, and no
problem when they change the rate on you again in years to come.


I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to set
up a Public or Global Variable, I am not sure which I need and I do
not understand what my reference book (or the internet) is telling
me.

I would like to be able to use the word GST in a number of
calculations in the code and to have the correct GST value selected
based on the following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed
in a module called Functions, I think this is where my GST needs to
go....... I guess I do not know what or how to declare the correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.

 

[Allen Browne]

If the form is bound to your table (i.e. the RecordSource property of the
form contains the name of your table, or a query that uses this table), and
the table contains the 2 fields, then the names will be available for the
function.

Hopefully you did put the function in a standard module (one you created by
clicking New in the database window while the Modules tab was selected.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, the fog is lifting.....those replace the two variables that were
identified in the RateWithGST function.

Now, I get the compile error: "External Name Not Defined" in my code:

If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) *
RateWithGst([ysnGSTExempt], [ToDate]), 2)

This is what I have trouble with.....where and how do I declare the fields
from my Table/Query/Form: ysnGSTExempt and ToDate?

Thanks......

You must supply 2 numbers inside the brackets when you use the function:
- the first is True or False (it is tax exempt, or it is not);
- the second is the date (so it can determine which rate to use.)

So, if your fields were named IsExempt and ClaimDate, you would use it
like this:

.Controls![HoldBack] = RoundOff(.Controls![HBPercent] * CurrClaim() *
RateWithGST([IsExempt], [ClaimDate]) / 100, 2)

....And so that is why you are the MVP.....and I am not!!

One other little hickup......When the following event code runs, I get a
"Data Mismatch Error" at the RateWithGst, the ClaimQty I entered was 10:
(If I use RateWithGST() I get "Argument Not Optional")

Private Sub ClaimQty_AfterUpdate()
On Error GoTo PROC_ERR
If Me.DetailID = 0 Then Exit Sub
Me.ClaimAmt = RoundOff(Nz(Me.ClaimQty) * Nz(Me.UnitPrice), 2)
Me.Qty_to_Date = Nz(Me.Prev_Qty) + Nz(Me.ClaimQty)
Me![$ to Date] = Nz(Me.ClaimAmt) + Nz(Me.Prev_Amt)
SaveDetailLine
With Me.Parent.subProgress
.Controls![HoldBack] = RoundOff(.Controls![HBPercent] *
CurrClaim() * "RateWithGST" / 100, 2)
End With
Exit Sub

PROC_ERR:
msgbox "The following error occured: " & Error$
Resume Next
End Sub

Thanks Allen

The idea was to leave the function intact, and pass in the actual field
names where needed. Try leaving the function exactly as it was, and
then
put your actual field names into the query or text box. The function
should return answers to the query/text box. You should not have to
change anything in the function, except perhaps to add some quotes
(depending on your field types.)

Wow, thanks for the huge input Allen!!

So, I gave your solution a try. I entered in the Public Function,
corrected the field names and then referred to the Function elsewhere
in the code....as shown:
<snip>
Set Db = CurrentDb()
Set qdf = Db.QueryDefs("pqryPayment")
qdf.Parameters("WhichProjectSub") = ProjectSubID
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) * RateWithGst, 2)
Else
ProjectSubPayment = 0
End If
<snip>

I am getting a compile error at the RateWithGST
reference......"Argument Not Optional" is what appears. The code used
to be * 1.07, 2 obviously, I need the correct GST value to be in
place.

Thanks again Allen
CJ

Perhaps you could create a public function by pasting the code below
into a standard module. Then anywhere in your database you can call
the function to get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date
field
named InvoiceDate, then you could type this expression into the Field
row in query design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered
the fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very,
very long time to correct and test all of the queries, forms and
reports.

I was hoping that you could help me out with the Public set up so
that I can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here.
The purpose of a public variable is that you can use it anywhere in
any routine. But your example shows that the value should be
specific to a particular set of conditions. If you set the public
variable to meet the conditions you are currently working with, and
then some other routine tries to use it as well, it might be set
wrongly. Looks like a recipe for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your
table with one that stores the GST rate for that particular
record -
say a field named GST, of type Number, size Double. Use an Update
query to populate the field for existing values, e.g. 0 where
txtEmempt is No, 0.6 where the ToDate is prior to July 1, and 0.7
after that date. Since each record now has the correct GST, there
is
no chance of public variables interfering with each other, and no
problem when they change the rate on you again in years to come.


I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to
set
up a Public or Global Variable, I am not sure which I need and I
do
not understand what my reference book (or the internet) is telling
me.

I would like to be able to use the word GST in a number of
calculations in the code and to have the correct GST value
selected
based on the following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions listed
in a module called Functions, I think this is where my GST needs
to
go....... I guess I do not know what or how to declare the correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.

 

[CJ]

OK, well does it make a difference that not all of the fields are on the
same form?

The parent form "frmContracts" contains the field ysnGSTExempt in its
recordsource, "qryProjectWithSubGST"
One subform "subProgress" contains the field "ToDate" in its recordsource,
"qryCurrProgress"
Another subform "subDetail" contains the field "ClaimQty" in its
recordsource, "tblDetailTemp"

As An Aside: The more I look at this database, the more I think (or know) it
wasn't put together properly. Only 5 of the 15 tables are related, there are
81 queries, 19 of which are parameter queries and only 7 reports. As far as
I can tell the majority of the "relationship" work is being done by code,
parameter queries and temporary tables.

I'm not sure if we will be able to solve this without a major rebuild of
this thing............

.....sigh...
Thanks Allen

If the form is bound to your table (i.e. the RecordSource property of the
form contains the name of your table, or a query that uses this table),
and the table contains the 2 fields, then the names will be available for
the function.

Hopefully you did put the function in a standard module (one you created
by clicking New in the database window while the Modules tab was
selected.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, the fog is lifting.....those replace the two variables that were
identified in the RateWithGST function.

Now, I get the compile error: "External Name Not Defined" in my code:

If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) *
RateWithGst([ysnGSTExempt], [ToDate]), 2)

This is what I have trouble with.....where and how do I declare the
fields
from my Table/Query/Form: ysnGSTExempt and ToDate?

Thanks......

You must supply 2 numbers inside the brackets when you use the function:
- the first is True or False (it is tax exempt, or it is not);
- the second is the date (so it can determine which rate to use.)

So, if your fields were named IsExempt and ClaimDate, you would use it
like this:

.Controls![HoldBack] = RoundOff(.Controls![HBPercent] * CurrClaim() *
RateWithGST([IsExempt], [ClaimDate]) / 100, 2)

....And so that is why you are the MVP.....and I am not!!

One other little hickup......When the following event code runs, I get
a
"Data Mismatch Error" at the RateWithGst, the ClaimQty I entered was
10:
(If I use RateWithGST() I get "Argument Not Optional")

Private Sub ClaimQty_AfterUpdate()
On Error GoTo PROC_ERR
If Me.DetailID = 0 Then Exit Sub
Me.ClaimAmt = RoundOff(Nz(Me.ClaimQty) * Nz(Me.UnitPrice), 2)
Me.Qty_to_Date = Nz(Me.Prev_Qty) + Nz(Me.ClaimQty)
Me![$ to Date] = Nz(Me.ClaimAmt) + Nz(Me.Prev_Amt)
SaveDetailLine
With Me.Parent.subProgress
.Controls![HoldBack] = RoundOff(.Controls![HBPercent] *
CurrClaim() * "RateWithGST" / 100, 2)
End With
Exit Sub

PROC_ERR:
msgbox "The following error occured: " & Error$
Resume Next
End Sub

Thanks Allen

The idea was to leave the function intact, and pass in the actual
field
names where needed. Try leaving the function exactly as it was, and
then
put your actual field names into the query or text box. The function
should return answers to the query/text box. You should not have to
change anything in the function, except perhaps to add some quotes
(depending on your field types.)

Wow, thanks for the huge input Allen!!

So, I gave your solution a try. I entered in the Public Function,
corrected the field names and then referred to the Function elsewhere
in the code....as shown:
<snip>
Set Db = CurrentDb()
Set qdf = Db.QueryDefs("pqryPayment")
qdf.Parameters("WhichProjectSub") = ProjectSubID
Set rst = qdf.OpenRecordset()
If rst.RecordCount > 0 Then
ProjectSubPayment = RoundOff(Nz(rst("Total")) * RateWithGst,
2)
Else
ProjectSubPayment = 0
End If
<snip>

I am getting a compile error at the RateWithGST
reference......"Argument Not Optional" is what appears. The code used
to be * 1.07, 2 obviously, I need the correct GST value to be in
place.

Thanks again Allen
CJ

Perhaps you could create a public function by pasting the code below
into a standard module. Then anywhere in your database you can call
the function to get the correct GST rate.

For example, if you have a yes/no field named Exempt, and a date
field
named InvoiceDate, then you could type this expression into the
Field
row in query design:
GST: RateWithGst([Exempt], [InvoiceDate])
or you could type this into the Control Source of a text box on your
form/report:
=RateWithGst([Exempt], [InvoiceDate])


Public Function RateWithGst(varExempt, varInvoiceDate) As Double
'Set 1.06 as the default return value.
RateWithGst = 1.06

'Return 1 if tax exempt
If varExempt = False Then
RateWithGst = 1#
Else
'Return 1.07 if before July 1 2006.
If IsDate(varInvoiceDate) Then
If varInvoiceDate < #7/1/2006# Then
RateWithGst = 1.07
End If
End If
End If
End Function

Hi Allen, thanks for the input.

It would have been nice if the original programmers had considered
the fact that hard coding tax rates is a mistake.........

I think what you have suggested is the way to go to fix this thing
properly, however, in this database it is going to take me a very,
very long time to correct and test all of the queries, forms and
reports.

I was hoping that you could help me out with the Public set up so
that I can correct things affected by this code.

Thanks
CJ

Use the word Public. Global is an out-of-date synonym for a Public
variable in the General Declarations of a standard module.

In practice, a global variable is not really a good solution here.
The purpose of a public variable is that you can use it anywhere
in
any routine. But your example shows that the value should be
specific to a particular set of conditions. If you set the public
variable to meet the conditions you are currently working with,
and
then some other routine tries to use it as well, it might be set
wrongly. Looks like a recipe for inaccuracy and bugs.

The best solution might be to replace the txtExempt field in your
table with one that stores the GST rate for that particular
record -
say a field named GST, of type Number, size Double. Use an Update
query to populate the field for existing values, e.g. 0 where
txtEmempt is No, 0.6 where the ToDate is prior to July 1, and 0.7
after that date. Since each record now has the correct GST, there
is
no chance of public variables interfering with each other, and no
problem when they change the rate on you again in years to come.


I am in wwwaaaaayyyyyy over my head on this one.

I have adopted a database that has extensive VB code. I need to
set
up a Public or Global Variable, I am not sure which I need and I
do
not understand what my reference book (or the internet) is
telling
me.

I would like to be able to use the word GST in a number of
calculations in the code and to have the correct GST value
selected
based on the following IF statement:

If [frmContracts].[Form]![txtExempt] = "No" And
[subprogress].[Form]![ToDate] >= #7/1/2006# Then
GST = 1.06
Else
GST = 1.07
End If

There are a number of Global variables and Public functions
listed
in a module called Functions, I think this is where my GST needs
to
go....... I guess I do not know what or how to declare the
correct
things.......and I'm tired or errors.

Many, many thanks for help on this one.