VB Calculate and update field in a Form

Hi all!

I want to take the value from one form-field, deduct the VAT and return the
value in another form-field. I have made a Public Function for this and
attached it to the text-box. Can anybody help me with the correct syntax?

Here's what I got so far:

Public Function specify_VAT()
Dim vat
vat = "[price]/1.24"
End Function

I suppose what I want to know is how to put the result of this into the
form-field in question. Any thought appreciated.

Sven

re: VB Calculate and update field in a Form

You need this twice, once after update of the price and once OnCurrent of
the form so that you get the correct value for each record.
I am assuming you have one field called Price, bound to a field in a table,
and one field called Vat which is unbound on your form.

Private Sub Price_AfterUpdate()

Dim VatRate As Single
VatRate = 17.5 ' UK Vat Rate

Vat = Price / (1 + VatRate / 100) * VatRate / 100

End Sub

I think it is bad policy to hard code in the VAT Rate, greedy governments
keep wanting more. Much better to hold this in a table

Phil
"Sven Seljom" <sven@seljom.no> wrote in message
news:5vbpb.3672$mf2.46960@news4.e.nsc.no...[color=blue]
> Hi all!
>
> I want to take the value from one form-field, deduct the VAT and return[/color]
the[color=blue]
> value in another form-field. I have made a Public Function for this and
> attached it to the text-box. Can anybody help me with the correct syntax?
>
> Here's what I got so far:
>
> Public Function specify_VAT()
> Dim vat
> vat = "[price]/1.24"
> End Function
>
> I suppose what I want to know is how to put the result of this into the
> form-field in question. Any thought appreciated.
>
> Sven
>
>
>[/color]

re: VB Calculate and update field in a Form

Thanks Phil

Your assumptions are correct, but how to I bind this Sub to the Vat-field,
and how is it fired?



"Phil Stanton" <phil@stantonfamily.co.uk> skrev i melding
news:3fa55275$0$116$65c69314@mercury.nildram.net.. .[color=blue]
> You need this twice, once after update of the price and once OnCurrent of
> the form so that you get the correct value for each record.
> I am assuming you have one field called Price, bound to a field in a[/color]
table,[color=blue]
> and one field called Vat which is unbound on your form.
>
> Private Sub Price_AfterUpdate()
>
> Dim VatRate As Single
> VatRate = 17.5 ' UK Vat Rate
>
> Vat = Price / (1 + VatRate / 100) * VatRate / 100
>
> End Sub
>
> I think it is bad policy to hard code in the VAT Rate, greedy governments
> keep wanting more. Much better to hold this in a table
>
> Phil
> "Sven Seljom" <sven@seljom.no> wrote in message
> news:5vbpb.3672$mf2.46960@news4.e.nsc.no...[color=green]
> > Hi all!
> >
> > I want to take the value from one form-field, deduct the VAT and return[/color]
> the[color=green]
> > value in another form-field. I have made a Public Function for this and
> > attached it to the text-box. Can anybody help me with the correct[/color][/color]
syntax?[color=blue][color=green]
> >
> > Here's what I got so far:
> >
> > Public Function specify_VAT()
> > Dim vat
> > vat = "[price]/1.24"
> > End Function
> >
> > I suppose what I want to know is how to put the result of this into the
> > form-field in question. Any thought appreciated.
> >
> > Sven
> >
> >
> >[/color]
>
>[/color]

re: VB Calculate and update field in a Form

You didn't set the value into the Function but you need to. Do that, and put
it in the Control Source of the Control. (I assume you don't want to store a
value that you can calculate when you need it.). I think if I was deducting
VAT, I'd call the function something other than "specify_VAT", too? And I'd
pass the price as an argument so I could reuse the Function if I needed it
elsewhere, too. Phil may have a good point, too, about the VAT changing --
unless there are different VAT rates used for different locales, a Public
(global) Constant would be an option to keeping it in a table.

Thus, I might have a Control Source of

= PriceWithoutVAT([Price])

Larry Linson
Microsoft Access MVP

"Sven Seljom" <sven@seljom.no> wrote in message
news:5vbpb.3672$mf2.46960@news4.e.nsc.no...[color=blue]
> Hi all!
>
> I want to take the value from one form-field, deduct the VAT and return[/color]
the[color=blue]
> value in another form-field. I have made a Public Function for this and
> attached it to the text-box. Can anybody help me with the correct syntax?
>
> Here's what I got so far:
>
> Public Function specify_VAT()
> Dim vat
> vat = "[price]/1.24"
> End Function
>
> I suppose what I want to know is how to put the result of this into the
> form-field in question. Any thought appreciated.
>
> Sven
>
>
>[/color]