Variable in a Message Box

Ok I have the following code:

Option Compare Database
Option Explicit
Private lngFile As Long
Private Sub Form_Load()
If Dir("D:\Data\Lockout.txt") <> "" Then
  MsgBox "Sorry, someone else is using this file. Try again later."
  Application.Quit
End If
lngFile = FreeFile
Open "D:\Data\Lockout.txt" For Output As lngFile
End Sub

Private Sub Form_Unload(Cancel As Integer)
Close lngFile
Kill "D:\Data\Lockout.txt"
End Sub

In the MsgBox how can I put MsgBox "Sorry (lookup userid field from usertable who is using the file) is using the file"

For exampl, say John is using the file and Mike want to open....

Mike should get the message "Sorry John is using the file, Try again later"
michaeljusino1017Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dmitryz6Commented:
MsgBox "Sorry " & dlookup("fieldname","tablename") & " is using the file"
mbizupCommented:
Are you using CurrentUser, Environ(UserName) or something else to lookup the user ID?

try something like this:
MsgBox "Sorry, " & dlookup("UserID", "tblUsers", "UserName = '" & Environ(UserName) & "'") & " is using this file. Try again later."
mbizupCommented:
Dmitry got there first.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

michaeljusino1017Author Commented:
i'm using Environ(Username) but I dont want to display my name, I want to display the name of the person who is currently using the file.....

Also, I noticed:

lngFile = FreeFile
Open "D:\Data\Lockout.txt" For Output As lngFile

doesn't actually open a file or put one in the directory...is there another way to call a function to actually create the file Locout.txt and save it in D:\Data\
peter57rCommented:
Hi michaeljusino1017,
Are you hoping someone is going to tell you how to find out who has a particular file open?
This info is obtained using the API NetFileEnum, but the user would have to be a Windows System/Network Administrator to be able to use that API.

Pete
michaeljusino1017Author Commented:
no I am just trying to lock the file, and keep it from being opened by another user.  It seem opening it exclusively still allows other users to open the file.  What I am trying to do is create a file the minute the MS Access File is opened and then locking the MS Access file by checking to see if there is a text file inthe directory.  The minute someome closes the MS Access file the text file is deleted and the MS Access file is accesible to anyone on the network

At the same time, I want to display the user that is currently using the file so that if someone really needs to get in to the file badly, say an adminstrator, they can call that person up and say, close the file
dmitryz6Commented:
In this case I would create lock file using Environ(UserName)

Option Compare Database
Option Explicit
Private lngFile As Long
Private Sub Form_Load()
If Dir("D:\Data\*") <> "" Then
  MsgBox "Sorry " &  Left(Dir("D:\Data\*"),InStr(Dir("D:\Data\*"),"-")-1) & " is using the file"
  Application.Quit
End If
lngFile = FreeFile
Open "D:\Data\" & Environ(UserName) & "-Lockout.txt" For Output As lngFile  '<-Create with current user name  file
End Sub

Private Sub Form_Unload(Cancel As Integer)
Close lngFile
Kill "D:\Data\Lockout.txt"
End Sub

In the MsgBox how can I put MsgBox "Sorry (lookup userid field from usertable who is using the file) is using the file"
dmitryz6Commented:
for  got about Kill statement

In this case I would create lock file using Environ(UserName)

Option Compare Database
Option Explicit
Private lngFile As Long
Private Sub Form_Load()
If Dir("D:\Data\*") <> "" Then
  MsgBox "Sorry " &  Left(Dir("D:\Data\*"),InStr(Dir("D:\Data\*"),"-")-1) & " is using the file"
  Application.Quit
End If
lngFile = FreeFile
Open "D:\Data\" & Environ(UserName) & "-Lockout.txt" For Output As lngFile  '<-Create with current user name  file
End Sub

Private Sub Form_Unload(Cancel As Integer)
Close lngFile
Kill "D:\Data\*"                       '<-will kill all files in this directory
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.