Trouble with a Delete Query

[RossWindows]

I have a table called active users which has an autonumber field called Session and the table also stores the username and date.

The record is appended to the table when the user opens the db.
That works fine.

What I need now, is when the user closes the main form (which causes an application.quit) I want the forms on close event to run a delete query to remove the user from the ActiveUsers table.

This is what I have so far, but it seems not to work:
I think one problem is incorrect syntax for referring to the Public "Session" variable...

    DoCmd.RunSQL "DELETE ActiveUsers.*, ActiveUsers.Session" & _
        " FROM ActiveUsers" & _
        " WHERE (((ActiveUsers.Session)=[Session]));"

 

[pbaldy]

If you're saying that Session is a public VBA variable, you can't refer directly to it in a query. You can create a public function that did nothing but return the value of that variable, and use that:

WHERE ActiveUsers.Session= FunctionName()

 

[RossWindows]

AH! I didnt know that, but it makes sense.

Thanks!

 

[pbaldy]

No problemo; post back if you get stuck.

 

[RossWindows]

One more question:

If the user opened multiple instances af Access and the db on their machine, would the "Session" public variable be unique for each instance, or would the public variable "bleed" over to the other sessions as well?

 

[boblarson]

But you should be able to refer to it without a function because this query SQL is in code:

    DoCmd.RunSQL "DELETE ActiveUsers.*, ActiveUsers.Session" & _
        " FROM ActiveUsers" & _
        " WHERE (((ActiveUsers.Session)=[COLOR="Red"]" & Session & "[/COLOR]));"

or if text

    DoCmd.RunSQL "DELETE ActiveUsers.*, ActiveUsers.Session" & _
        " FROM ActiveUsers" & _
        " WHERE (((ActiveUsers.Session)=[COLOR="Red"]'" & Session & "'[/COLOR]));"

 

[pbaldy]

Bob's point is a good one. It didn't dawn on me you were running the query from code, though it is quite plainly there.

 

[RossWindows]

Thanks Bob, " & Session & " worked for me because Session is a long integer.
I tried '" & Session & '"" earlier but of course it didn't work because the variable is not text... That's what my problem was.

DoCmd.RunSQL "DELETE ActiveUsers.*, ActiveUsers.Session" & _
        " FROM ActiveUsers" & _
        " WHERE (((ActiveUsers.Session)=[COLOR=red]" & Session & "[/COLOR]));"

 

[RossWindows]

OK, I have another problem.... very weird but probably very simple.

I am now fine-tuning the Append query that creates a record for each instance of the db that the user opens.
i.e. If the user opens two instances of the db, then two records get created, each with their own unique session id

The query works great the first time it runs (it creates one record)
But, the second time it runs is when the problems start (see attachment)

Here is the code in my AutoExec module:

Option Compare Database
Public Session As Long
Option Explicit
 
    Dim rs As DAO.Recordset
    Dim strSql As String
 
        DoCmd.RunSQL "INSERT INTO ActiveUsers ( User )" & _
            " SELECT fOSUserName()" & _
            " FROM ActiveUsers;"
 
        strSql = "SELECT Max(ActiveUsers.Session) AS MaxOfSession" & _
            " FROM ActiveUsers" & _
            " WHERE (ActiveUsers.User)=fOSUserName()"
 
        Set rs = DBEngine(0)(0).OpenRecordset(strSql)
        If Not rs.EOF Then Session = rs.Fields("MaxOfSession")
        rs.Close
        Set rs = Nothing
 
        DoCmd.OpenForm "HomePage"
 
    Exit Function

 

[pbaldy]

Instead of

INSERT INTO...
SELECT...

try

INSERT INTO...
VALUES(...)

 

[RossWindows]

That's Beautiful!

        DoCmd.RunSQL "INSERT INTO ActiveUsers ( User )" & _
            " VALUES (fOSUserName());"