Page 1 of 3 123 LastLast
Results 1 to 15 of 38

Thread: currency format

  1. #1
    Join Date
    May 2009
    Posts
    72

    Unanswered: currency format

    Hi
    I have an issue

    I have forms which have textboxes that are bound and some are unbound(calculated textbox)

    all the above textbox are in currency format

    to handle multiple currencies, iam presently using the below code
    If Me.VENDORPAYCURRENCY.value = "usd" Then
    Me.TAMT.Format = "$#,##0.00;($#,##0.00)"
    Else
    Me.TAMT.Format = "#,##0.00;($#,##0.00)"
    End If

    what I want
    I want this code to be global
    that means
    on form current and load event , the global code checks the status of VENDORPAYCURRENCY.value
    and formats all the textboxes those are using currency format with the appropriate currency

    thanks in advance
    Hemanth

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Provided that there is already a format defined for the concerned TextBox controls, you could use something like:
    Code:
    Dim ctl as Control
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
            If Len(ctl.Format) > 0 Then
                Select Case Me.VENDORPAYCURRENCY.value
                    Case "usd":    ctl.Format = "$#,##0.00;($#,##0.00)"
                    Case Else:     ctl.Format = "#,##0.00;($#,##0.00)"
                End Select
            End If
        End If
    Next ctl
    Have a nice day!

  3. #3
    Join Date
    May 2009
    Posts
    72

    format

    thanks for the reply sinndho
    I have small problem with the code

    this code is formatting all textbox

    I want this code to format only those text box that have been formatted with currency only

    I want the textbox that has been formatted with percent or standard or general number to be left alone

    thanks again

    Hemanth

  4. #4
    Join Date
    May 2009
    Posts
    72
    figured it out
    If ctl.format="currency" Then
    again
    is it possible to make this a public function like formatcurrency()

    thanks

  5. #5
    Join Date
    May 2009
    Posts
    72

    format

    HI

    while formatting a currency I use this line of code in vba
    "$#,##0.00"

    when I replace $ with Rs the code does not work

    Please help

    Hemanth

  6. #6
    Join Date
    May 2009
    Posts
    72

    format

    Hi

    Still in troubled waters

    Iam using this code courtesy sinndho

    Dim ctl as Control
    For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
    If Len(ctl.Format) > 0 Then
    Select Case Me.VENDORPAYCURRENCY.value
    Case "usd": ctl.Format = "$#,##0.00;($#,##0.00)"
    Case Else: ctl.Format = "#,##0.00;($#,##0.00)"
    End Select
    End If
    End If
    Next ctl

    the problem is ... it is formatting all textboxes
    I have few textboxes on the form that's formatted with currency (property sheet)
    I want only those to be formatted by the above code

    please help

    Hemanth

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by krishnanhemanth View Post
    the problem is ... it is formatting all textboxes
    I have few textboxes on the form that's formatted with currency (property sheet)
    I want only those to be formatted by the above code
    Then change the test:
    1. Set the Tag property of all the textbox controls that must be formatted to: Currency.
    2. Change the test in the code accordingly:
    Code:
    If ctl.ControlType = acTextBox Then
        If ctl.Tag = "Currency" Then
    Select Case Me.VENDORPAYCURRENCY.value
    ...
    Have a nice day!

  8. #8
    Join Date
    May 2009
    Posts
    72
    Hi Sinndho
    I changed the code to what you suggested

    but still its not working

    Hemanth

  9. #9
    Join Date
    May 2009
    Posts
    72
    Hi Sinndho

    I changed to
    If ctl.Tag = "Currency" Then
    now only the required textboxes are getting formatted correctly
    but
    the currency is not changing
    what I mean is..
    the textbox is getting formatted as per the code, but if the first record is dollar, then all records are formatted with dollar,. if the first record is GBP, then all records are formatted with GBP
    The code I used is
    Dim ctl As Control
    For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
    If ctl.Tag = "currency" Then
    Select Case Me.VENDORPAYCURRENCY.value
    Case "dollar": ctl.Format = "$#,##0.00;($#,##0.00)"
    Case "GBP": ctl.Format = "#,##0.00;(#,##0.00)"
    Case "INR": ctl.Format = "currency"
    End Select
    End If
    End If
    Next ctl


    Hemanth
    Last edited by krishnanhemanth; 12-04-13 at 09:19.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Where in the module did you place that code? When is it executed?
    Have a nice day!

  11. #11
    Join Date
    May 2009
    Posts
    72

    format

    HI Sinndho

    I have placed the code at

    form....current event
    load event

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if this is a multi currency application then it may make more sense to have a currency table with all the relevant formatting masks define there
    use a call to a function to do the formatting
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This code works as expected (I tested it):
    Code:
    Private Sub Form_Current()
    
        Dim ctl As Control
    
        For Each ctl In Me.Controls
            If ctl.ControlType = acTextBox Then
                If ctl.Tag = "currency" Then
                    Select Case Me.VENDORPAYCURRENCY.Value
                        Case "dollar": ctl.Format = "$#,##0.00;($#,##0.00)"
                        Case "GBP": ctl.Format = "#,##0.00;(#,##0.00)"
                        Case "INR": ctl.Format = "currency"
                    End Select
                End If
            End If
        Next ctl
        
    End Sub
    Set a breakpoint on the line containing the Select Case statement and, when the VBA editor pops up, progress line by line (F8 key) and see what happens.
    Have a nice day!

  14. #14
    Join Date
    May 2009
    Posts
    72

    currency formatting

    Hi

    Thank you all for your answers
    you are right sinndho... I missed updating the tag property as currency
    now every textbox is getting formatted as per code

    but

    now I am stuck with something new

    all unbound textboxes are not formatting as per code

    what could be the reason

    Hemanth

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by krishnanhemanth View Post
    all unbound textboxes are not formatting as per code

    what could be the reason
    Sorry, I don't understand. Which unbound textboxes?

    Keep in mind that I do not know anything about your project and that I don't have the database open in front of me.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •