Check if database is in use, where am i going wrong!

[llaadd]

hi,

I have a system set up which would get messed up if more then 1 person uses it, but didn't know of any pre-existing ways to do it, so, I am doing it by making a login page where the user selects who they are, this and the date and time of login are stored, and when the user closes access, the date out time is stored.

If someone else tried to open access, it checks to see if the last entry in the access log has a logout time entered, if not, they are not allowed to use it. I do understand this could potentially cause an issue if the database is not closed properly, but I will see how it goes.

to implement this i wrote the following but it produces an error:

Private Sub Form_Load()
Dim logged_out As String
Dim last_id As Integer
Dim current_user As String

last_id = DMax("[ID]", "Access Log")
logged_out = DMax("[Date_Logout]", "Access Log", "[Access Log].[ID] = last_id")

If IsNull(logged_out) Then
    current_user = DMax("[User]", "Access Log", "[Access Log].[ID] = last_id")
    Select Case MsgBox(current_user & " is currently logged in." _
                        & vbCr & vbCr & "Please try again once " & current_user & " has logged off", _
                        vbOKOnly + vbExclamation, _
                        "RMA System In Use")
            Case vbOK
                'close access down!
    End Select
End If
End Sub

So i broke it down into the following to find the source of the error:

Private Sub Form_Load()
Dim logged_out As String
Dim last_id As Double
Dim current_user As String

last_id = DMax("[ID]", "Access Log")

Select Case MsgBox(last_id, vbOKOnly + vbExclamation, "test")
        Case vbOK
        'Do Nothing
End Select

logged_out = DMax("[Date_Logout]", "Access Log", "[Access Log].[ID] = last_id")

Select Case MsgBox(last_id, vbOKOnly + vbExclamation, "test")
        Case vbOK
            Select Case MsgBox(logged_out, vbOKOnly + vbExclamation, "test")
                     Case vbOK
                         'Do Nothing
            End Select
End Select
End Sub

The first part of this worked, and it displays a message box with the number 1 in it, as there is only 1 record currently, but then it fails on the following line:

logged_out = DMax("[Date_Logout]", "Access Log", "[Access Log].[ID] = last_id")

The error says:

Run-tine error '2471
The expression you entered as a query parameter produced this error: 'last_id'

any advise?

thanks in advance

 

[pbaldy]

You have to concatenate the variable value, like so:

http://www.mvps.org/access/general/gen0018.htm

 

[Guus2005]

What is last_id? if that is the name of a column then it might work. I mean, it shouldn't produce any error then. What you want it to do is probably

logged_out = DMax("[Date_Logout]", "Access Log", "[Access Log].[ID] = " & last_id)

If the users computer crashes, while in the program, the user isn't properly logged out and the logout time is not recorded. You might want to consider using the contents
of the Access locking file.

search the net for function: ShowUserRoster

HTH

 

[llaadd]

What is last_id? if that is the name of a column then it might work. I mean, it shouldn't produce any error then. What you want it to do is probably

logged_out = DMax("[Date_Logout]", "Access Log", "[Access Log].[ID] = " & last_id)

If the users computer crashes, while in the program, the user isn't properly logged out and the logout time is not recorded. You might want to consider using the contents
of the Access locking file.

search the net for function: ShowUserRoster

HTH

Thanks a lot, I got my code working, I did like this:

Dim last_id As Long
Dim current_user As String

last_id = DMax("[ID]", "Access Log")

If IsNull(DMax("[Date_Logout]", "Access Log", "[Access Log].[ID] =" & last_id)) Then
    current_user = DMax("[User]", "Access Log", "[Access Log].[ID] =" & last_id)
    Select Case MsgBox(current_user & " is currently logged in." _
                        & vbCr & vbCr & "Please try again once " & current_user & " has logged off", _
                        vbOKOnly + vbExclamation, _
                        "RMA System In Use")
            Case vbOK
                'close access down!
    End Select
End If
End Sub

I will also look into ShowUserRoster as you suggested, thanks a lot!