Global variable with a small amount of code

[megamef]

Hi All,

I'm writing an accounts package and need to store the current VAT rate with each record; I don't want the user to have to enter this number manually, but I want some sort of one time access to it so I can change it if the VAT rate changes.

I have a large number of forms so i'm hoping to use just a single line of code on each form something like:

VATrateVariable = [VATrate]

in the on load event for each form.

What I don't know is how and where to manually store the VAT rate globally as variable.

Cheers Steve

 

[pr2-eugin]

megamef, why not create a table that will have not many but four fields..

tbl_vatRates
vatID - PK
dateFrom
dateTo
vatRate

So this way you will have a history of the VAT rates that were from one period to another and also makes it easier to calculate..

 

[megamef]

Yes that sounds like a good idea, how to assign a table value to a global variable ?

 

[megamef]

OK, I've been playing around and I've stored my VatRate in a new table and I'm assigning that value to a global variable called "VatVar" in the on load event of my main menu screen.

Then in each form's on current event I'm making the record vat rate equal the variable vat rate.

This works great.

But how do I do the same on a report?

I'm currently calculating vat rate by just multiplying the value in a text box by "0.20" in the data source of my report vat field, I've tried replacing "0.20" with VatVar but this doesn't work.

Any ideas, and thanks for your help so far

 

[Alastair]

megamef,

Mind me asking how you defined your global variable from your table? I am trying to do something very similar. But still haven't managed to figure this out, I did start my own thread on this subject but so far I haven't received much response. Perhaps we can help each other?

 

[megamef]

Hi Alastair,

Yes I used a new module and defined "VatVar" there.

So in Module1 i just wrote:
Global VatVar

and then saved that module.

Then I made a new table called that I called T_VATrate, that had one field (CurrentVatRate) and one record (0.2).

Then I used that table as the record source of my welcome screen. Then in the onload event for my welcome screen i used: VatVar = Me.CurrentVatRate

As my welcome screen is the first thing to load this value is always set first and if the vat rate changes in the future i only need to change it once in that table.

 

[megamef]

Oh yeah, one more thing you need to have CurrentVatRate as a field on your form somewhere or else you can't use the "Me.CurrentVatRate" part but you can set it's visible property to false to stop the user seeing it.

 

[RainLover]

megamef, why not create a table that will have not many but four fields..

tbl_vatRates
vatID - PK
dateFrom
dateTo
vatRate

So this way you will have a history of the VAT rates that were from one period to another and also makes it easier to calculate..

Paul
There is a lot more to it than meets the eye.
However in situations like this delete the Field dateTo as it is not required
I am sure you can still write the code without it.

 

[Alastair]

Hi Megamef,

Thanks for the info, that sounds like it may work for me. I will take a look at this when I get a bit more time.

One thing that I have been advised to do is to use tempVars rather than Global variables, this may help you with your question with the Report. Unfortunately I can't paste a link at the moment as I haven't made enough posts.

But my thread is called: Define Global/Public variables from a Table?

 

[megamef]

Ah yes this thread was a little while ago so I already found a solution for that, just forgot to post about it.

I used a public function so in my module:

 Public Function GetVAT()
GetVAT = VatVar
end function [code/]

Then in my report i make the data source on a textbox "=getvat()"

 

[Trilback]

This is what I am looking for too. I have a forum called Manual Issue Forum with an unbound text box that I want to pass what was typed into that textbox back to my other forum "Warranty" to the IssueBox on the Warranty forum. As you see below I declared it but when I open the "Manual Issue" forum from the Warranty forum, I get =#Name? the source of unbound textbox in my "Manual Issue" forum is =GetIssue().

Public Function GetIssue()
Public VarIssue As String
GetIssue = VarIssue
End Function

 

[Mihail]

Hey !
Is anyone that seen Paul's solution (amended by Rain) ?
May I ask you what will happen after the VAT rate will be changed ?
How you recalculate a part that used the old rate ?
But after 100 years (after the VAT rate will be changed for 16 times) ?

@Paul (and Rain too)
I tried (successfully) to implement a solution like yours (about prices) but I founded out that is easier (and faster) to store the actual price in the same table with the invoice details than to use a table that store prices by item and date.
This way I maintain only one table (Product - ActualPrice) and still I'm able to recalculate any earlier invoice. If needed, with a bit of work, I can also calculate a history of prices from the InvoicesDetails table. I know: the "price" for this approach is an increased size of the DB.

How you comment this ? Thank you.

@megamef
Why you need a global variable if you already have a table where you store the actual VAT?
You can use this table in your queries or, because is a one row table, you can use either DLookup either DSum in order to extract the VAT from this table.