Another user edited this record... (1 Viewer)

ellenr · Oct 20, 2014

My Autoexec macro does several things, among which it adds 1 to the number of users currently in the db (mysql backend file) and opens a hidden form and the main form. This main form displays the number of current users. Originally the close button on this main form subtracted 1 from the number of active users before closing the app. Some users were leaving the app by clicking the "X" rather than my big "Exit" button so that the number of current users was never decremented. Online suggestions were to open a hidden form and attach whatever I wanted to do in its onUnload. So now, I get the error msg "The data has been changed. Another user edited this record... " and it doesn't do the subtraction.

I can't figure out an order of events to make this work without throwing the error.

AOB · Oct 20, 2014

How is the number of users stored? Is it some kind of public variable or a record in a table?

Why increment / decrement an integer / long variable at all?

Why not maintain a table of users and 'tick' people as they join / leave, then just query the table (DCount) to get the number of active users? Then, not only do you know how many users are active, you also know which ones?

Alternatively, disable the 'X' button so they have to exit the application "your way"?

ellenr · Oct 20, 2014

The number of users is just a field in a table in a mysql db. I don't need to know who is currently in the db, just how many. Finally, can you tell me know to disable the app's "X" - thought you couldn't do that.

AOB · Oct 20, 2014

In the Form properties, under Format, you can set Close Button to Yes or No...

So when you open your hidden form, you update the record in that table by incrementing / decrementing accordingly? And then you have a bound control on your main form that shows that number? I presume you only have one record in that table then?

ellenr · Oct 20, 2014

I understand about "Close" yes or no in a form's format. I am talking about the app's close "X". Some users click the "X" in the top right corner to end the program instead of my "Exit" button. I am just trying to trap that.

"Yes" to your last two questions.

AOB · Oct 20, 2014

If you add your increment / decrement procedure to the form's UnLoad event, then it should still fire if they close via the X - assuming the form is open at this point? As the form has to unload before the application can quit? Hence, having a hidden form automatically open at startup, and using its UnLoad event, should circumvent this problem?

Alternatively, if users don't need the ribbons or navigation pane, you could set up your AutoExec to hide them, hence all you can see is the form (and thus you have control over the only X button available, i.e. that of the form itself and not the parent application window)

It might help to see the code behind your UnLoad event for your hidden form? And also the Load event while you're at it?

ellenr · Oct 20, 2014

Solved! Thanks to AOB--his question made me realize my mistake. I moved the increment from the Autoexec to the OnLoad event of the hidden form, so both the increment and decrement were connected there. Now all works smoothly, whether my Exit button is clicked or the "X" in the corner.

AOB · Oct 20, 2014

Boom! Nice one, well done

gemma-the-husky · Oct 20, 2014

I bet if your database crashed, you would leave a phantom "lock count" in place. In this sort of thing you always need a "reset" scenario to recover the situation.

AOB · Oct 20, 2014

Agreed Dave - that's why my original suggestion was to maintain a user / connection table and have a startup / close process that sets some boolean field to True or False. That way, if you question the number, you can trace the problem to a specific connection. You may not *want* or *need* to know specifically who is connected, but it can be useful down the line when you are trying to track a problem.

If you're getting a '2' on your form, but everybody insists they are out of the database, how do you know whose connection is causing the problem?

Admittedly it's an extra bit of work but I find it's very useful information to have, even if you may only use it once in a blue moon.

ellenr · Oct 20, 2014

hmm...
Guess I will try to put in a user's in and out table--don't know if it isn't overkill for this little program, but at least it will be a good "learning experience."

Does anyone know of available vba to do this?

ellenr · Oct 20, 2014

vba code:

Code:

Private Sub Form_Load()
On Error GoTo Form_Load_Err
   
    Dim myQry As String
    Dim sUserName As String
    Dim sUserComputer As String
    
    sUserName = Environ$("username")
    sUserComputer = Environ$("computername")
    
    strSQL = "SELECT * FROM UserList WHERE Username = " & sUserName & " and Where UserComputer = " & sUserComputer & " "
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If (rst.RecordCount = 0) Then
      myQry = "INSERT INTO UserList (UserName, UserComputer, OnOrOff) VALUES ('" & sUserName & "','" & sUserComputer & "', -1)"
    CurrentDb.Execute myQry
End If
Me!SaveID.Value = ID

Form_Load_Exit:
    Exit Sub

Form_Load_Err:
    MsgBox Error$
    Resume Form_Load_Exit

End Sub

This gives me error: Syntax error (missing operator) in query expression 'Username = Ellen and Where UserComputer = ApplePC'. I removed the second Where and the error says "Too few parameters. Expected 2."
What am I doing wrong? (Confusing 's and "s)!

AOB · Oct 21, 2014

You need to qualify your string variables with quotes.

Also, you only need to use WHERE once (and use logical operators i.e. AND / OR / NOT) for combinations

Try :

Code:

strSQL = "SELECT * FROM UserList " & _
         "WHERE Username = " [COLOR=red][COLOR=black]&[/COLOR] Chr(34) [COLOR=black]& sUserName  &[/COLOR] Chr(34) [/COLOR][COLOR=black]& _[/COLOR]
         " AND UserComputer = " & [COLOR=red]Chr(34)[/COLOR] & sUserComputer  & [COLOR=red]Chr(34)[/COLOR]
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Chr(34) is the ASCII character for the double-quote (") which I prefer using over the single-quote (') in situations like these as names can have apostrophes in them which starts to get messy (my own surname is O'Brien...) as the code thinks the apostrophe marks the end of the text string rather than being part of it.

Try using Debug.Print to see your constructed SQL in the Immediate Window, it's a lot easier than using a watch

ellenr · Oct 21, 2014

Wonderful! Thank you. It is working!

Another user edited this record... (1 Viewer)

ellenr

Registered User.

AOB

Registered User.

ellenr

Registered User.

AOB

Registered User.

ellenr

Registered User.

AOB

Registered User.

ellenr

Registered User.

AOB

Registered User.

gemma-the-husky

Super Moderator

AOB

Registered User.

ellenr

Registered User.

ellenr

Registered User.

AOB

Registered User.

ellenr

Registered User.

Similar threads

Users who are viewing this thread