Public Variable

[LeAnn]

I have an Access 2003 *.mde database with front end loaded on local PCs.
There are several tasks that I only want 1 person to perform at a time. I
set public variables and when a user clicks a task's button, it sets the
variable to TRUE. If the variable is TRUE when the next user clicks on it
exit the Sub. It isn't working. I've tried having users log onto a single
network copy and that didn't work either. I've tried setting text boxes to
a value and evaluate that but nothing is working. I'm sure this is something
simple that I'm overlooking. Any help would be appreciated.

Here a snippet of my code........

Sub ImportInventory
On Error GoTo ErrorHandler

If bolImportLOC = True Then
MsgBox "Someone else is performing this task, please wait.",
vbOKOnly + vbCritical, "Alert"
Exit Sub
End If

bolImportLOC = True

ImportInventoryLocations "City"

Exit_Sub:
bolImportLOC = False
Exit Sub

ErrorHandler:
MsgBox "Error #" & Err.Number & " - Description: " &
Err.Description, vbOKOnly + vbExclamation, "Error"
GoTo Exit_Sub

Thanks
LeAnn

 

[Graham Mandeno]

Hi LeAnn

A Public variable is only "public" within the scope of the particular Access
process, so it is public to other modules in the same project, but not to
other instances of Access, even if they are opened by same user on the same
machine.

The best way to open a Recordset based on a table in your shared back-end
database and deny read access to the recordset

Set rsLock = CurrentDb.OpenRecordset ("SomeTable", , dbDenyRead )
....
rsLock.Close

Anyone else attempting to do the same, before the recordset is closed, will
raise an error that can be handled by displaying an appropriate message.

 

[LeAnn]

Thanks for the quick reply. I have tried your suggestion but it doesn't
raise an error for the second user. It goes on and tries to open an excel
file that the first user has open. This raises an error that I was able to
trap but it still prompts the user to open the file read only or notify. I
want to suppress that message as well. Here's part of my code. What am I
stumbling on now? Can I just suppress the notify/read-only message and be
done with it?
Thanks
LeAnn

'For each excel file in the directory do the following:
If strLocation = "City1" Then
strLocDir = BDLOC
strArchDir = BDARCH
strDupDir = BDDUPS
strTable = "tblCity1Locations"
strLogHeader = "City1 Locations// Processed By " & strUserName & ":"
ElseIf strLocation = "POC" Then
strLocDir = POCDIR
strArchDir = POCARCH
strDupDir = POCDUPS
strTable = "tblPOCs"
strLogHeader = "POC Locations// Processed By " & strUserName & ":"
ElseIf strLocation = "City2" Then
strLocDir = LOCDIR
strArchDir = LOCADIR
strDupDir = DUPDIR
strTable = "tblCity2Locations"
strLogHeader = "City 2 Locations // Processed By " & strUserName & ":"
End If

Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset(strTable, , dbDenyRead)

strFile = Dir(strLocDir)
If strFile = "" Then
If IsLoaded("frmWait") Then DoCmd.Close acForm, "frmWait", acSaveNo
MsgBox "There are no files in the directory.", vbOKOnly + vbExclamation,
"No Files"
Exit Sub
End If
........................

Hi LeAnn

A Public variable is only "public" within the scope of the particular Access
process, so it is public to other modules in the same project, but not to
other instances of Access, even if they are opened by same user on the same
machine.

The best way to open a Recordset based on a table in your shared back-end
database and deny read access to the recordset

Set rsLock = CurrentDb.OpenRecordset ("SomeTable", , dbDenyRead )
....
rsLock.Close

Anyone else attempting to do the same, before the recordset is closed, will
raise an error that can be handled by displaying an appropriate message.


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have an Access 2003 *.mde database with front end loaded on local PCs.
There are several tasks that I only want 1 person to perform at a time. I
set public variables and when a user clicks a task's button, it sets the
variable to TRUE. If the variable is TRUE when the next user clicks on it
exit the Sub. It isn't working. I've tried having users log onto a
single
network copy and that didn't work either. I've tried setting text boxes
to
a value and evaluate that but nothing is working. I'm sure this is
something
simple that I'm overlooking. Any help would be appreciated.

Here a snippet of my code........

Sub ImportInventory
On Error GoTo ErrorHandler

If bolImportLOC = True Then
MsgBox "Someone else is performing this task, please wait.",
vbOKOnly + vbCritical, "Alert"
Exit Sub
End If

bolImportLOC = True

ImportInventoryLocations "City"

Exit_Sub:
bolImportLOC = False
Exit Sub

ErrorHandler:
MsgBox "Error #" & Err.Number & " - Description: " &
Err.Description, vbOKOnly + vbExclamation, "Error"
GoTo Exit_Sub

Thanks
LeAnn

 

[Graham Mandeno]

Hi LeAnn

I'm sorry - my memory must be failing me

The dbDenyRead option only works for table-type recordsets (i.e. local
tables, not linked tables).

What you will need to do is open the recordset and then issue the Edit
method to lock the first record. Anyone else attempting to do the same will
raise Error 3218. Then, to release the lock, issue a CancelUpdate and Close
the recordset.

Set db = CurrentDb() ' <<< this line is unnecessary
Set rst = CurrentDb.OpenRecordset(strTable) ' don't need dbDenyRead

rst.Edit
' lock is now effective

....
rst.CancelUpdate
' lock is now released
....
rst.Close

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the quick reply. I have tried your suggestion but it doesn't
raise an error for the second user. It goes on and tries to open an excel
file that the first user has open. This raises an error that I was able
to
trap but it still prompts the user to open the file read only or notify.
I
want to suppress that message as well. Here's part of my code. What am I
stumbling on now? Can I just suppress the notify/read-only message and be
done with it?
Thanks
LeAnn

'For each excel file in the directory do the following:
If strLocation = "City1" Then
strLocDir = BDLOC
strArchDir = BDARCH
strDupDir = BDDUPS
strTable = "tblCity1Locations"
strLogHeader = "City1 Locations// Processed By " & strUserName & ":"
ElseIf strLocation = "POC" Then
strLocDir = POCDIR
strArchDir = POCARCH
strDupDir = POCDUPS
strTable = "tblPOCs"
strLogHeader = "POC Locations// Processed By " & strUserName & ":"
ElseIf strLocation = "City2" Then
strLocDir = LOCDIR
strArchDir = LOCADIR
strDupDir = DUPDIR
strTable = "tblCity2Locations"
strLogHeader = "City 2 Locations // Processed By " & strUserName & ":"
End If

Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset(strTable, , dbDenyRead)

strFile = Dir(strLocDir)
If strFile = "" Then
If IsLoaded("frmWait") Then DoCmd.Close acForm, "frmWait", acSaveNo
MsgBox "There are no files in the directory.", vbOKOnly +
vbExclamation,
"No Files"
Exit Sub
End If
.......................

Hi LeAnn

A Public variable is only "public" within the scope of the particular
Access
process, so it is public to other modules in the same project, but not to
other instances of Access, even if they are opened by same user on the
same
machine.

The best way to open a Recordset based on a table in your shared back-end
database and deny read access to the recordset

Set rsLock = CurrentDb.OpenRecordset ("SomeTable", , dbDenyRead )
....
rsLock.Close

Anyone else attempting to do the same, before the recordset is closed,
will
raise an error that can be handled by displaying an appropriate message.


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have an Access 2003 *.mde database with front end loaded on local PCs.
There are several tasks that I only want 1 person to perform at a time.
I
set public variables and when a user clicks a task's button, it sets
the
variable to TRUE. If the variable is TRUE when the next user clicks on
it
exit the Sub. It isn't working. I've tried having users log onto a
single
network copy and that didn't work either. I've tried setting text
boxes
to
a value and evaluate that but nothing is working. I'm sure this is
something
simple that I'm overlooking. Any help would be appreciated.

Here a snippet of my code........

Sub ImportInventory
On Error GoTo ErrorHandler

If bolImportLOC = True Then
MsgBox "Someone else is performing this task, please wait.",
vbOKOnly + vbCritical, "Alert"
Exit Sub
End If

bolImportLOC = True

ImportInventoryLocations "City"

Exit_Sub:
bolImportLOC = False
Exit Sub

ErrorHandler:
MsgBox "Error #" & Err.Number & " - Description: " &
Err.Description, vbOKOnly + vbExclamation, "Error"
GoTo Exit_Sub

Thanks
LeAnn