?
Solved

Variable in a Message Box

Posted on 2006-04-18
9
Medium Priority
?
250 Views
Last Modified: 2008-02-26
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"
0
Comment
Question by:michaeljusino1017
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16479704
MsgBox "Sorry " & dlookup("fieldname","tablename") & " is using the file"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16479737
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."
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16479775
Dmitry got there first.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:michaeljusino1017
ID: 16479814
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\
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16479839
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
0
 

Author Comment

by:michaeljusino1017
ID: 16480837
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
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 16484018
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"
0
 
LVL 19

Accepted Solution

by:
dmitryz6 earned 2000 total points
ID: 16484032
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
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question