How can I get Sales Tax Rate in underlying Subform table. - Revisited

[mthornblad]

I posted the following on July 15:

Hi

I have a Master/Detail (form/subform) for Customer Invoices. The
Master portion of the form contains the Customer info. Customer
number, customer name, resale number, and invoice number. Some
customers have a resale number and some don't.

The Detail portion of the form contains the line items for the
invoice. One of the fields in each line items is sales tax.

If the customer has a resale number, then they are not charged sales
tax. If the customer does not have a resale number, they are charged
the current sales tax rate.

How can I handle this situation ? I would appreciate any help.

Thanks in advance

Mark

Allen Brown replied with the following:

Use the BeforeInsert event procedure of the subform to look up the
customer's [Resale Number] and assign the TaxRate accordingly.

Ideally the line items table has a TaxRate field, not a TaxAmount
field.
This avoids the dependency between the fields (always good design.)

Form_BeforeInsert fires only when creating a new record. Using this
event
prevents the problems that could occur with editing old records after
a
customer acquired (or lost) their tax free status.

The lookup could look in a hidden column of the CustomerID combo on
the main
form. Alternatively, it could use DLookup() to examine the record in
the
Customer table. For help with DLookup() see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

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

-------------------------------------------------
My followup is:

My line items table does indeed have a TaxRate field. (not the tax
amount).
I have the Customer Resale Number on the main form.
I could put the Sales Tax Rate on the main form as well.

My problem is I don't know how to get the Tax Rate into the underlying
table
of the subform.

The fields on my subform are as follows:

Qty Part No. Description Unit Price Extention
Tax Rate Tax Amount

The user enters the Qty and Part No. The description comes from the
underlying query for the subform. I use the AfterUpdate event
property of the
Part No. field and use a Dlookup to get the Unit Price. The Extention
is
a calculated field (Qty * UnitPrice). My Tax Amount field would be
calculated
(TaxRate*Extention) if I could just get the Tax Rate.

How can I populate the Tax Rate field? If I use a Dlookup, on which
event property
could I use?

Thanks
Mark

 

[pietlinden]

I posted the following on July 15:

Hi

I have a Master/Detail (form/subform) for Customer Invoices. The
Master portion of the form contains the Customer info. Customer
number, customer name, resale number, and invoice number. Some
customers have a resale number and some don't.

The Detail portion of the form contains the line items for the
invoice. One of the fields in each line items is sales tax.

If the customer has a resale number, then they are not charged sales
tax. If the customer does not have a resale number, they are charged
the current sales tax rate.

How can I handle this situation ? I would appreciate any help.

Thanks in advance

Mark

Allen Brown replied with the following:

Use the BeforeInsert event procedure of the subform to look up the
customer's [Resale Number] and assign the TaxRate accordingly.

Ideally the line items table has a TaxRate field, not a TaxAmount
field.
This avoids the dependency between the fields (always good design.)

Form_BeforeInsert fires only when creating a new record. Using this
event
prevents the problems that could occur with editing old records after
a
customer acquired (or lost) their tax free status.

The lookup could look in a hidden column of the CustomerID combo on
the main
form. Alternatively, it could use DLookup() to examine the record in
the
Customer table. For help with DLookup() see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

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

-------------------------------------------------
My followup is:

My line items table does indeed have a TaxRate field. (not the tax
amount).
I have the Customer Resale Number on the main form.
I could put the Sales Tax Rate on the main form as well.

My problem is I don't know how to get the Tax Rate into the underlying
table
of the subform.

The fields on my subform are as follows:

Qty Part No. Description Unit Price Extention
Tax Rate Tax Amount

The user enters the Qty and Part No. The description comes from the
underlying query for the subform. I use the AfterUpdate event
property of the
Part No. field and use a Dlookup to get the Unit Price. The Extention
is
a calculated field (Qty * UnitPrice). My Tax Amount field would be
calculated
(TaxRate*Extention) if I could just get the Tax Rate.

How can I populate the Tax Rate field? If I use a Dlookup, on which
event property
could I use?

Thanks
Mark

put the control on your subform as invisible - you can still modify
the value in code...

 

[Klatuu]

I would use a Dlookup to the tax rate table as the Control source of a text
box. And, take into account whether the customer has a resale certificate.
It may be something like:

=IIf(Me.txtHasCertificate, 0, Nz(DLookup("[TaxRate]", "tblTaxRates"))

This is assuming you have a text box named txtHasCertificate bound to a
boolean field in your recordset that tells you whether the customer has a
certificate. Also, I assume a tax rate table. It may not be applicable to
you, but in Texas, sales tax rates vary from county to county and city to
city. The twist to it is the tax rate is determined based on the delivery
point. For example if your business is in Mule Shoe and your customer is in
Cut And Shoot (both real texas town names) and you deliver the product, you
charge the Cut And Shoot Rate. If the customer picks up at your location,
you charge the Mule Shoe rate.

If you are not sure where Cut And Shoot is, it is about a 3 mile horseback
ride east of Dime Box.
--
Dave Hargis, Microsoft Access MVP

I posted the following on July 15:

Hi

I have a Master/Detail (form/subform) for Customer Invoices. The
Master portion of the form contains the Customer info. Customer
number, customer name, resale number, and invoice number. Some
customers have a resale number and some don't.

The Detail portion of the form contains the line items for the
invoice. One of the fields in each line items is sales tax.

If the customer has a resale number, then they are not charged sales
tax. If the customer does not have a resale number, they are charged
the current sales tax rate.

How can I handle this situation ? I would appreciate any help.

Thanks in advance

Mark

Allen Brown replied with the following:

Use the BeforeInsert event procedure of the subform to look up the
customer's [Resale Number] and assign the TaxRate accordingly.

Ideally the line items table has a TaxRate field, not a TaxAmount
field.
This avoids the dependency between the fields (always good design.)

Form_BeforeInsert fires only when creating a new record. Using this
event
prevents the problems that could occur with editing old records after
a
customer acquired (or lost) their tax free status.

The lookup could look in a hidden column of the CustomerID combo on
the main
form. Alternatively, it could use DLookup() to examine the record in
the
Customer table. For help with DLookup() see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

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

-------------------------------------------------
My followup is:

My line items table does indeed have a TaxRate field. (not the tax
amount).
I have the Customer Resale Number on the main form.
I could put the Sales Tax Rate on the main form as well.

My problem is I don't know how to get the Tax Rate into the underlying
table
of the subform.

The fields on my subform are as follows:

Qty Part No. Description Unit Price Extention
Tax Rate Tax Amount

The user enters the Qty and Part No. The description comes from the
underlying query for the subform. I use the AfterUpdate event
property of the
Part No. field and use a Dlookup to get the Unit Price. The Extention
is
a calculated field (Qty * UnitPrice). My Tax Amount field would be
calculated
(TaxRate*Extention) if I could just get the Tax Rate.

How can I populate the Tax Rate field? If I use a Dlookup, on which
event property
could I use?

Thanks
Mark