Solved Docmd.RunSQL INSERT INTO... (1 Viewer)

evictme

Registered User.
Local time
Today, 15:42
Joined
May 18, 2011
Messages
168
Hello All, I have this code (see below) for keeping track of users logged in...It is in the "Form_Load" code and opposite code to delete in the "Form_Close" code.

I've just added a field to the table - [tblCurrentlyLoggedIn]; [empEnviron]. I added this field because I want to capture the PC name they are using.
How would I add the field and the 'Environ() AS empEnviron" into the existing code? Im stuck with the syntax and cant really find something that helps.

I would also like to add the field into the DELETE portion of the Form_Close code.

Any help would be greatly appreciated. Thank you in advance!

Form_load Code:
DoCmd.RunSQL "INSERT INTO tblCurrentlyLoggedIn ( empCurrentlyLoggedIn ) " & _
"SELECT CurrentUser() AS Users;"

Form_close Code:
DoCmd.RunSQL "DELETE * FROM tblCurrentlyLoggedIn WHERE empCurrentlyLoggedIn = CurrentUser()"
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
You can also structure the Insert code as simply inserting Values.

The general structure is, which you can modify for your needs:

Code:
Insert into tablename (field1, field2, field3) values (value1, value2, value3)

In a dynamic SQL/VBA combination, it might become something more like:

Code:
"Insert into tablename(field1, field2, field3) values ('" & variablename & "','" & variablename & "','" & variablename & "'"
In the above example, I've used single quotes around what would be text-type destination table columns. If you use numbers, then you won't need the quotes.

HTH.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:42
Joined
Aug 30, 2003
Messages
36,118
Generally:

DoCmd.RunSQL "INSERT INTO tblCurrentlyLoggedIn ( empCurrentlyLoggedIn, SecondFieldName ) " & _
"SELECT CurrentUser() AS Users, SecondValue;"
 

evictme

Registered User.
Local time
Today, 15:42
Joined
May 18, 2011
Messages
168
Thank you all. This helps. However, it is giving me an error with Environ()...is the syntax for this not correct?
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
Environ() is just the proper name of the function, generally.
To actually use it you usually supply it a parameter, like Environ("Username")

I think you want environ("computername")
 

evictme

Registered User.
Local time
Today, 15:42
Joined
May 18, 2011
Messages
168
Environ() is just the proper name of the function, generally.
To actually use it you usually supply it a parameter, like Environ("Username")

I think you want environ("computername")
1592496502540.png


This is the error I get. Putting "computername" in quotes like that turns all the code red. Not sure whats going on
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
Can you post your code?
environ("computername") is definitely right ... I just used it. but maybe it's the whole line of code that's wrong somehow else.
 

evictme

Registered User.
Local time
Today, 15:42
Joined
May 18, 2011
Messages
168

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
In Access, it's better to use CurrentDB.Execute. Also, I don't normally use insert into ... select, so I am going to just post how I would do it; this works:

Code:
CurrentDb.Execute "insert into tblCurrentlyLoggedin (empCurrentlyLoggedIn,empEnviron) " _
                & " values ('" & CurrentUser() & "','" & Environ("computername") & "'", dbFailOnError

Sorry, maybe I am too lazy ... someone else may point out the syntax error in your original code.

But when I am building dynamic sql string by vba, I use insert into values, so am not that familiar with the other. In Access anyway.
 

evictme

Registered User.
Local time
Today, 15:42
Joined
May 18, 2011
Messages
168
In Access, it's better to use CurrentDB.Execute. Also, I don't normally use insert into ... select, so I am going to just post how I would do it; this works:

Code:
CurrentDb.Execute "insert into tblCurrentlyLoggedin (empCurrentlyLoggedIn,empEnviron) " _
                & " values ('" & CurrentUser() & "','" & Environ("computername") & "'", dbFailOnError

Sorry, maybe I am too lazy ... someone else may point out the syntax error in your original code.

But when I am building dynamic sql string by vba, I use insert into values, so am not that familiar with the other. In Access anyway.
Thank you.

I am getting a syntax error with "Insert Into"

1592496951370.png
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
One more thought. Do you realize that CurrentUser() is only going to reflect the "microsoft office username", which 99.9% of all Office users have never affirmatively gone into their Office settings and adjusted theirs, and it will show something meaningless (mine shows Admin).

You may prefer using Environ("Username") which will get their actual logged-in network username. There are a few ways to get that. Environ("Username") is one.
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
Sorry, add a closing parenthesis at the end. My bad.
Code:
CurrentDb.Execute "insert into tblCurrentlyLoggedin (empCurrentlyLoggedIn,empEnviron) " _
                & " values ('" & CurrentUser() & "','" & Environ("computername") & "')", dbFailOnError
 

evictme

Registered User.
Local time
Today, 15:42
Joined
May 18, 2011
Messages
168
One more thought. Do you realize that CurrentUser() is only going to reflect the "microsoft office username", which 99.9% of all Office users have never affirmatively gone into their Office settings and adjusted theirs, and it will show something meaningless (mine shows Admin).

You may prefer using Environ("Username") which will get their actual logged-in network username. There are a few ways to get that. Environ("Username") is one.

Actually, I am using the original mdb file for the backend with a accdb front-end. The mdb file has the old user security file associated, so they use a username and password from that security file.
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
Actually, I am using the original mdb file for the backend with a accdb front-end. The mdb file has the old user security file associated, so they use a username and password from that security file.
Ahh. Ok.
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
You may not have noticed my correction post to my syntax since we were also posting about currentuser() at that time.
 

evictme

Registered User.
Local time
Today, 15:42
Joined
May 18, 2011
Messages
168
You may not have noticed my correction post to my syntax since we were also posting about currentuser() at that time.
Thank you for the help. I really appreciate it. The code works perfectly.

How would I adjust the Close code to only delete the record if the Environ() matches? It works well enough and deletes the record that matches the user name but will delete ALL the records that match. I want to make sure that only the correct instance is deleted so i can see if and where they are logged in if they are logged in on multiple PCs.



1592502747463.png
 

Isaac

Lifelong Learner
Local time
Today, 13:42
Joined
Mar 14, 2017
Messages
8,738
Untested aircode:
Code:
CurrentDB.Execute "delete * from tblCurrentlyLoggedIn where empCurrentlyLoggedIn=CurrentUser() and empEnviron='" & environ("computername") & "'",dbFailOnError
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:42
Joined
Aug 30, 2003
Messages
36,118
add

" AND empEnviron = '" & Environ("computername") & "'"
 

Users who are viewing this thread

Top Bottom