INSERT INTO issue - help please.

  • Thread starter Thread starter Rhys Davies
  • Start date Start date
R

Rhys Davies

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
 
Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?
 
Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

Ofer Cohen said:
Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


Rhys Davies said:
Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
 
Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


Rhys Davies said:
Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

Ofer Cohen said:
Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


Rhys Davies said:
Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Ofer Cohen said:
Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


Rhys Davies said:
Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

Ofer Cohen said:
Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help Ofer, have it working nicely now with the following code:
DoCmd.RunSQL "UPDATE tbluserslogin SET [loggedoffdatetime]= Now WHERE
([user]) = forms!switchboard!userid AND [loggedoffdatetime] is null;"

it seems so simple when you get the answer!

Thanks again for your help.

Rhys

Ofer Cohen said:
Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


Rhys Davies said:
Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

Ofer Cohen said:
Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
thanks for your help Dale, i realised this afterwards so added an is null
criteria on the timedateloggedout field and that seems to work well.

thanks for your help.

Rhys.

Dale Fye said:
Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Ofer Cohen said:
Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


Rhys Davies said:
Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

:

Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Rhys,

The problem with using ISNULL([TimeDateLoggedOut]) is that if a user closes
your application abnormally, or the system locks up and they have to reboot,
you will have a NULL for that record. The next time you log on and log off,
you will set the LoggedOut time for both the current session and the previous
one to the new LoggedOut time.

Personally, I prefer to have only the current session changed. Makes it
easier for me to identify the users that are having difficulties or who don't
understand how to properly exit the application if I can identify the NULL
LoggedOut values.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Rhys Davies said:
thanks for your help Dale, i realised this afterwards so added an is null
criteria on the timedateloggedout field and that seems to work well.

thanks for your help.

Rhys.

Dale Fye said:
Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Ofer Cohen said:
Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


:

Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

:

Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dale, yes, a good point as ive just realised!

I tried your code but got a syntax error(missing operator) message, which
usually means that there is a quotation mark missing or out of place that
takes days to find!!

Any idea where it might be?

Dale Fye said:
Rhys,

The problem with using ISNULL([TimeDateLoggedOut]) is that if a user closes
your application abnormally, or the system locks up and they have to reboot,
you will have a NULL for that record. The next time you log on and log off,
you will set the LoggedOut time for both the current session and the previous
one to the new LoggedOut time.

Personally, I prefer to have only the current session changed. Makes it
easier for me to identify the users that are having difficulties or who don't
understand how to properly exit the application if I can identify the NULL
LoggedOut values.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Rhys Davies said:
thanks for your help Dale, i realised this afterwards so added an is null
criteria on the timedateloggedout field and that seems to work well.

thanks for your help.

Rhys.

Dale Fye said:
Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


:

Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

:

Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Rhys,

Actually, I think the problem is on the line starting: WHERE

insert a space between the quote and the word WHERE:

Change: "WHERE
To : " WHERE

I also see that there is a line wrap underscore missing on the line starting:

(SELECT MAX(

but I'm sure you had to fix that just to get the code running.

You also might want to try:

SET [LoggedOffDateTime] = #" & Now() & "# " _

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rhys Davies said:
Thanks Dale, yes, a good point as ive just realised!

I tried your code but got a syntax error(missing operator) message, which
usually means that there is a quotation mark missing or out of place that
takes days to find!!

Any idea where it might be?

Dale Fye said:
Rhys,

The problem with using ISNULL([TimeDateLoggedOut]) is that if a user closes
your application abnormally, or the system locks up and they have to reboot,
you will have a NULL for that record. The next time you log on and log off,
you will set the LoggedOut time for both the current session and the previous
one to the new LoggedOut time.

Personally, I prefer to have only the current session changed. Makes it
easier for me to identify the users that are having difficulties or who don't
understand how to properly exit the application if I can identify the NULL
LoggedOut values.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Rhys Davies said:
thanks for your help Dale, i realised this afterwards so added an is null
criteria on the timedateloggedout field and that seems to work well.

thanks for your help.

Rhys.

:

Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


:

Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

:

Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Genius! Those changes did the trick, works like a dream.

thanks very much,


Rhys.

Dale Fye said:
Rhys,

Actually, I think the problem is on the line starting: WHERE

insert a space between the quote and the word WHERE:

Change: "WHERE
To : " WHERE

I also see that there is a line wrap underscore missing on the line starting:

(SELECT MAX(

but I'm sure you had to fix that just to get the code running.

You also might want to try:

SET [LoggedOffDateTime] = #" & Now() & "# " _

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rhys Davies said:
Thanks Dale, yes, a good point as ive just realised!

I tried your code but got a syntax error(missing operator) message, which
usually means that there is a quotation mark missing or out of place that
takes days to find!!

Any idea where it might be?

Dale Fye said:
Rhys,

The problem with using ISNULL([TimeDateLoggedOut]) is that if a user closes
your application abnormally, or the system locks up and they have to reboot,
you will have a NULL for that record. The next time you log on and log off,
you will set the LoggedOut time for both the current session and the previous
one to the new LoggedOut time.

Personally, I prefer to have only the current session changed. Makes it
easier for me to identify the users that are having difficulties or who don't
understand how to properly exit the application if I can identify the NULL
LoggedOut values.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

thanks for your help Dale, i realised this afterwards so added an is null
criteria on the timedateloggedout field and that seems to work well.

thanks for your help.

Rhys.

:

Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


:

Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

:

Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
I spoke too soon! it enters tha lastloggedout date in but it reverses the
day and month for me to an american format i.e. 12/05/2007 rather than
05/12/2007. If i remove the # from that line then it brings up a syntax
error pop up message on the date but it shows the date in the error message
in the format that i want 05/12/2007 - is there something else that i need to
add to format the date in the correct manner?

Thanks,

Rhys.

Dale Fye said:
Rhys,

Actually, I think the problem is on the line starting: WHERE

insert a space between the quote and the word WHERE:

Change: "WHERE
To : " WHERE

I also see that there is a line wrap underscore missing on the line starting:

(SELECT MAX(

but I'm sure you had to fix that just to get the code running.

You also might want to try:

SET [LoggedOffDateTime] = #" & Now() & "# " _

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rhys Davies said:
Thanks Dale, yes, a good point as ive just realised!

I tried your code but got a syntax error(missing operator) message, which
usually means that there is a quotation mark missing or out of place that
takes days to find!!

Any idea where it might be?

Dale Fye said:
Rhys,

The problem with using ISNULL([TimeDateLoggedOut]) is that if a user closes
your application abnormally, or the system locks up and they have to reboot,
you will have a NULL for that record. The next time you log on and log off,
you will set the LoggedOut time for both the current session and the previous
one to the new LoggedOut time.

Personally, I prefer to have only the current session changed. Makes it
easier for me to identify the users that are having difficulties or who don't
understand how to properly exit the application if I can identify the NULL
LoggedOut values.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

thanks for your help Dale, i realised this afterwards so added an is null
criteria on the timedateloggedout field and that seems to work well.

thanks for your help.

Rhys.

:

Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


:

Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

:

Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Hi Dale, changed code to:
SET [LoggedOffDateTime] =Now" _
and that has worked.

Thanks for all of your help, that is now a really ueful feature for me to
have in my database.

Rhys.


Dale Fye said:
Rhys,

Actually, I think the problem is on the line starting: WHERE

insert a space between the quote and the word WHERE:

Change: "WHERE
To : " WHERE

I also see that there is a line wrap underscore missing on the line starting:

(SELECT MAX(

but I'm sure you had to fix that just to get the code running.

You also might want to try:

SET [LoggedOffDateTime] = #" & Now() & "# " _

Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Rhys Davies said:
Thanks Dale, yes, a good point as ive just realised!

I tried your code but got a syntax error(missing operator) message, which
usually means that there is a quotation mark missing or out of place that
takes days to find!!

Any idea where it might be?

Dale Fye said:
Rhys,

The problem with using ISNULL([TimeDateLoggedOut]) is that if a user closes
your application abnormally, or the system locks up and they have to reboot,
you will have a NULL for that record. The next time you log on and log off,
you will set the LoggedOut time for both the current session and the previous
one to the new LoggedOut time.

Personally, I prefer to have only the current session changed. Makes it
easier for me to identify the users that are having difficulties or who don't
understand how to properly exit the application if I can identify the NULL
LoggedOut values.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

thanks for your help Dale, i realised this afterwards so added an is null
criteria on the timedateloggedout field and that seems to work well.

thanks for your help.

Rhys.

:

Another issue here is since you are inserting a record when they log in, you
should limit your Update query to update the loggedoff field only for the
most recent record entered for that individual. Something like:

strSQL = "UPDATE tblUsersLoggedIn " _
& "SET [LoggedOffDateTime] = " & Now() _
& "WHERE [User] = '" & loginname & "' " _
& " AND [LoggedInDateTime] = " _
& "(SELECT MAX([LoggedInDateTime]) "
& "FROM tblUsersLoggedIn " _
& "WHERE [User] = '" & loginname & "')"

Otherwise, you will update the logged out time of every record pertaining to
that individual, each time they log out.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


:

Sorry , my mistake, I forgot to remve it,

Try
strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE [user]
= '" & loginname & "'"

--
Good Luck
BS"D


:

Hmm yes monday afternoon blues, what an idiot, especially as i had created a
near identical update query in VBA last week! However on this one it gives
me an error when i try and close the application:

Undefined fucntion 'tblusersloggedin' in expression.

Here is my amended code:

strsql = "UPDATE tblusersloggedin SET [loggedoffdatetime] = Now WHERE
tblusersloggedin([user]) = '" & loginname & "'"

Any ideas where im going wrong, think i have my dunce cap on today!

:

Insert will add a new record to the table, so you can't add a criteria to the
table you are adding records to, you need to use update query instead

Try:

strsql = "Update tblusersloggedin Set [loggedoffdatetime] = '" Now & "'" & _
" WHERE tblusersloggedin([user]) = '" & loginname & "'"

In addition: why the date field time is string and not DateTime?

--
Good Luck
BS"D


:

Hi - when a users logs in the following code runs in my system:

DoCmd.SetWarnings False
strsql = "INSERT INTO tblusersloggedin([userID],[loggedindatetime],[user])"
& _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

this is fine because it enters the users unique ID number, loginname and
time/date into the table so that i can see who logged in when.

However, i now want to add similar code on the close command button that
will insert the date/time that the user logged off.

The code i have is:

Dim strsql As String
strsql = "INSERT INTO tblusersloggedin([loggedoffdatetime])" & _
"SELECT" & "'" & Now & "'" & _
"WHERE tblusersloggedin([user]) = '" & loginname & "'"
DoCmd.RunSQL strsql

i am using the WHERE clause to match the value in the user field in table
'tblusersloggedin' with their loginname so that the log off time/date will be
entered against the correct user. loginname is a global variable that
persists while they are logged into the system.

However access doesnt like my syntax, keeps saying that there is a missing
expression - can anyone help?

Thanks,

Rhys.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top