Results 1 to 7 of 7
  1. #1
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81

    Set textbox number format with VBA

    Hi,

    I'm stuck trying to set the numerical format of a text box on a report. All of the documentation that I can find isn't working for me, can someone steer me in the right direction?

    1. The numerical format was set in the properties tab for that box, I set it to "General Number" and Decimal Places "0"
    2. I set the control source for the box programatically based on parameters when the report is launched:
    Code:
    Reports!OpenRecordRpt!daysElapsedBox.ControlSource = "COM_INSTATE"
    3. The numerical format is being overwritten by the contents of the control source, which are floats. I only want to see the integer of those floats, so I want to set it back to zero decimal places. This is what I tried:
    Code:
    Reports!OpenRecordRpt!daysElapsedBox.format = (0, "Fixed")
    This is causing a syntax error, what am I doing wrong here?

    Thanks in advance,



    -Paul
    Last edited by virgilio; 06-04-2020 at 10:25 AM. Reason: changed 'form' to 'report'

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:

    Reports!OpenRecordRpt!daysElapsedBox.ControlSource = CInt(COM_INSTATE)

    EDIT:
    Better still:

    Reports!OpenRecordRpt!daysElapsedBox.ControlSource = ABS(COM_INSTATE)
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You start saying 'text box on a report' then you say 'form is launched'. Which is it? If this is a report, can't change ControlSource if want report to print.

    Why don't you just set Format to "Fixed" instead of "General Number" in design?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    It thought that COM_INSTATE was an undeclared variable when I tried '= CInt(COM_INSTATE)'. I added quotes to make it '= CInt("COM_INSTATE")' and got a type-mismatch, I guess it took it as a string. "COM_INSTATE" is a field in the report source, if it isn't clear.

    And Thanks June, I changed the OP from 'form' to 'report'

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Revised my previous post, might review again.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Thanks June7, setting it to "Fixed" fixed it.

    Thanks also, Bob Fitz

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So you are still setting ControlSource in VBA? What event is code in? Have you opened report to PrintPreview? What happens?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Format textbox as email
    By slimjen in forum Forms
    Replies: 4
    Last Post: 08-09-2019, 09:20 AM
  2. Replies: 3
    Last Post: 06-10-2019, 01:49 PM
  3. Replies: 3
    Last Post: 02-01-2018, 03:10 AM
  4. Display textbox value with percent format
    By AmanKaur123 in forum Programming
    Replies: 2
    Last Post: 01-18-2017, 11:23 AM
  5. Number Format to General Format
    By mkc80 in forum Access
    Replies: 2
    Last Post: 05-05-2012, 02:10 AM

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