Detect an open form in multi-user database

[mdmckin]

I'd like to detect if a form is open, I want only one person to have
this form at a time. The form uses temporary data to fill in a few
fields on reports and gets cleared after each use.
The following code works, but only within the same instance of Access,
if I have the form open on one client and then try to open up the same
form within the same database from a different client, it doesn't
work.

In a module:

Public Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function


And in a test form with a command button:

Private Sub Command0_Click()
fIsLoaded "JobPacket"
Dim strMsg As String
strMsg = "Form is already opened by another user,"
strMsg = strMsg & vbCrLf & vbCrLf & "Please try again later."
If fIsLoaded("fJobPacket") = True Then
MsgBox (strMsg)
Else
DoCmd.OpenForm "fJobPacket"
End If
End Sub


What would need to be done differently to make this work in a multi-
user environment?

Thanks!
Mark

 

[Steve]

Your multi-user application should be a split database with the backend on
the server and a separate frontend on each user's computer linkrd to the
backend over the network. This would eliminate your problem. Having a single
database on a network with multiusers accessing the database is a disater
waiting to happen. One of these days you may find your database corrupted
with no way to recover any of the data.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Klatuu]

You can't reliably do that; however, what I do in this situation is to make
the table a local table and run the report from that. That way, multiple
users could actually run the report at the same time. This also
prevents....(oh well, at least reduces) user frustration. Here is an
example. In this example, rather than deleting the data and letting the
table remain, I create and delete the table after each report. And why, you
ask, would you do such a thing....?

That's the way the boss wanted it.

Private Function BuildOpStatsChart() As Boolean
Dim rstPct As DAO.Recordset 'Recordset for query qselOpStatsPct
Dim rstChart As DAO.Recordset 'Recordset for tblOPStatsChart - Data for
report
Dim dbf As DAO.Database
Dim qdf As QueryDef
Dim lngDays As Long 'Counter to create the 31 days
Dim tdf As DAO.TableDef
Dim tdfNew As DAO.TableDef
Dim strPeriod As String 'Convert Period to dislpay format
Const conPreBillTable As String = "tblOPStatsChart" 'Table Name


On Error GoTo BuildOpStatsChart_Error

BuildOpStatsChart = True
Set dbf = CurrentDb

'Make sure the old table was deleted.
If TableExists(conPreBillTable) Then
dbf.TableDefs.Delete conPreBillTable
End If

'Create the new table
Set tdfNew = dbf.CreateTableDef(conPreBillTable)

With tdfNew
.Fields.Append .CreateField("DOM", dbLong)
.Fields.Append .CreateField("DAY_PCT", dbDouble)
.Fields.Append .CreateField("PERIOD", dbText, 8)
End With
dbf.TableDefs.Append tdfNew

'Set up the parameters for the query
Set qdf = dbf.QueryDefs("qselOpStatsPct")

'Get the query and be sure it has data
Set rstPct = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
With rstPct
If .RecordCount = 0 Then
MsgBox "No Data Found for Report", vbExclamation, "Data Error"
.Close
BuildOpStatsChart = False
GoTo BuildOpStatsChart_Exit
Else
.MoveLast
.MoveFirst
End If
End With

'Prepare the Chart Data
Set rstChart = dbf.OpenRecordset(conPreBillTable, dbOpenDynaset)

'Transform the data from the query to the table
Do While Not rstPct.EOF

If rstPct!txtPeriodID = "YTD" Then
strPeriod = "YTD"
Else
strPeriod = Format(DateSerial(Left(rstPct!txtPeriodID, 4), _
Right(rstPct!txtPeriodID, 2), 1), "mmm yyyy")
End If
'Remove this line to allow YTD to be included in the report
If rstPct!txtPeriodID <> "YTD" Then
With rstChart
For lngDays = 1 To 31
.AddNew
![Period] = strPeriod
![dom] = lngDays
![DAY_PCT] = rstPct.Fields(Format(lngDays, "00"))
.Update
Next lngDays
End With
'Remove this line to allow YTD to be included in the report
End If
rstPct.MoveNext
Loop

'Destroy Objects
rstPct.Close
rstChart.Close
Set rstPct = Nothing
Set rstChart = Nothing
Set tdfNew = Nothing
Set dbf = Nothing

BuildOpStatsChart_Exit:
On Error Resume Next


Exit Function

BuildOpStatsChart_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure BuildOpStatsChart of Module modOpStatsReports"
GoTo BuildOpStatsChart_Exit

End Function