Suggestions for Sales Tax Setup??

[Guest]

I want to make my sales tax editable as it is the habit for it to be changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the rate
is assigned to a cell. If that would be Ideal, how would I VBA code such a
thing?

Thanks

 

[Al Campagna]

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an Admin (if you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that value in every
transaction, so rate changes in the futuire won't change the past transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form, secured by some
password known only to selected user/s, that would update the SalesTax value in the Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax field with a
starting value, and using the above "password" scheme allow changes to that DefaultValue.

If it's just you using the app... do it whatever way that's easiest for you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Hi Al:

I never, thought of that. So let me make sure I'm understanding correctly.
I could just create a [Sales Tax] table with "only" a [Sales Tax] field with
a Default Value in the Default Value field in the "Field Properties" general
tab area?

If so, how would I assign that value to the [Order Details] table with it
changing as the Sales Tax changed for other orders?

Thanks!

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an Admin (if you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that value in every
transaction, so rate changes in the futuire won't change the past transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form, secured by some
password known only to selected user/s, that would update the SalesTax value in the Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax field with a
starting value, and using the above "password" scheme allow changes to that DefaultValue.

If it's just you using the app... do it whatever way that's easiest for you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I want to make my sales tax editable as it is the habit for it to be changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the rate
is assigned to a cell. If that would be Ideal, how would I VBA code such a
thing?

Thanks

 

[Al Campagna]

EZ,
Well not quite, that's really two really two methods I suggested...
If you create a table to hold the SalesTax, then the form could use a Dlookup of that
value as the Default for SalesTax on the form.
When a change occurs in the tax rate, only authrized people can be allowed to edit the
table to the new rate. That now becomes the default for all transactions from then on.

If security is not a critical issue, just make the default for the Transactions
SalesTax = .04 (for ex.)
Now, when the tax changes, simply edit that value on your first transaction under the
new rate. And using the AfterUpdate event of that control, change the Default to this new
value... (say .04 to .055)

Private Sub SalesTax_AfterUpdate()
SalesTax.DefaultValue = SalesTax
End Sub
From now on, all new transactions will have a tax rate of .55. As long as tax rate
is the same just leave it be. If there's a change, just edit the field, and the new rate
takes over.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hi Al:

I never, thought of that. So let me make sure I'm understanding correctly.
I could just create a [Sales Tax] table with "only" a [Sales Tax] field with
a Default Value in the Default Value field in the "Field Properties" general
tab area?

If so, how would I assign that value to the [Order Details] table with it
changing as the Sales Tax changed for other orders?

Thanks!

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an Admin (if
you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that value in every
transaction, so rate changes in the futuire won't change the past transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form, secured by some
password known only to selected user/s, that would update the SalesTax value in the Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax field with
a
starting value, and using the above "password" scheme allow changes to that
DefaultValue.

If it's just you using the app... do it whatever way that's easiest for you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I want to make my sales tax editable as it is the habit for it to be changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the rate
is assigned to a cell. If that would be Ideal, how would I VBA code such a
thing?

Thanks

 

[Al Campagna]

EZ,
As a general rule, you never save the results of a calculation based on elements
already captured in your table.
ex. Price * SalesTax = LineTotal
100 * .05 = 5
Since you must capture the Price, and the SalesTax at the time of the transaction, you
would not save the LineTotal... just display it on your form. The LineTotal can be
"re-derived" at any time in any subsequent query, form, or report... from the existing
Price and SalesTax.

On your form, you would enter the Price in the Price field, the SalesTax is looked up
(from the table) and entered in the SalesTax field, AND a calculated "unbound" text
controil with a ControlSource of...
= Price * SalesTax
will always display the correct calculation for LineTotal.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al:

I think I'm following this!!??. What I want is to create a "safety-net" so
to speak so it doesn't change throughout. That is why I decided on a table.
Then I wanted to just take this value and multiply it against the
[Subtotal], and have that value assigned to a [Sales Tax] field in the [Order
Details] table. However, I wasn't exactly sure how to do that because this
multiplied value also has to show on the form as well. Security isn't really
critical, but accurate values are.

If I make my default value in my Field Properties, in my case, ".07", then
how do I keep that from being just $.07 in the form?

Thanks!

EZ,
Well not quite, that's really two really two methods I suggested...
If you create a table to hold the SalesTax, then the form could use a Dlookup of
that
value as the Default for SalesTax on the form.
When a change occurs in the tax rate, only authrized people can be allowed to edit
the
table to the new rate. That now becomes the default for all transactions from then on.

If security is not a critical issue, just make the default for the Transactions
SalesTax = .04 (for ex.)
Now, when the tax changes, simply edit that value on your first transaction under
the
new rate. And using the AfterUpdate event of that control, change the Default to this
new
value... (say .04 to .055)

Private Sub SalesTax_AfterUpdate()
SalesTax.DefaultValue = SalesTax
End Sub
From now on, all new transactions will have a tax rate of .55. As long as tax
rate
is the same just leave it be. If there's a change, just edit the field, and the new
rate
takes over.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hi Al:

I never, thought of that. So let me make sure I'm understanding correctly.
I could just create a [Sales Tax] table with "only" a [Sales Tax] field with
a Default Value in the Default Value field in the "Field Properties" general
tab area?

If so, how would I assign that value to the [Order Details] table with it
changing as the Sales Tax changed for other orders?

Thanks!

:

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an Admin (if
you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that value in
every
transaction, so rate changes in the futuire won't change the past transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form, secured by some
password known only to selected user/s, that would update the SalesTax value in the
Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax field
with
a
starting value, and using the above "password" scheme allow changes to that
DefaultValue.

If it's just you using the app... do it whatever way that's easiest for you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


I want to make my sales tax editable as it is the habit for it to be changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the rate
is assigned to a cell. If that would be Ideal, how would I VBA code such a
thing?

Thanks

 

[Guest]

Al:

I do have a [Subtotal] unbound box on my form that recieves its subtotal
value from the subform subtotal in the footer (e.g. as in Northwind) and I
have an undbound [Tax] box that has: =([Subtotal])*.07 in its control source,
and this gives me a tax amount. Are you saying instead, I should have
something similar to:

=[Subtotal]*DLookup("[Sales Tax]", "[Sale Tax]") in the control source.

(i.e. "[FieldName]", [TableName]")

I'm probably completely wrong, because I really don't know too much about
VBA and other codes. I 've read two books, but both are very vague about VBA
terms and their use.

So, are you saying, (remember I'm new and naive), that I can enter an "after
update" from this unbound text box called [Tax] and have that assigned to my
[Sales Tax] field of my [Order Details] table? If so, how? I had to do that
for my [Order Details] subform, but really didn't know what I was doing or
why.

Thanks for your help!

EZ,
As a general rule, you never save the results of a calculation based on elements
already captured in your table.
ex. Price * SalesTax = LineTotal
100 * .05 = 5
Since you must capture the Price, and the SalesTax at the time of the transaction, you
would not save the LineTotal... just display it on your form. The LineTotal can be
"re-derived" at any time in any subsequent query, form, or report... from the existing
Price and SalesTax.

On your form, you would enter the Price in the Price field, the SalesTax is looked up
(from the table) and entered in the SalesTax field, AND a calculated "unbound" text
controil with a ControlSource of...
= Price * SalesTax
will always display the correct calculation for LineTotal.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al:

I think I'm following this!!??. What I want is to create a "safety-net" so
to speak so it doesn't change throughout. That is why I decided on a table.
Then I wanted to just take this value and multiply it against the
[Subtotal], and have that value assigned to a [Sales Tax] field in the [Order
Details] table. However, I wasn't exactly sure how to do that because this
multiplied value also has to show on the form as well. Security isn't really
critical, but accurate values are.

If I make my default value in my Field Properties, in my case, ".07", then
how do I keep that from being just $.07 in the form?

Thanks!

EZ,
Well not quite, that's really two really two methods I suggested...
If you create a table to hold the SalesTax, then the form could use a Dlookup of
that
value as the Default for SalesTax on the form.
When a change occurs in the tax rate, only authrized people can be allowed to edit
the
table to the new rate. That now becomes the default for all transactions from then on.

If security is not a critical issue, just make the default for the Transactions
SalesTax = .04 (for ex.)
Now, when the tax changes, simply edit that value on your first transaction under
the
new rate. And using the AfterUpdate event of that control, change the Default to this
new
value... (say .04 to .055)

Private Sub SalesTax_AfterUpdate()
SalesTax.DefaultValue = SalesTax
End Sub
From now on, all new transactions will have a tax rate of .55. As long as tax
rate
is the same just leave it be. If there's a change, just edit the field, and the new
rate
takes over.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Hi Al:

I never, thought of that. So let me make sure I'm understanding correctly.
I could just create a [Sales Tax] table with "only" a [Sales Tax] field with
a Default Value in the Default Value field in the "Field Properties" general
tab area?

If so, how would I assign that value to the [Order Details] table with it
changing as the Sales Tax changed for other orders?

Thanks!

:

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an Admin (if
you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that value in
every
transaction, so rate changes in the futuire won't change the past transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form, secured by some
password known only to selected user/s, that would update the SalesTax value in the
Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax field
with
a
starting value, and using the above "password" scheme allow changes to that
DefaultValue.

If it's just you using the app... do it whatever way that's easiest for you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


I want to make my sales tax editable as it is the habit for it to be changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the rate
is assigned to a cell. If that would be Ideal, how would I VBA code such a
thing?

Thanks

 

[Al Campagna]

EZ,
No... What would happen to an old record that was created when the SaleTax was .06.
Your "on the fly" calculation would use the "current" value from the table, so all your
"old" order item "after tax" values would be wrong.
And, SalesTax is not a MANY related field, it's a ONE related field to the Order
itself, not the OrderDetails. One SalesTax to many Details

Here's the setup...
On the main form (ex. frmOrders) place a control called SalesTax, bound to your
tblOrders SalesTax field. Make the Default a Dlookup against the tax table, so that every
time you create a new order, the "current" SalesTax is captured to SalesTax on the main
form.

In the subform footer (ex. frmOrderDetails) the calculation would be...
= [Subtotal] * Forms!frmOrders!SalesTax
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al:

I do have a [Subtotal] unbound box on my form that recieves its subtotal
value from the subform subtotal in the footer (e.g. as in Northwind) and I
have an undbound [Tax] box that has: =([Subtotal])*.07 in its control source,
and this gives me a tax amount. Are you saying instead, I should have
something similar to:

=[Subtotal]*DLookup("[Sales Tax]", "[Sale Tax]") in the control source.

(i.e. "[FieldName]", [TableName]")

I'm probably completely wrong, because I really don't know too much about
VBA and other codes. I 've read two books, but both are very vague about VBA
terms and their use.

So, are you saying, (remember I'm new and naive), that I can enter an "after
update" from this unbound text box called [Tax] and have that assigned to my
[Sales Tax] field of my [Order Details] table? If so, how? I had to do that
for my [Order Details] subform, but really didn't know what I was doing or
why.

Thanks for your help!

EZ,
As a general rule, you never save the results of a calculation based on elements
already captured in your table.
ex. Price * SalesTax = LineTotal
100 * .05 = 5
Since you must capture the Price, and the SalesTax at the time of the transaction,
you
would not save the LineTotal... just display it on your form. The LineTotal can be
"re-derived" at any time in any subsequent query, form, or report... from the existing
Price and SalesTax.

On your form, you would enter the Price in the Price field, the SalesTax is looked
up
(from the table) and entered in the SalesTax field, AND a calculated "unbound" text
controil with a ControlSource of...
= Price * SalesTax
will always display the correct calculation for LineTotal.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al:

I think I'm following this!!??. What I want is to create a "safety-net" so
to speak so it doesn't change throughout. That is why I decided on a table.
Then I wanted to just take this value and multiply it against the
[Subtotal], and have that value assigned to a [Sales Tax] field in the [Order
Details] table. However, I wasn't exactly sure how to do that because this
multiplied value also has to show on the form as well. Security isn't really
critical, but accurate values are.

If I make my default value in my Field Properties, in my case, ".07", then
how do I keep that from being just $.07 in the form?

Thanks!

:

EZ,
Well not quite, that's really two really two methods I suggested...
If you create a table to hold the SalesTax, then the form could use a Dlookup of
that
value as the Default for SalesTax on the form.
When a change occurs in the tax rate, only authrized people can be allowed to
edit
the
table to the new rate. That now becomes the default for all transactions from then
on.

If security is not a critical issue, just make the default for the Transactions
SalesTax = .04 (for ex.)
Now, when the tax changes, simply edit that value on your first transaction
under
the
new rate. And using the AfterUpdate event of that control, change the Default to
this
new
value... (say .04 to .055)

Private Sub SalesTax_AfterUpdate()
SalesTax.DefaultValue = SalesTax
End Sub
From now on, all new transactions will have a tax rate of .55. As long as tax
rate
is the same just leave it be. If there's a change, just edit the field, and the new
rate
takes over.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Hi Al:

I never, thought of that. So let me make sure I'm understanding correctly.
I could just create a [Sales Tax] table with "only" a [Sales Tax] field with
a Default Value in the Default Value field in the "Field Properties" general
tab area?

If so, how would I assign that value to the [Order Details] table with it
changing as the Sales Tax changed for other orders?

Thanks!

:

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an Admin
(if
you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that value in
every
transaction, so rate changes in the futuire won't change the past transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form, secured by
some
password known only to selected user/s, that would update the SalesTax value in
the
Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax field
with
a
starting value, and using the above "password" scheme allow changes to that
DefaultValue.

If it's just you using the app... do it whatever way that's easiest for you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


I want to make my sales tax editable as it is the habit for it to be changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the rate
is assigned to a cell. If that would be Ideal, how would I VBA code such a
thing?

Thanks

 

[Al Campagna]

EZ,
Yes, you add the SalesTax field from table Orders, and place it on your main form.
If you open a new Order, and the default for the SalesTax field (bound to tblOrders
SalesTax field) is
=Dlookup("[SalesTax]","tblSalesTax" )
then every Order record you create will place the current SalesTax value in that field.

In the footer of your OrderDetails subform, the calculated "unbound" field [Tax] will
display the correct value with...
=[Subtotal] * Forms![Client Orders]![Sales Tax]

When you create a report, you'll use the relationship between Orders and OrdersDetails
to supply the report with all the values you need to re-derive the Subtotal and the
SalesTax, and the Tax calculation... just like the form subform footer calculation...
=[Subtotal] * Forms![Client Orders]![Sales Tax]

You've captured the SalesTax in your table... you can re-create the SubTotal on the
report,... and can also re-create the same Tax calculation.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hi Al:

Thanks for the patience and all the help here.

Okay, now a bound box is a textbox that is directly associated with a table
field? So, I can drag in my [Sales Tax] field from the [Orders] table and
put it in my [Client Orders] form (i.e. based on my Orders table). Under the
the "Delfault Value" field properties for the [Sales Tax] field I run a
"DLookup" from my [Sales Tax] table for the tax rate. (I was going to put
the [Sales Tax] DLookup in the footer of the Orders.) Then I take an actual
unbound text box called [Tax] and put:

=[Subtotal]*Forms![Client Orders]![Sales Tax]

If that is correct, I do would I VBA the After Update to get an assigned
value to the [Sales Tax] field for later query and report summaries?

I apologize if I'm not clicking on this if I'm totally off base.

Thanks!

EZ,
No... What would happen to an old record that was created when the SaleTax was .06.
Your "on the fly" calculation would use the "current" value from the table, so all your
"old" order item "after tax" values would be wrong.
And, SalesTax is not a MANY related field, it's a ONE related field to the Order
itself, not the OrderDetails. One SalesTax to many Details

Here's the setup...
On the main form (ex. frmOrders) place a control called SalesTax, bound to your
tblOrders SalesTax field. Make the Default a Dlookup against the tax table, so that
every
time you create a new order, the "current" SalesTax is captured to SalesTax on the main
form.

In the subform footer (ex. frmOrderDetails) the calculation would be...
= [Subtotal] * Forms!frmOrders!SalesTax
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al:

I do have a [Subtotal] unbound box on my form that recieves its subtotal
value from the subform subtotal in the footer (e.g. as in Northwind) and I
have an undbound [Tax] box that has: =([Subtotal])*.07 in its control source,
and this gives me a tax amount. Are you saying instead, I should have
something similar to:

=[Subtotal]*DLookup("[Sales Tax]", "[Sale Tax]") in the control source.

(i.e. "[FieldName]", [TableName]")

I'm probably completely wrong, because I really don't know too much about
VBA and other codes. I 've read two books, but both are very vague about VBA
terms and their use.

So, are you saying, (remember I'm new and naive), that I can enter an "after
update" from this unbound text box called [Tax] and have that assigned to my
[Sales Tax] field of my [Order Details] table? If so, how? I had to do that
for my [Order Details] subform, but really didn't know what I was doing or
why.

Thanks for your help!

:

EZ,
As a general rule, you never save the results of a calculation based on elements
already captured in your table.
ex. Price * SalesTax = LineTotal
100 * .05 = 5
Since you must capture the Price, and the SalesTax at the time of the
transaction,
you
would not save the LineTotal... just display it on your form. The LineTotal can be
"re-derived" at any time in any subsequent query, form, or report... from the
existing
Price and SalesTax.

On your form, you would enter the Price in the Price field, the SalesTax is
looked
up
(from the table) and entered in the SalesTax field, AND a calculated "unbound" text
controil with a ControlSource of...
= Price * SalesTax
will always display the correct calculation for LineTotal.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Al:

I think I'm following this!!??. What I want is to create a "safety-net" so
to speak so it doesn't change throughout. That is why I decided on a table.
Then I wanted to just take this value and multiply it against the
[Subtotal], and have that value assigned to a [Sales Tax] field in the [Order
Details] table. However, I wasn't exactly sure how to do that because this
multiplied value also has to show on the form as well. Security isn't really
critical, but accurate values are.

If I make my default value in my Field Properties, in my case, ".07", then
how do I keep that from being just $.07 in the form?

Thanks!

:

EZ,
Well not quite, that's really two really two methods I suggested...
If you create a table to hold the SalesTax, then the form could use a Dlookup
of
that
value as the Default for SalesTax on the form.
When a change occurs in the tax rate, only authrized people can be allowed to
edit
the
table to the new rate. That now becomes the default for all transactions from
then
on.

If security is not a critical issue, just make the default for the
Transactions
SalesTax = .04 (for ex.)
Now, when the tax changes, simply edit that value on your first transaction
under
the
new rate. And using the AfterUpdate event of that control, change the Default to
this
new
value... (say .04 to .055)

Private Sub SalesTax_AfterUpdate()
SalesTax.DefaultValue = SalesTax
End Sub
From now on, all new transactions will have a tax rate of .55. As long as
tax
rate
is the same just leave it be. If there's a change, just edit the field, and the
new
rate
takes over.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Hi Al:

I never, thought of that. So let me make sure I'm understanding correctly.
I could just create a [Sales Tax] table with "only" a [Sales Tax] field with
a Default Value in the Default Value field in the "Field Properties" general
tab area?

If so, how would I assign that value to the [Order Details] table with it
changing as the Sales Tax changed for other orders?

Thanks!

:

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an
Admin
(if
you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that value
in
every
transaction, so rate changes in the futuire won't change the past
transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form, secured
by
some
password known only to selected user/s, that would update the SalesTax value
in
the
Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax
field
with
a
starting value, and using the above "password" scheme allow changes to that
DefaultValue.

If it's just you using the app... do it whatever way that's easiest for
you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


I want to make my sales tax editable as it is the habit for it to be changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the rate
is assigned to a cell. If that would be Ideal, how would I VBA code such a
thing?

Thanks

 

[Al Campagna]

EZ,
No, that was not the setup I suggested...
I'll try again... For the sake of problem determination and clarity... please follow
the instructions carefully.
From what you had when this thread started, there are only 2 thing that needs to be
chneged.
--------- Main Form ----------------------------------------------------
The Main form should be based on tblClientOrders. That table should have a
numeric/single field called SalesTax. The default value on the form for SalesTax is the
Dlookup of the current SalesTax value from your Tax table.
That field goes in the Detail section of your main form.
Whenever you create a new ClientOrder, the Dlookup places the current SalesTax in that
bound control.

That's ALLl you need to add to the main form.
Delete any other fields you added that deal with SalesTax on the main form.
you wrote...

In the [Client Orders] form Footer section, a bound text box from the [Sales
Tax] table named: [Sales Tax_Sales Tax]

That was not in my instructions... remove that completely!
----------- SubForm ------------------------------------
The subform [Order Details] needs no SalesTax field or control at all.
*Put your subform back just the way it was when we started this problem.
The only place in the subform that a reference to SalesTax is needed, is in the
calculated [Tax] field calculation in the footer...
= SubTotal * Forms![Client Orders]!SalesTax
That's ALL you need in the subform.
------------------------------------
IF... you would like to just "DISPLAY" the SalesTax value (from the main form) in the
subform, place an unbound calculated control named txtSalesTax with...
= Forms![Client Orders]!SalesTax
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al:

Well, I'm glad to say your setup worked, and I did get the footer Sales Tax
to look up the rate and I think the rest will work; however, it has created a
problem maybe you can explain.

The problem is: Now my [Client Orders] form freezes and I cannot add a
record and it does not show the number of records. It just shows "1 of 1".
This is how it is set up just so you have an idea of how it is being done.
Even if I clear the fields it doesn't allow use of the form anymore.
Thankfully I have a backup and keep resaving it, but can't seem to figure it
out. Here is the setup:

In the
footer is a [Sales Tax] field from the [Sales Tax] table that uses a DLookup
to find the rate. In the Details there is a [Sales Tax] field that is from
the [Orders] table that takes the rate in the footer multiplied by the
Subtotal in the [Order Details] subform to get the Sales Tax displayed in the
Details of the form. The fields used are:

In the [Order Details] subform an unbound text box in the footer named:
[Subtot]

In the [Client Orders] form Details section, a bound text box from the
[Orders] table named: [Sales Tax]

In the [Client Orders] form Footer section, a bound text box from the [Sales
Tax] table named: [Sales Tax_Sales Tax]

The [Sales Tax_Sales Tax] bound text box in the footer has a Default Value:
=DLookUp("[Sales Tax]","[Sales Tax]")

The [Sales Tax] bound text box in the Details has a Control Source: =[Order
Details].[Form]![SubTot]*[Forms]![Client Orders]![Sales Tax_Sales Tax]

Thanks

EZ,
Yes, you add the SalesTax field from table Orders, and place it on your main form.
If you open a new Order, and the default for the SalesTax field (bound to tblOrders
SalesTax field) is
=Dlookup("[SalesTax]","tblSalesTax" )
then every Order record you create will place the current SalesTax value in that field.

In the footer of your OrderDetails subform, the calculated "unbound" field [Tax]
will
display the correct value with...
=[Subtotal] * Forms![Client Orders]![Sales Tax]

When you create a report, you'll use the relationship between Orders and
OrdersDetails
to supply the report with all the values you need to re-derive the Subtotal and the
SalesTax, and the Tax calculation... just like the form subform footer calculation...
=[Subtotal] * Forms![Client Orders]![Sales Tax]

You've captured the SalesTax in your table... you can re-create the SubTotal on the
report,... and can also re-create the same Tax calculation.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Hi Al:

Thanks for the patience and all the help here.

Okay, now a bound box is a textbox that is directly associated with a table
field? So, I can drag in my [Sales Tax] field from the [Orders] table and
put it in my [Client Orders] form (i.e. based on my Orders table). Under the
the "Delfault Value" field properties for the [Sales Tax] field I run a
"DLookup" from my [Sales Tax] table for the tax rate. (I was going to put
the [Sales Tax] DLookup in the footer of the Orders.) Then I take an actual
unbound text box called [Tax] and put:

=[Subtotal]*Forms![Client Orders]![Sales Tax]

If that is correct, I do would I VBA the After Update to get an assigned
value to the [Sales Tax] field for later query and report summaries?

I apologize if I'm not clicking on this if I'm totally off base.

Thanks!

:

EZ,
No... What would happen to an old record that was created when the SaleTax was
.06.
Your "on the fly" calculation would use the "current" value from the table, so all
your
"old" order item "after tax" values would be wrong.
And, SalesTax is not a MANY related field, it's a ONE related field to the Order
itself, not the OrderDetails. One SalesTax to many Details

Here's the setup...
On the main form (ex. frmOrders) place a control called SalesTax, bound to your
tblOrders SalesTax field. Make the Default a Dlookup against the tax table, so that
every
time you create a new order, the "current" SalesTax is captured to SalesTax on the
main
form.

In the subform footer (ex. frmOrderDetails) the calculation would be...
= [Subtotal] * Forms!frmOrders!SalesTax
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Al:

I do have a [Subtotal] unbound box on my form that recieves its subtotal
value from the subform subtotal in the footer (e.g. as in Northwind) and I
have an undbound [Tax] box that has: =([Subtotal])*.07 in its control source,
and this gives me a tax amount. Are you saying instead, I should have
something similar to:

=[Subtotal]*DLookup("[Sales Tax]", "[Sale Tax]") in the control source.

(i.e. "[FieldName]", [TableName]")

I'm probably completely wrong, because I really don't know too much about
VBA and other codes. I 've read two books, but both are very vague about VBA
terms and their use.

So, are you saying, (remember I'm new and naive), that I can enter an "after
update" from this unbound text box called [Tax] and have that assigned to my
[Sales Tax] field of my [Order Details] table? If so, how? I had to do that
for my [Order Details] subform, but really didn't know what I was doing or
why.

Thanks for your help!

:

EZ,
As a general rule, you never save the results of a calculation based on
elements
already captured in your table.
ex. Price * SalesTax = LineTotal
100 * .05 = 5
Since you must capture the Price, and the SalesTax at the time of the
transaction,
you
would not save the LineTotal... just display it on your form. The LineTotal can
be
"re-derived" at any time in any subsequent query, form, or report... from the
existing
Price and SalesTax.

On your form, you would enter the Price in the Price field, the SalesTax is
looked
up
(from the table) and entered in the SalesTax field, AND a calculated "unbound"
text
controil with a ControlSource of...
= Price * SalesTax
will always display the correct calculation for LineTotal.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Al:

I think I'm following this!!??. What I want is to create a "safety-net" so
to speak so it doesn't change throughout. That is why I decided on a table.
Then I wanted to just take this value and multiply it against the
[Subtotal], and have that value assigned to a [Sales Tax] field in the [Order
Details] table. However, I wasn't exactly sure how to do that because this
multiplied value also has to show on the form as well. Security isn't really
critical, but accurate values are.

If I make my default value in my Field Properties, in my case, ".07", then
how do I keep that from being just $.07 in the form?

Thanks!

:

EZ,
Well not quite, that's really two really two methods I suggested...
If you create a table to hold the SalesTax, then the form could use a
Dlookup
of
that
value as the Default for SalesTax on the form.
When a change occurs in the tax rate, only authrized people can be allowed
to
edit
the
table to the new rate. That now becomes the default for all transactions from
then
on.

If security is not a critical issue, just make the default for the
Transactions
SalesTax = .04 (for ex.)
Now, when the tax changes, simply edit that value on your first
transaction
under
the
new rate. And using the AfterUpdate event of that control, change the Default
to
this
new
value... (say .04 to .055)

Private Sub SalesTax_AfterUpdate()
SalesTax.DefaultValue = SalesTax
End Sub
From now on, all new transactions will have a tax rate of .55. As long
as
tax
rate
is the same just leave it be. If there's a change, just edit the field, and
the
new
rate
takes over.

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


Hi Al:

I never, thought of that. So let me make sure I'm understanding correctly.
I could just create a [Sales Tax] table with "only" a [Sales Tax] field
with
a Default Value in the Default Value field in the "Field Properties" general
tab area?

If so, how would I assign that value to the [Order Details] table with it
changing as the Sales Tax changed for other orders?

Thanks!

:

EZ,
A table should do it. Just one field [SalesTax].
If your in a multi-user environment, that would be the safest. Only an
Admin
(if
you
have security) could go into the table and change it.
Of course, because the SalesTax changes, you'll have to capture that
value
in
every
transaction, so rate changes in the futuire won't change the past
transactions.
And, that field should be Locked from any user changes.

With a table, you could create a method, on the transaction form,
secured
by
some
password known only to selected user/s, that would update the SalesTax
value
in
the
Tax
table.
Or, with no table, you could hardwire the DefaultValue for the SalkesTax
field
with
a
starting value, and using the above "password" scheme allow changes to that
DefaultValue.

If it's just you using the app... do it whatever way that's easiest for
you.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


I want to make my sales tax editable as it is the habit for it to be
changed
every six months. Any suggestions on how to do this?

Should I set it up in the form?
Set it up as a table Entry and change it that way, linked to the form?

Open to suggestions and thoughts.

I was thinking of it being setup like Northwinds Order sheet where the
rate
is assigned to a cell. If that would be Ideal, how would I VBA code such
a
thing?

Thanks