Current User List Form

[GendoPose]

Hi all, thanks for the help so far.

My database loads up into a login form, a user enters their username and password and then the navigation form opens with certain things enabled or disabled based on the users access level.

What I'd like to do is have the login form tick a "Logged In" box, based on that user's record. I've tried to make this a bound box and tick it when the user logs in, but it just updates this for the first record in the user list, not the record for that user.

After that, I'd like the box for that user to be unticked when they click the logout button from the main page.

Any ideas?

Thanks guys

 

[Walshie]

Hi,

I'm guessing that you have a table with all the users in?

Here's a possible solution. Add a column in the table for the status to go in e.g. "Status"

When the user logs in add the following line the the login button:

docmd.runSQL "UPDATE tablename SET Status = 'Logged in' WHERE Username = 'username'

and logs our:

docmd.runSQL "UPDATE tablename SET Status = '' WHERE Username = 'username'

Then you build a query to show users currently logged in?

Here's some help with UPDATE http://www.w3schools.com/SQl/sql_update.asp

Hope this helps

Cheers
Chris

 

[GendoPose]

I've tried your suggestion, and this is my code;

DoCmd.RunSQL "UPDATE tblUser SET [Logged In] = 'Yes' WHERE UserID = '[cboUser.Column(0])'"

Unfortunately it doesn't update the field in my table, whether it's a check box or a yes/no field as I originally had. Also there are DoCmd.OpenForm and Me.Visble lines, and no matter if I put this code before or in between it either opens the Navigation and doesn't minimise the login form, or doesn't do anything at all. Putting the code after these lines allows the form to log you in and open the Navigation, but the table doesn't get updated.

 

[Walshie]

Sorry I've not got access to a computer at the min (just finished work and on the way home).

My guess is change this

 WHERE UserID = '[cboUser.Column(0])'"

To

 WHERE UserID = '" & cboUser.Column(0) & "'"

Let me know if no joy and ill take a look at first chance I get

Cheers
Chris

 

[Walshie]

Please note edit above, and also change = 'yes' to = 1

 

[GendoPose]

No luck with it yet =[

I'm leaving work now as well so I'll have to check back tomorrow, but currently the whole code for the login button is this;

Private Sub Login_Click()
'Check that User is selected
    If IsNull(Me.cboUser) Then
        MsgBox "You need to select a user!", vbCritical
        Me.cboUser.SetFocus
    Else
    'Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then
        'Check if password needs to be reset
        If Me.cboUser.Column(3) = True Then
            MsgBox "Your password needs to be updated."
            DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.cboUser
        End If
        DoCmd.OpenForm "Navigation", acNormal, , , acFormReadOnly
        Me.Visible = False
        DoCmd.RunSQL "UPDATE tblUser SET tblUser.[Logged In] = 'True' WHERE tblUser.UserID = '" & cboUser.Column(0) & "'"
    Else
        MsgBox "Password does not match, please re-enter!", vboOkOnly
        Me.txtPassword = Null
        Me.txtPassword.SetFocus
    End If
End If
End Sub

Thanks

 

[Walshie]

Hi,

I've just tested and the below should work fine:

I've changed this:

DoCmd.RunSQL "UPDATE tblUser SET tblUser.[Logged In] = 'True' WHERE tblUser.UserID = '" & cboUser.Column(0) & "'"

to this:

DoCmd.RunSQL "UPDATE tblUser SET tblUser.[Logged In] = 1 WHERE tblUser.UserID = '" & me.cboUser.Column(0) & "'"

You could also use "= TRUE" instead of "=1". The version you posted didn't work as the TRUE was in quotes.

Let me know how you get on

Cheers
Chris

 

[GendoPose]

Nope, that still hasn't got it for some reason. I've tried both TRUE and 1 with no avail and I've tried moving code the any of the places it can be and still it doesn't work

 

[Walshie]

Hi,

Is the username a text field or is it a user number? I'm assuming the username is in text in the above example.

What I would try and do is target just one entry for the minute to try and to locate the problem.

So i'd change

WHERE tblUser.UserID = '" & me.cboUser.Column(0) & "'"

to

WHERE tblUser.UserID = 'name_of_a_user_in_your_table_here'"

If that works then you know its the me.cboUser.Column(0) that isn't passing through the value.

Also check the positioning of your code, currently you are opening the Nav form and then running the above code, move this line to before the form is opened, that should get it.

I've amended this and here it is:

Private Sub Login_Click()
'Check that User is selected
    If IsNull(Me.cboUser) Then
        MsgBox "You need to select a user!", vbCritical
        Me.cboUser.SetFocus
    Else
    'Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then
        'Check if password needs to be reset
        If Me.cboUser.Column(3) = True Then
            MsgBox "Your password needs to be updated."
            DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.cboUser
        End If
        DoCmd.RunSQL "UPDATE tblUser SET tblUser.[Logged In] = 1 WHERE tblUser.UserID = '" & cboUser.Column(0) & "'"
        DoCmd.OpenForm "Navigation", acNormal, , , acFormReadOnly
        Me.Visible = False

    Else
        MsgBox "Password does not match, please re-enter!", vboOkOnly
        Me.txtPassword = Null
        Me.txtPassword.SetFocus
    End If
End If
End Sub

Cheers
Chris

 

[GendoPose]

Ok so I changed it to

WHERE tblUser.FName = 'someone's first name'"

and that works fine. I tried changing it to a specific UserID and that didn't work (the Login form wouldn't minimise and the Nav wouldn't open). I then tried it as

'" & Me.cboUser.Column(1) & "'"

and that also didn't work (Column 1 is the FName column)

 

[Walshie]

That looks like were getting somewhere now!

So it appears that cboUser.column(1) isn’t passing the value through.

I think this is because of the position of the code, you are opening the “Navigation” form and then trying to pull from cboUser.column(1), which doesn’t exist on the “Navigation” form.

You either need to move the docmd.runSQL code before you open the new form or change cboUser.column(1) to forms!frmLogin!cboUser.column(1) Replacing "frmLogin" with the name of your form where cboUser is located.

Hope this helps!

Cheers
Chris

 

[GendoPose]

Yeah I hope so, it's still not having it though.
Currently I have this;

DoCmd.RunSQL "UPDATE tblUser SET tblUser.[Logged In] = TRUE WHERE tblUser.UserID = '" & Forms!frmLogin!cboUser.Column(0) & "'"

after the open navigation code, and nothing is updating. If I move it anywhere else, the login form breaks.

 

[GendoPose]

Ok now I've got it vaguely working, I managed to get it working based on the FName field rather than the UserID. It's not ideal but it's a start. The UserID field is an autonumber, but surely that shouldn't make a difference? Any ideas?

Thanks for all the help so far!

 

[Walshie]

Add this directly before the docmd.runsql line:

msgbox Forms!frmLogin!cboUser.Column(0)

A message box should pop up with the value in it.

Does it match the name in the field from your table?

If not please repost your code how it is now

Cheers
Chris

 

[GendoPose]

Yeah the ID matches the ID of the Username.

 

[Walshie]

That's confuses things!

This is a long shot but are you running the code with the table open, if you make sure the user is unticked in the table, close the table then run the login, then open the table again is the box now ticked?

 

[Walshie]

Hi sorry my PC was playing up and I missed your post regarding the UserID been a number.

It does make a difference

For numbers you need to remove the '

So change

DoCmd.RunSQL "UPDATE tblUser SET tblUser.[Logged In] = True WHERE tblUser.UserID = '" & cboUser.Column(0) & "'"

to

DoCmd.RunSQL "UPDATE tblUser SET tblUser.[Logged In] = True WHERE tblUser.UserID = " & cboUser.Column(0)

Should do the trick!

 

[GendoPose]

Yeah that's got it! Thanks a lot for that!

 

[Walshie]

No problem! Good luck with your project!