Why only one Current event for continuous forms?

[David Anderson]

Using Access 2000, I have designed a form that is intended to be used in
continuous forms mode and which contains both an Amount field and a
CurrencyID field. I want to ensure that the Amount field is always properly
formatted for the relevant currency.

Perhaps I am missing more obvious solutions, but I chose to put the
following code in the Current event of this form:

Select Case Me![CurrencyID]
Case 1 'Pounds Sterling
Me![Amount].Format = "£###0.00"
Case 2 'Euros
Me![Amount].Format = "€###0.00"
Case 3 'US Dollars
Me![Amount].Format = "$###0.00"
End Select

The problem is that the Current event is only being triggered once, even
though the underlying table contains many records. All instances of the form
in continuous forms mode therefore have the same formatting of the Amount
field - as set by the first record.

Why do I not get a Current event triggered for every instance of the form,
i.e. for every record in the table? How do I resolve this problem?

David

 

[missinglinq via AccessMonster.com]

Why do I not get a Current event triggered for every instance of the form,

i.e. for every record in the table?

There is only one instance of the form! There are multiple instances of each
control, because that's the way Continuous forms work in Access. The
Form_Current event is triggered each time you move to another record. If, for
instance, you move from a record where the CurrencyID is Euros to a record
where the CurrencyID is Dollars, *all* amount fields for all records will go
from being formatted as Euros to being formatted as Dollars. This is how
Access works with Contnuous Forms!

How do I resolve this problem?

The only workaround I can think of is to do your *formatting* as your amount
is entered. Your amount field would have to be text and if you need to use
the [Amount] field later for calculations, you'd have manipulate it to strip
off the currency symbol and convert it to a numerical datatype, which is
doable. This code assumes that the CurrencyID is entered first (you'll have
to code to assure this.) I don't have the time to find out the ASCII codes
for the various currency symbols, so you'll have to get that and substitute
it for the "PS" and "E" for the Pounds Sterling and Euros in the code.

Private Sub Amount_LostFocus()
Select Case Me.CurrencyID
Case "Pounds Sterling"
Me.Amount = "PS" & Me.Amount
Case "Euros"
Me.Amount = "E" & Me.Amount
Case "Dollars"
Me.Amount = "$" & Me.Amount
End Select
End Sub

This is probably not the easy answer you were looking for, but maybe it'll
point you in the right direction! OR maybe someone else will have a better
idea!

Good Luck!

 

[Bob Quintal]

=?Utf-8?B?RGF2aWQgQW5kZXJzb24=?=

Thanks for clarifying the situation. I've never developed an
Access app with mixed currencies before, so I never knew that
continuous forms behaved in that way. You're also right about
the formatting changing if you click from one record to
another. I hadn't noticed that until you mentioned it!

Thanks also for the suggested solution. I guess another way
would be to have two amount fields in my table, one numeric
and one formatted.

David

You do not need to put it in the table as formatted. You do want to
have a query which holds a formatted copy of the amount, to use as
the source for your form or report.

 

[David Anderson]

Thanks for clarifying the situation. I've never developed an Access app with
mixed currencies before, so I never knew that continuous forms behaved in
that way. You're also right about the formatting changing if you click from
one record to another. I hadn't noticed that until you mentioned it!

Thanks also for the suggested solution. I guess another way would be to have
two amount fields in my table, one numeric and one formatted.

David

Why do I not get a Current event triggered for every instance of the form,
i.e. for every record in the table?

There is only one instance of the form! There are multiple instances of each
control, because that's the way Continuous forms work in Access. The
Form_Current event is triggered each time you move to another record. If, for
instance, you move from a record where the CurrencyID is Euros to a record
where the CurrencyID is Dollars, *all* amount fields for all records will go
from being formatted as Euros to being formatted as Dollars. This is how
Access works with Contnuous Forms!

How do I resolve this problem?

The only workaround I can think of is to do your *formatting* as your amount
is entered. Your amount field would have to be text and if you need to use
the [Amount] field later for calculations, you'd have manipulate it to strip
off the currency symbol and convert it to a numerical datatype, which is
doable. This code assumes that the CurrencyID is entered first (you'll have
to code to assure this.) I don't have the time to find out the ASCII codes
for the various currency symbols, so you'll have to get that and substitute
it for the "PS" and "E" for the Pounds Sterling and Euros in the code.

Private Sub Amount_LostFocus()
Select Case Me.CurrencyID
Case "Pounds Sterling"
Me.Amount = "PS" & Me.Amount
Case "Euros"
Me.Amount = "E" & Me.Amount
Case "Dollars"
Me.Amount = "$" & Me.Amount
End Select
End Sub

This is probably not the easy answer you were looking for, but maybe it'll
point you in the right direction! OR maybe someone else will have a better
idea!

Good Luck!

 

[David Anderson]

Bob,
I agree. That's a better way to do it - though it does seem to require a
very long expression in the query.

My first attempt at writing this expression is as follows (the final version
will have to be even longer to cover more currency variations),

AmountFmtd:
IIf([Payments]![CurrencyID]=1,Format([Payments]![Amount],"£0.00"),"")
& IIf([Payments]![CurrencyID]=2,Format([Payments]![Amount],"€0.00"),"")
& IIf([Payments]![CurrencyID]=3,Format([Payments]![Amount],"$0.00"),"")

Is there a slicker way to code that expression?

David

 

[Marshall Barton]

I agree. That's a better way to do it - though it does seem to require a
very long expression in the query.

My first attempt at writing this expression is as follows (the final version
will have to be even longer to cover more currency variations),

AmountFmtd:
IIf([Payments]![CurrencyID]=1,Format([Payments]![Amount],"£0.00"),"")
& IIf([Payments]![CurrencyID]=2,Format([Payments]![Amount],"€0.00"),"")
& IIf([Payments]![CurrencyID]=3,Format([Payments]![Amount],"$0.00"),"")

You do not need to put it in the table as formatted. You do want to
have a query which holds a formatted copy of the amount, to use as
the source for your form or report.

I suggest that you have a table with the CurrencyID,
currency text and the currency symbol. You probably already
have a lookup table with the first two fields, so just add
the symbol field.

Then the form's record source query can join to that table
and have a field for the symbol.

With that in place, add a text box and set its control
source expression to:
=CurrencySymbol & Format(Amount, "0.00")
Size and position this text box directly on top of the
Amount text box (Format menu - Bring To Front). To make
sure the formatted value can not be edited, add a line of
code to its GotFocus event:
Me.Amount.SetFocus

When the focus is set to the Amount text box, it will
automatically be brought to the front so the unformatted
value can be edited. When a user moves the focus away from
the amount text box, the formatted text box will again be in
front so all records will display the way you want.

 

[Bob Quintal]

=?Utf-8?B?RGF2aWQgQW5kZXJzb24=?=

Bob,
I agree. That's a better way to do it - though it does seem to
require a very long expression in the query.

My first attempt at writing this expression is as follows (the
final version will have to be even longer to cover more
currency variations),

AmountFmtd:
IIf([Payments]![CurrencyID]=1,Format([Payments]![Amount],"Â £
0.00"),"")
& IIf([Payments]![CurrencyID]=2,Format([Payments]! [Amount],"€
0.00"),"") &
IIf([Payments]![CurrencyID]=3,Format([Payments]! [Amount],"$0.00
"),"")

Is there a slicker way to code that expression?

David

AmountPaid:
Format([payments],choose([CurrencyID],"£","€","$")&"0.00")

Or you could create a user defined function,
or use a lookup table as mr Barton suggested.

You do not need to put it in the table as formatted. You do
want to have a query which holds a formatted copy of the
amount, to use as the source for your form or report.

 

[David Anderson]

Thanks to both of you for some very useful suggestions. I will probably go
with Bob's solution on this occasion. The use of the Choose Function (which I
had not come across before) enables a much more compact query expression than
my original. I also have a general preference for simple solutions like this
that I will instantly understand when I re-read the code a year or two later.
Marshall's overlapping fields solution is ingenious but marginally less
intuitive to my aging brain.

Thanks again to MissingLinq for clarifying the behaviour of continuous forms.

This is a great forum for rapid and helpful assistance. Keep up the good
work, guys, it's very much appreciated!

David

 

[Marshall Barton]

Thanks to both of you for some very useful suggestions. I will probably go
with Bob's solution on this occasion. The use of the Choose Function (which I
had not come across before) enables a much more compact query expression than
my original. I also have a general preference for simple solutions like this
that I will instantly understand when I re-read the code a year or two later.
Marshall's overlapping fields solution is ingenious but marginally less
intuitive to my aging brain.

Thanks again to MissingLinq for clarifying the behaviour of continuous forms.

This is a great forum for rapid and helpful assistance. Keep up the good
work, guys, it's very much appreciated!

While using the Choose function is simple, it is not a good
long term approach. It requires you to embed data (currency
symbol) in your application instead of in an easily modified
table. Think about the effort required at some future time
when you decide you need to add another currency. With
Choose or similar texhniques, you will have to find every
place in your app where you have hard coded the symbols,
modify the code and redistribute your app. If you put the
currency symbols in a table, then the only effort that's
needed to add a new symbol is for a user to add another
record to the currencies table with no effort on your part.

 

[David Anderson]

Marshall,
You make a persuasive case. I will give your proposal a second look when I
get back to coding my app tomorrow.

Thanks,
David