Use text file to provide the default value for a field in Access form

Question

I have a pop-up window in Microsoft Access containing text box fields that are required to be filled out by the user, for example:

First Name:
Last Name:

Now I'm trying to create a button that when clicked would look into C:\mytextfile.txt and auto-populate those fields.

inside the text file it would look like this:

##$@#%#$543%#$%#$$#%LAST NAME:BOB#$#@$@#$@#$@#FIRST NAME:DERRICK$#%$#%$#%#$%$#%$#

So essentially I'm looking for 3 things:

to access the text file
to parse for the data
to populate it into the text boxes. (The data doesn't need to go into a table until the "SAVE" button is clicked")

Update: This is what I've written so far, I'm not sure why it's not working.

Private Sub LoadText_Click()

    Dim myFile As String myFile = "C:\myFile.txt"
    Me.NameofTextbox = Mid(myFile, 7, 3)

End Sub

mainly 2 and 3, I'm a newbie. I don't need a full answer with code, just suggestions on how to get by would be helpful enough. — user519753, Apr 14 '14 at 17:04
Well for #2 you could use [regex](http://stackoverflow.com/a/8146688/119477) or if that's to complicated you could use the built in string functions. Populating the text boxes should be easy `Me.txtfoo = strSomestring`. You might want to update your question to reduce the scope. — Conrad Frix, Apr 14 '14 at 17:25
You need to look into how to read a file. All you're doing in your code is setting a string equal to `C:\myFile.txt`, so `Mid(myFile, 7, 3)` will return `le.`. You're not actually reading the contents of the file. — , Apr 17 '14 at 02:13
"A detailed canonical answer is required to address all the concerns." Huh? You have several different problems, all your own making. Someone is prolly going to write this for you (what's a bounty for?), but more info will be needed. — Smandoli, Apr 17 '14 at 18:08
I'm curious about the text file. I've never seen a string like that. — Smandoli, Apr 17 '14 at 21:07
@Smandoli it looks a bit like redis protocol. But the strangest part is various separators after field values. — 4dmonster, Apr 18 '14 at 07:32

score 1 · Accepted Answer · edited Apr 18 '14 at 12:44

Here example for file you provided and controls on Form that are named txtboxLastName and txtboxFirstName

Dim mFields() As String ' array with fields' names in file
Dim mControls() As String ' corresponding controls' names
Dim mStopChars() As String ' Characters that put after values

Dim tmpstr As String
Dim content As String

Dim i As Long
Dim fStart  As Long
Dim valStart As Long
Dim valEnd As Long
Dim FieldValue As String
Dim j As Long
Dim tmp As Long

' prepare maps

' here : included in field name for common case
mFields = Split("LAST NAME:,FIRST NAME:", ",") 
mControls = Split("txtboxLastName,txtboxFirstName", ",")
mStopChars = Split("#,$,@,%", ",")

' read file into string
Open "c:\mytextfile.txt" For Input As #1

Do While Not EOF(1)
    Input #1, tmpstr
    content = content & tmpstr
Loop

Close #1

' cycle through fields and put their values into controls
For i = LBound(mFields) To UBound(mFields)
    fStart = InStr(1, content, mFields(i))
    If fStart > 0 Then
        valStart = fStart + Len(mFields(i))  'value start at this pos
        'cycle through possible stop chars to locate end of current value
        valEnd = Len(content)
        For j = LBound(mStopChars) To UBound(mStopChars)
            tmp = InStr(valStart, content, mStopChars(j))
            If tmp > 0 Then
                If tmp <= valEnd Then
                    valEnd = tmp - 1
                End If
            End If
        Next j
        ' cut value
        FieldValue = Mid(content, valStart, valEnd - valStart + 1)
        ' assign to control
        Me.Controls(mControls(i)).Value = FieldValue
    End If
Next i

I'll give it a try, but "As #1" is usually for when using Microsoft Excel, representing #1 on a grid, I'm using Microsoft Access. — user519753, Apr 17 '14 at 11:26
+1 ... I just hope I remember to check back here the next time I am called up to deal with a case of _Redis Protocolus_. — Smandoli, Apr 18 '14 at 12:51

Use text file to provide the default value for a field in Access form

1 Answers1