Currency/Number formats from table.

[flect]

G'day

I deal with a lot of different countries in my db which have a variety of number/currency formats.

I have a table containing the [countyname] [contactname] and [currencyformat] -

What I would like to do is have the [currencyformat] field automatically apply to the appropriate text boxes on the reports ie-



- but I can't seem to be able to program it.

Any ideas or suggestions?

 

[boblarson]

Format is a string - it doesn't take an =[field] argument. However, you can set it via code.

 

[flect]

I thought as much....

Any idea on how/where to start with the code?

My VBA experience is rather limited to _Click(), msgbox's and basic docmd. stuff.

 

[khawar]

do it on "On Format" event

 

[flect]

Thanks guys -

I've managed to get the appropriate data happening as a qry or sql string, but I'm having 2 problems

1) i'm having trouble with the appropriate syntax to refer to the result of the qry/sql in VBA - i can perform the query but my text box format: refers to the whole qry string, rather than the result of it; ie Format = "S'ele'Ct currEncy fOrmat frO(m).., rather than Format = "SG$"#,##0.00"

2) even if I explicitlaty dim cformat to the value "SG$"#,##0.00" - it still removes the quotations and renders it similar to ' Format = "S'ele'Ct currE()ncy fOrmat..'

so, first thing's first - what is the correct VB syntax to explicitly register the string (eg) "SG$"#,##0.00" as the format

and second, how do I make that a variable so that it will reflect the record i've selected through VB/SQL - ie: "HK$"#,##0.00" or #.##0,00" Kr"

 

[flect]

Never mind; got it working thanks to the awesome people on this fantastic forum!!!

DLookup() is my friend.



Final working code was:

Private Sub report_open(cancel as integer)
Dim Cformat As String
Cformat = DLookup("currencyformat", "tblagency", "agencyID = [forms]![frmnavigation]![masterfilter]")

StockValA.Format = Cformat
StockValB.Format = Cformat
VALTOT.Format = Cformat
txtcommission.format = Cformat
End Sub