Results 1 to 6 of 6
  1. #1
    krishnanhemanth is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    2

    formatting

    Hi
    looking for a better solution

    I have a form with few textboxes in it. some of them are bound, some of them calculated
    all textboxes (bound or unbound) that are assigned for numerical values are formatted with "currency" from the property sheet
    I have a combobox called "CURRENCY TYPE" on the form .. I can select the type of currency used for a transaction... usd, euro, gbp, inr

    what I want is

    after the "CURRENCY TYPE" is selected, I want all the textbox that are formatted with "currency" to be reformatted with the new currency symbol $ for usd, £ for pound, € for euro.
    the code iam using now is

    Dim ctl As Control
    For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
    If ctl.Format = "currency" Then
    If Me.VENDORPAYCURRENCY.value = "DOLLAR" Then
    ctl.Format = "$#,##0.00;($#,##0.00)"


    Else
    If Me.VENDORPAYCURRENCY.value = "INR" Then
    ctl.Format = "CURRENCY"
    Else
    If Me.VENDORPAYCURRENCY.value = "GBP" Then
    ctl.Format = "£#,##0.00;(£#,##0.00)"
    Else
    If Me.VENDORPAYCURRENCY.value = "EURO" Then
    ctl.Format = "€#,##0.00;(€#,##0.00)"
    End If
    End If
    End If
    End If
    End If
    End If
    Next ctl

    the above code is not working
    Please help
    Hemanth

  2. #2
    BluffMeAllIn is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2013
    Location
    Canada
    Posts
    21
    Hi Hemanth,

    What exactly isn't working for it? Is it just not applying the new format, is it giving an error message?

    Do you have this code in an event for the currency combo box such as on_change, so that anytime the value of the combo box changes it will execute the code and thus should reformat effectively? There is also a possibility that to have the new format applied you may have to refresh the data within the fields (just a though if you have the code etc as I have indicated).

    If able to switch between the currency once the data is changed, I'm assuming you also have logic linked to conversion rates? Or is the currency simply a field stored for the vendor that when the data loads for a specific vendor it will have a currency already and as such the fields should be formatted according to this?

    Any details much appreciated to assist.

    Thanks,
    Dave

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Have a look at Doctor9's suggestion here

    http://www.utteraccess.com/forum/For...-t2012309.html

    where you've double-posted the question.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So there are controls that have the format property set to "Currency".
    Then you select "EURO" from the combo box. The text boxes now have the Format property set to "€#,##0.00;(€#,##0.00)".

    Next, you select "GBP" from the combo box. There is a line of code
    Code:
    If ctl.Format = "currency" Then
    This line is checking to see if the text box format property is "Currency"; it is not because the format had been changed to the Euro format.
    So nothing is changed for that text box.

    For each of the text boxes that you want to be able to change the currency format, enter "Currency" in the TAG property.
    Then change the code to
    Code:
    Private Sub VENDORPAYCURRENCY_AfterUpdate()
       Dim ctl As Control
       For Each ctl In Me.Controls
          If ctl.ControlType = acTextBox And ctl.Tag = "Currency" Then
             Select Case Me.VENDORPAYCURRENCY
                Case "DOLLAR"
                   ctl.Format = "$#,##0.00;($#,##0.00)"
                Case "INR"
                   ctl.Format = "CURRENCY"  '<<< -- is this correct for "INR"???
                Case "GBP"
                   ctl.Format = "£#,##0.00;(£#,##0.00)"
                Case "EURO"
                   ctl.Format = "€#,##0.00;(€#,##0.00)"
             End Select
    
          End If
       Next ctl
    
    End Sub

  5. #5
    krishnanhemanth is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    2
    hi
    thank you all for your answers
    ssanfu ... thanks ...I updated the tag property for all textboxes and its working


    but

    now I am stuck with something new

    all unbound textboxes are not formatting as per code

    what could be the reason

    Hemanth

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    When I tested the code, I all of the text boxes were unbound... All the formats changed.

    Do all of the text boxes (bound & unbound) that you want to change the format have the tag property set to "Currency" or to whatever word you used?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Even Odd row formatting
    By Perceptus in forum Reports
    Replies: 3
    Last Post: 12-06-2012, 01:50 PM
  2. Need some help in formatting my report
    By ammarbohra@gmail.com in forum Reports
    Replies: 3
    Last Post: 04-09-2012, 07:46 PM
  3. Conditional Formatting vs. VBA Formatting
    By gopherking in forum Reports
    Replies: 5
    Last Post: 11-09-2011, 06:53 PM
  4. Correct Formatting of SQL in VBA?
    By Coffee in forum Queries
    Replies: 6
    Last Post: 08-04-2011, 12:03 AM
  5. Between formatting
    By reidn in forum Queries
    Replies: 2
    Last Post: 07-21-2011, 12:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums