Combining values from different forms

[ssanfu]

Any chance you would post your dB? Do a "Compact & Repair?, then zip it before posting..

I still have a confuser with A2000 loaded; no reason to convert to a different version. Everything you are trying to do can be done in A2K.

[pbaldy]

I still have a confuser with A2000 loaded; no reason to convert to a different version.

Freudian slip?

[ssanfu]

Did Freud wear a slip?? That would explain a lot!

[DavidWrightSr]

I have a subform,(named 'Fldetail'), in which I would like to calculate a sales tax based on the sales price * a tax rate stored in a separate Form. This form is called 'Values' and contains the 'Tax Rate' in a row called that.

The amount to use in the calculation is in the same row as the result to be calculated and is called 'Amount'.
To further complicate things, the final calculation is dependent on a type field labeled 'S' (for Sales Item) in the same row and a field from the main form called 'Tax Code', (the main form is named 'Florist'), so the final calculation is:
If 'S' and 'Tax Code' are true then the result is ('Tax Rate' * 'Amount') + 'Amount'.

[Omitted]

Any help would be greatly appreciated.

David Wright Sr.

I have partially solved this problem. I can now calculate Tax based on using a literal 7% in the SQL query.
However, I have still to be able to add Tax_Code from the main form and TaxRate into the calculation.

I tried to create a VBA function TRate in module basTRate.

Function TRate()

[DavidWrightSr]

I have a subform,(named 'Fldetail'), in which I would like to calculate a sales tax based on the sales price * a tax rate stored in a separate Form. This form is called 'Values' and contains the 'Tax Rate' in a row called that.

The amount to use in the calculation is in the same row as the result to be calculated and is called 'Amount'.
To further complicate things, the final calculation is dependent on a type field labeled 'S' (for Sales Item) in the same row and a field from the main form called 'Tax Code', (the main form is named 'Florist'), so the final calculation is:
If 'S' and 'Tax Code' are true then the result is ('Tax Rate' * 'Amount') + 'Amount'.

[Omitted]

Any help would be greatly appreciated.

David Wright Sr.

I have partially solved this problem. I can now calculate Tax based on using a literal 7% in the SQL query.
However, I have still to be able to add Tax_Code from the main form and TaxRate into the calculation.

I tried to create a VBA function TRate in module basTRate.

Function TRate()
DIM TRate as Integer
TRate=.07
End Function

The current SQL statement works fine if I use '.07' * Amount' directly, but when I try to plug in 'TRate', I get an error box requiring that I define TRate.

I would like to be able to create a Form that the user could use to redefine TRate whenever it changed.

Also, I still need to make Tax_Code from the main form part of the solution. "(Tax_Code='false' & TRate) * Amount" (Tax_Code to the user is defined as 'No Tax charged' when Tax_Code=false

David Wright
P.S. Thanks to all for help in getting me this far.

[pbaldy]

A couple of things wrong with the function. An Integer data type won't hold a decimal; I'd use Currency for what you're doing. Next, you have both a function and a variable with the same name. To do it this way, I'd just have:

Function TRate() As Currency
  TRate=.07
End Function

You could also refer to a form control, or get the value from a table, which might provide the best maintainability.

[DavidWrightSr]

A couple of things wrong with the function. An Integer data type won't hold a decimal; I'd use Currency for what you're doing. Next, you have both a function and a variable with the same name. To do it this way, I'd just have:

Function TRate() As Currency
  TRate=.07
End Function

You could also refer to a form control, or get the value from a table, which might provide the best maintainability.

I changed the function as you suggested.

The segment of code in the SQL statement is currently iff((TYPE="S"),TRate()*[AMOUNT] AS TAX and it works perfectly.
However, as you suggested, I would like to get the TRATE value from a table which could be easily maintained and I need to
make the conditional part be (TYPE="S") anded with (TAX_CODE=false)) from the main form.

Could you show me the code for both TRATE from a table* and code to get TAX_CODE from the main form.
I have tried all sorts of combinations and have not been able to figure it out.

With these changes done, I can move on to the next part of the problems with the form.
Many Thanks.

David

* The table is named tblValues and contains a single column labeled Tax Rate and currently has the value $.07.

[pbaldy]

You can simply refer to the form control to get its value:

http://access.mvps.org/access/forms/frm0031.htm

You can use a DLookup() to get the value from a table. If there's a single record, you don't need a criteria.

[pbaldy]

Meant to include:

http://access.mvps.org/access/general/gen0018.htm

[DavidWrightSr]

Meant to include:

http://access.mvps.org/access/general/gen0018.htm

Thanks. That should help with the table lookup on the TRate.

finding the value of the control is giving me problems still.

TAX_CODE is on the main form and I can't seem to find the right combination to
retrieve it.

David

[pbaldy]

Did you see the form link in the previous post? What are you trying exactly?

[DavidWrightSr]

My thinking on this has evolved during the process of creating a value for Tax in a subform table, so please bear with me if I repeat myself from earlier.

I have a query which creates the subform table.
SELECT tblFldetail.ACCTNO, tblFldetail.TDATE, tblFldetail.TTYPE, tblFldetail.TAMOUNT, IIf(([TTYPE]="S"),DLookup("[Tax Rate]","tblValues")*[TAMOUNT]) AS TAX, tblFldetail.DESC
FROM tblFldetail
ORDER BY tblFldetail.TTYPE;

I am trying to create a value for the field TAX whenever I add a new row to the subform table.

Calculation Clause: The following clause represents the effort to do this.
IIf(([TTYPE]="S"),DLookup("[Tax Rate]","tblValues")*[TAMOUNT]) AS TAX

However, I need to produce a value for TAX only if the field TAX_CODE on the Form tblFlorist is ‘false’
In which case DLookup("[Tax Rate]" * [TAMOUNT] produces the desired value for the TAX field.

So far so good. The problem is that the TAX calculated above applies to all rows of the table, not
Just the new one and there are other conditions which have to be considered. Also the value in a main form field TAX_CODE should produce no tax when equal to -1.

It appears to me that I might be able to add another parameter such that TAX is only created when the date in the row is equal to the current date, but I don’t see how I can add that directly in the query.

It seems to me that the best solution would be to replace the Calculation Clause: with a zero parameter function with simple statements specifying the various outcomes:

1. No Tax if TTYPE not equal “S”.
2. No Tax if tblFlorist.TAX_CODE =-1
3. No Tax if TDATE <> Date()
4. Otherwise TAX would be produced by the Calculation Clause:

I have tried the following after replacing the Calculation Clause: in the query with mktax() and created it in a module.

Public Function mkTax()

If tblFldetailForm.TTYPE <> "S" Then
mkTax = Null
Exit Function

If tblFlorist.TAX_CODE = -1 Then
mkTax = Null
Exit Function

If tblFlddetailForm.TDATE <> Date Then
mkTax = Null
Exit Function

mkTax = DLookup("[Tax Rate]", "tblValues") * [TAMOUNT]

End Function

There are two problems, 1) I get ‘External Name not defined with “Public Function mkTax()” and trying to use the date() function to compare with TDATE changes to simply ‘date’

Thanks for any suggestions as to what to do. This particular problem has held me up for weeks.

David Wright

[pbaldy]

Boy, there are a few things wrong there. Your first error could be due to the module having the same name as the function; does it? The second isn't a big deal, will happen in some situations. You can make sure with:

VBA.Date

That said, I doubt the code will even compile. Your If statements don't have End If. I doubt the form references would work in a standard module; you probably need the full form reference. I personally would have the function accept parameters rather than get them from a form, so it could be called from anywhere.

[ssanfu]

I asked a question in Post #6 and you replied in Post #8:

Is it possible for the tax rate to change?
If yes, I would save the tax rate in the record. Otherwise, next year, if you look back at a record, the calculated value will be different if the tax rate was changed.

Yes, I will be doing that.

Where are you on this? If you add a field to store the tax rate in the table for the subform, you don't need the function to get the tax rate (Function mkTax() ).

When a new record is added to the subform record source "tblFldetail", I would use the form before update event to get the tax rate and push it into the tax rate control/field. The default value for the tax rate field should be zero.

Then the subform record source would look like

SELECT tblFldetail.ACCTNO, tblFldetail.TDATE, tblFldetail.TTYPE, tblFldetail.TAMOUNT, tblFldetail.TaxRate, ([TaxRate] * [TAMOUNT]) AS TAX, ([TAMOUNT] + ([TaxRate] * [TAMOUNT])) AS ExtendedAmount, tblFldetail.DESC 
FROM tblFldetail
ORDER BY tblFldetail.TTYPE;

[DavidWrightSr]

I asked a question in Post #6 and you replied in Post #8:

Where are you on this? If you add a field to store the tax rate in the table for the subform, you don't need the function to get the tax rate (Function mkTax() ).

When a new record is added to the subform record source "tblFldetail", I would use the form before update event to get the tax rate and push it into the tax rate control/field. The default value for the tax rate field should be zero.

Then the subform record source would look like

SELECT tblFldetail.ACCTNO, tblFldetail.TDATE, tblFldetail.TTYPE, tblFldetail.TAMOUNT, tblFldetail.TaxRate, ([TaxRate] * [TAMOUNT]) AS TAX, ([TAMOUNT] + ([TaxRate] * [TAMOUNT])) AS ExtendedAmount, tblFldetail.DESC 
FROM tblFldetail
ORDER BY tblFldetail.TTYPE;

Ok. I think I understand part of your post. I add a field TAX with default value of zero to tblFldetail.

However, I don't know how to 'get the tax rate and push it into the tax rate control/field'
Is this done with an event procedure? or otherwise. Does this utilize the tax rate which is in the tblValues table, which needs to be the source for tax rate as it must be changeable by the user in a form.

Sorry to be so dumb about all of this, but I have been only working with HTML for the last 25 years since the last time I did any programming which was in Foxpro and have only limited experience with Access.

David Wright
P.S. This still will also have to take into consideration, the TAX_CODE on the main form, I'll be happy to get one part working first.