Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151

    Question Open text file in a form

    Hello Brains trust.

    Been struggling with this for a while and haven't been able to find a suitable answer.



    I have a large text file that I want to open and display on a form. I'm not doing anything with the text, it is just a reference for the viewer to be able to view and scroll through.

    I can open the file within the DB using Notepad, however this then allows the user to edit the file, which I don't want.

    Currently I have the text file opening as a message box, however the limits of the message box means that the entire contents of the text file is not displayed.

    Code:
    Private Sub VersionControl_Click()
    
        Dim fs As FileSystemObject
        Dim f, a
        Dim dbName As String
        Dim dbPath As String
    
        dbName = Me.Application.CurrentDb.Name
        dbPath = Left(dbName, InStrRev(dbName, "\"))
    
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set f = fs.OpenTextFile(dbPath & "_Readme.txt")
    
        a = f.ReadAll
    
        MsgBox a, , "Version History"
    
    End Sub
    What I think I need to do is create a form, with a text box, and import the contents of the text file in to the text box. This is where I am stuck.

    Has anyone tried this approach in the past? Will this work? Is there a limit to the number of characters that the text box can hold?

    Thank you in advance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Limit of 65,535 characters in textbox https://support.office.com/en-us/art...8-98c1025bb47c
    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.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That limit of 65535 characters only applies if text is entered directly. You can have many millions of characters if entered programmatically - up to 1GB of storage space.
    From the same article:

    65,535 when entering data through the user interface;
    1 gigabyte of character storage when entering data programmatically
    Here's an example with over 4.6 million characters. It loads quickly as well!

    Click image for larger version. 

Name:	LargeTextFile.PNG 
Views:	32 
Size:	115.9 KB 
ID:	39906
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Thanks Colin, good to know. I only looked at the textbox spec, not field in table.

    So importing to a field in table should be possible.
    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.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes indeed. I use that approach all the time for the JSON Analyser app in the screenshot.
    Some of the files are far larger than the one shown.
    I've used it on JSON files of almost 80MB successfully.
    Only one file, a 152MB monster used as a test, couldn't be handled that way as it caused an out of memory error.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Thanks for the feedback guys. To be clear, I don't actually want the text file imported in to a table. I just want the user to be able to read, and scroll through the text file on the form, then close the form when done.

    What I can't figure out is the code to import the text file in to an unbound text box on the form.

    Any help here would be great.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    See attached for a simple demo that reads a specified text file - its currently set to read the supplied text file from the same folder

    The module code contains several functions related to text files - create/write/edit as well as read
    Hope that helps
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by isladogs View Post
    See attached for a simple demo that reads a specified text file - its currently set to read the supplied text file from the same folder

    The module code contains several functions related to text files - create/write/edit as well as read
    Hope that helps
    Thank you very much for the sample code. Some juicy gems in there that I'll be able to use later.

    This has helped me solve my problem.

    I used the module you supplied, then created a popup form with an unbound textbox.

    I used the OnOpen Event of the form to execute the code
    Code:
    Private Sub Form_Open(Cancel As Integer)
         Me.txtVersionHistory = ReadTextFile(CurrentProject.Path & "\_readme.txt")
    
        With Me.txtVersionHistory
            .SetFocus
            .SelStart = Len(.Text)
        End With
    End Sub
    I had to add the With statement code so that the vertical scrollbar would show without the text being highlighted as the form opened. Otherwise the text would show, but the viewer had no way of knowing they could scroll down to read more unless they clicked in the textbox.

    The only problem I have now is that if the expected file is not found by the module, the form will still open and the text field is blank (as you would expect). There is a close button on the form, but it would be best if it didn't open at all. I'll work on that.

    Code:
    Public Function ReadTextFile(strFilename As String) As String
    
      Dim ret As Long
      
      Set objFso = CreateObject("Scripting.FileSystemObject")
      
      If objFso.FileExists(strFilename) Then
         Set logStream = objFso.OpenTextFile(strFilename)
         
         ReadTextFile = logStream.ReadAll
         logStream.Close
         Set logStream = Nothing
       Else
         ret = MsgBox("The _Readme.txt file was not found." & Chr(13) & "The file must be in the same location as the database.", vbCritical)
       End If
       
       Set objFso = Nothing
    End Function
    Again a huge thank you.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So maybe:

    If Dir(CurrentProject.Path & "\_readme.txt") = "" Then
    Cancel = True
    Else

    End If
    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.

  10. #10
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by June7 View Post
    So maybe:

    If Dir(CurrentProject.Path & "\_readme.txt") = "" Then
    Cancel = True
    Else

    End If
    Exactly. I modified the the code on the OnOpen event of the form (which meant that I could remove the error checking from the module).
    Code:
    Private Sub Form_Open(Cancel As Integer)
        If Dir(CurrentProject.Path & "\_readme.txt") = "" Then
            MsgBox "The _Readme.txt file was not found." & Chr(13) & "The file must be in the same location as the database.", vbCritical
            DoCmd.Close
            Exit Sub
        Else
        
        Me.txtVersionHistory = ReadTextFile(CurrentProject.Path & "\_readme.txt")
    
        With Me.txtVersionHistory
            .SetFocus
            .SelStart = Len(.Text)
        End With
        End If
        
    End Sub
    I used the DoCmd.Close instead of Cancel=True because the latter resulted in an Access message "The OpenForm Action was cancelled"

    Another thing I noticed was that the mouse scroll wheel didn't work in the text box. A bit of digging around and I found this excellent solution by one Daniel Pineault. (link takes you to another site)

    Thanks again Colin & June7

  11. #11
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    EDIT

    Turns out the DoCmd.Close doesn't work as desired. The form still loads then cancels the underlying form.

    Cancel = True works, but I get the Access message, which I can't seem to suppress.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you only want the form to open if the file exists then use this on the calling form:

    Code:
    If Dir(CurrentProject.Path & "\_readme.txt")<> "" Then
        DoCmd.OpenForm "Your form name"
    Else
       MsgBox "The _Readme.txt file was not found." & vbCrLf & "The file must be in the same location as the database.", vbCritical, "File not found"
    End If
    Next move the rest of your code to the Form_Load event - Form_Open isn't appropriate for this

    Code:
    Private Sub Form_Load()
         Me.txtVersionHistory = ReadTextFile(CurrentProject.Path & "\_readme.txt")
    
        With Me.txtVersionHistory
            .SetFocus
            .SelStart = Len(.Text)
        End With
        End If
    End Sub
    Doing this solves your issue of closing the form

    BTW - the file can be located somewhere else if preferred,
    Just use the actual folder path or a variable for that value instead of CurrentProject.Path

    Also recommend you don't start file names with an underscore
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by isladogs View Post

    Doing this solves your issue of closing the form

    BTW - the file can be located somewhere else if preferred,
    Just use the actual folder path or a variable for that value instead of CurrentProject.Path

    Also recommend you don't start file names with an underscore
    And so it does. Thanks Colin

    For simplicity I'll keep the readme.txt file in the same folder as the DB. I'm not a fan of hard coding folder locations. I also take your point about the underscore in the filename. It's a carry over I have from trying to keep the file at the top of the folder.

    Can you briefly explain why the OnOpen event wasn't appropriate?

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by sheusz View Post
    And so it does. Thanks Colin

    For simplicity I'll keep the readme.txt file in the same folder as the DB. I'm not a fan of hard coding folder locations. I also take your point about the underscore in the filename. It's a carry over I have from trying to keep the file at the top of the folder.

    Can you briefly explain why the OnOpen event wasn't appropriate?
    See this link about form events https://www.simply-access.com/Form-Events.html

    As its name suggests, the open event should be used only for code you want to run when the form is first opened.
    The more code you place there, the longer the form will take to load the form data.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  15. #15
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Thank's Colin. I'll brush up!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Button to open file/path in text box
    By samh4567 in forum Access
    Replies: 1
    Last Post: 05-30-2015, 09:32 PM
  2. Replies: 6
    Last Post: 11-08-2013, 11:18 AM
  3. Replies: 3
    Last Post: 02-22-2013, 06:41 AM
  4. Open pdf file from Access form
    By lios1984 in forum Access
    Replies: 7
    Last Post: 02-21-2012, 01:11 PM
  5. Open a text file to show results...
    By kkrishna in forum Programming
    Replies: 3
    Last Post: 08-13-2010, 09:38 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