How to save Unbound field data to specific txt-file? (1 Viewer)

[travel4u]

Hello,

I have kinda log-in form where is unbound field (LoginUserName).
If access is granted I need macro(?) to save that LoginUserName in to local drive (C:\Users\ActiveUser.txt) file, as Access is reading that file data later.
This ActiveUser.txt file has only one line of text (6 characters) eg. XYZXYZ.
I have fixed how Access can read that file but not how to save in to it.
I have tried this ImportExportText macro but no success:

 

[arnelgp]

create a function that will Append text to the file:

Public Function fncAppendToTextFile(ByVal pstrTextFile As String, _
                                    ByVal pstrContent As String, _
                                    Optional ByVal pbolAddLineFeed As Boolean = True)
    Dim txtFile As Object
    Const ForReading As Integer = 1
    Const ForWriting As Integer = 2
    Const ForAppending As Integer = 8
    Const Unicode As Integer = -1
    Const Ascii As Integer = 0

    With CreateObject("Scripting.FileSystemObject")
        If .FileExists(pstrTextFile) = False Then
            Set txtFile = .CreateTextFile(pstrTextFile, True)
        
        Else
    
            Set txtFile = .OpenTextFile(pstrTextFile, ForAppending, False, Ascii)
        
        End If
        With txtFile
            .Write pstrContent & IIf(pbolAddLineFeed, vbCrLf, vbNullString)
            .Close
        End With
    End With

End Function

call it using RunCode macro.

 

[travel4u]

Thanks Arnelgp for this.
I am not much done VBA's so not so familiar with this....
So this ActiveUser.txt file is already in each PC and code should just overwrite existing ActiveUser.txt with current user [LogInUserName] from Log-In form.
Should I replace in your code pstrTextFile with ActiveUser.txt or ActiveUser?
Where in code I put [LogInUserName]?

 

[arnelgp]

you need to modify the code if you will overwrite the textfile everytime:

Public Function fncAppendToTextFile(ByVal pstrTextFile As String, _
                                    ByVal pstrContent As String, _
                                    Optional ByVal pbolAddLineFeed As Boolean = True)
    Dim txtFile As Object
    Const ForReading As Integer = 1
    Const ForWriting As Integer = 2
    Const ForAppending As Integer = 8
    Const Unicode As Integer = -1
    Const Ascii As Integer = 0

    With CreateObject("Scripting.FileSystemObject")
            Set txtFile = .CreateTextFile(pstrTextFile, True)
        With txtFile
            .Write pstrContent & IIf(pbolAddLineFeed, vbCrLf, vbNullString)
            .Close
        End With
    End With

End Function

replace your Macro with RunCode macro:

RunCode
Function Name fncAppendToTextFile("\\Client\C$\Users\ActiveUser.txt", [Form]![LoginUserName], True)

 

[travel4u]

Thanks Arnelgp,

I copied that VBA code to Access - it seems to run okay as no errors.
I added RunCode in to Macro that runs after user is granted to open application:
Pic #1:

Something is not correct as Macro stops with following infos:
Pic #2:

Note: In Europe we use ; instead of , in above.

 

[travel4u]

Forgot this: I saved that VBA code with name modSaveActiveUser:

 

[arnelgp]

do you also use semicolon in vba in replacement for comma (,)?

 

[travel4u]

Hey. I assume it is comma (,) in VBA as I tried to change then to semicolon ( i got this errot:

 

[travel4u]

I changed semicolons to commas in this:

Now I got this error:

Clicking DEBUG it shows this:

 

[arnelgp]

how about if you change the semicolon to comma on the macro?

EDIT!!
the message shows that you have No Write Privelege on that Path.

 

[travel4u]

how about if you change the semicolon to comma on the macro?

See my earlier post minute ago

 

[arnelgp]

are you sure about the Path:
\\Client\C$\Users

$ means the folder is hidden?

 

[travel4u]

how about if you change the semicolon to comma on the macro?

EDIT!!
the message shows that you have No Write Privelege on that Path.

Came to my mid as well, as C:-drive is under "Admin" (even I am Admin in this lap-top). Should I have to change that ActiveUser.txt file to D: drive?

 

[travel4u]

are you sure about the Path:
\\Client\C$\Users

$ means the folder is hidden?

When I link that file on Access it shows path to that file like this:

 

[travel4u]

THANK YOU!
I just changed write permission for that C:\\Users folder and NOW this works!