sql to pass value to variable to pass to text box

[DawnTreader]

Hello All

quick question, i have form that "stores" the user and i am trying to record
the time and date they logged in and logged out. i have been doing that but
up till now i have been doing logging in on a line in a table and then the
logging out on a new seperate line. after a question was asked of me i
realised i should be doing both on one line and that i need to change the way
some things work in my login proceedure.

here is some code that i am fiddling with now:

DoCmd.RunSQL "INSERT INTO tblLoginLog (EmployeeId, Type,
SystemModule, DateIn) VALUES (" & DBLoginId & ", 'In', 'Server', #" & Now() &
"#)"

loginSQL = "SELECT Max(tblLoginLog.LoginLogId) AS MaxOfLoginLogId
FROM tblLoginLog " & _
"GROUP BY tblLoginLog.EmployeeId, tblLoginLog.DateOut "
& _
"HAVING
(((tblLoginLog.EmployeeId)=[Forms]![zzMAINFORM]![cboEmployee]) " & _
"AND ((Max(tblLoginLog.DateIn)) Is Not Null) AND
((tblLoginLog.DateOut) Is Null))"
MsgBox loginSQL
' loginSessionID = loginSQL
Me.txtLoginSessionID = loginSessionID

Me.cboEmployee = DBLoginId

the first bit of sql causes a new line in the login table, the second bit i
am trying to do is to find that new record and store it on my form. once i
have done that i can call it back from the form when the user logs out and
put both the login and log out date and time on the same record in the table.
the problem is i dont know how to do a select sql statement in vba. i need to
know how to tell it to do the sql and the result get passed to my variable.

any and all help appreciated.

 

[Allen Browne]

Concatenate the value from the form into your string:
"HAVING (tblLoginLog.EmployeeId = " & Forms!zzMAINFORM!cboEmployee] & ") AND
....

Note that you need extra quotes if EmployeeId is a Text field (not a Number
field.)

 

[DawnTreader]

Right... i forgot about that one needing to be that way.

thanks. will test it tomorrow.

Concatenate the value from the form into your string:
"HAVING (tblLoginLog.EmployeeId = " & Forms!zzMAINFORM!cboEmployee] & ") AND
....

Note that you need extra quotes if EmployeeId is a Text field (not a Number
field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello All

quick question, i have form that "stores" the user and i am trying to
record
the time and date they logged in and logged out. i have been doing that
but
up till now i have been doing logging in on a line in a table and then the
logging out on a new seperate line. after a question was asked of me i
realised i should be doing both on one line and that i need to change the
way
some things work in my login proceedure.

here is some code that i am fiddling with now:

DoCmd.RunSQL "INSERT INTO tblLoginLog (EmployeeId, Type,
SystemModule, DateIn) VALUES (" & DBLoginId & ", 'In', 'Server', #" &
Now() &
"#)"

loginSQL = "SELECT Max(tblLoginLog.LoginLogId) AS MaxOfLoginLogId
FROM tblLoginLog " & _
"GROUP BY tblLoginLog.EmployeeId, tblLoginLog.DateOut "
& _
"HAVING
(((tblLoginLog.EmployeeId)=[Forms]![zzMAINFORM]![cboEmployee]) " & _
"AND ((Max(tblLoginLog.DateIn)) Is Not Null) AND
((tblLoginLog.DateOut) Is Null))"
MsgBox loginSQL
' loginSessionID = loginSQL
Me.txtLoginSessionID = loginSessionID

Me.cboEmployee = DBLoginId

the first bit of sql causes a new line in the login table, the second bit
i
am trying to do is to find that new record and store it on my form. once i
have done that i can call it back from the form when the user logs out and
put both the login and log out date and time on the same record in the
table.
the problem is i dont know how to do a select sql statement in vba. i need
to
know how to tell it to do the sql and the result get passed to my
variable.

any and all help appreciated.

 

[DawnTreader]

Hello Allen

here is the code currently:

loginSQL = "SELECT Max(tblLoginLog.LoginLogId) AS MaxOfLoginLogId FROM
tblLoginLog " & _
"GROUP BY tblLoginLog.EmployeeId, tblLoginLog.DateOut " & _
"HAVING (((tblLoginLog.EmployeeId)= " &
[Forms]![zzMAINFORM]![cboEmployee].Column(0) & ") " & _
"AND ((Max(tblLoginLog.DateIn)) Is Not Null) AND
((tblLoginLog.DateOut) Is Null))"
MsgBox loginSQL
loginSessionID = loginSQL
Me.txtLoginSessionID = loginSessionID

this works up to the "loginSessionID = loginSQL" line. i know i am not doing
the grabbing of the sessionid right, what do i need to do to execute the sql
and get the value into the loginSessionID variable.

Concatenate the value from the form into your string:
"HAVING (tblLoginLog.EmployeeId = " & Forms!zzMAINFORM!cboEmployee] & ") AND
....

Note that you need extra quotes if EmployeeId is a Text field (not a Number
field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello All

quick question, i have form that "stores" the user and i am trying to
record
the time and date they logged in and logged out. i have been doing that
but
up till now i have been doing logging in on a line in a table and then the
logging out on a new seperate line. after a question was asked of me i
realised i should be doing both on one line and that i need to change the
way
some things work in my login proceedure.

here is some code that i am fiddling with now:

DoCmd.RunSQL "INSERT INTO tblLoginLog (EmployeeId, Type,
SystemModule, DateIn) VALUES (" & DBLoginId & ", 'In', 'Server', #" &
Now() &
"#)"

loginSQL = "SELECT Max(tblLoginLog.LoginLogId) AS MaxOfLoginLogId
FROM tblLoginLog " & _
"GROUP BY tblLoginLog.EmployeeId, tblLoginLog.DateOut "
& _
"HAVING
(((tblLoginLog.EmployeeId)=[Forms]![zzMAINFORM]![cboEmployee]) " & _
"AND ((Max(tblLoginLog.DateIn)) Is Not Null) AND
((tblLoginLog.DateOut) Is Null))"
MsgBox loginSQL
' loginSessionID = loginSQL
Me.txtLoginSessionID = loginSessionID

Me.cboEmployee = DBLoginId

the first bit of sql causes a new line in the login table, the second bit
i
am trying to do is to find that new record and store it on my form. once i
have done that i can call it back from the form when the user logs out and
put both the login and log out date and time on the same record in the
table.
the problem is i dont know how to do a select sql statement in vba. i need
to
know how to tell it to do the sql and the result get passed to my
variable.

any and all help appreciated.

 

[DawnTreader]

Hello Allen

i have figured out what i needed to do. after playing with a few ideas and
things that were placed in the wrong place i got it working to do exactly
what i needed.

final code:

strDLWhere = "[EmployeeId] =" & Me!cboEmployee.Column(0)
loginSessionID = DLookup("[SessionID]", "qryNewLoginIdea", strDLWhere)
Me.txtLoginSessionID = loginSessionID

moved away from a query because selects dont work well with what i was
trying to do in VBA. the dlookup works and the other piece of code for when
someone logs out does what i need it to as well.

thanks for the help, even though i went a totally different route

Concatenate the value from the form into your string:
"HAVING (tblLoginLog.EmployeeId = " & Forms!zzMAINFORM!cboEmployee] & ") AND
....

Note that you need extra quotes if EmployeeId is a Text field (not a Number
field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello All

quick question, i have form that "stores" the user and i am trying to
record
the time and date they logged in and logged out. i have been doing that
but
up till now i have been doing logging in on a line in a table and then the
logging out on a new seperate line. after a question was asked of me i
realised i should be doing both on one line and that i need to change the
way
some things work in my login proceedure.

here is some code that i am fiddling with now:

DoCmd.RunSQL "INSERT INTO tblLoginLog (EmployeeId, Type,
SystemModule, DateIn) VALUES (" & DBLoginId & ", 'In', 'Server', #" &
Now() &
"#)"

loginSQL = "SELECT Max(tblLoginLog.LoginLogId) AS MaxOfLoginLogId
FROM tblLoginLog " & _
"GROUP BY tblLoginLog.EmployeeId, tblLoginLog.DateOut "
& _
"HAVING
(((tblLoginLog.EmployeeId)=[Forms]![zzMAINFORM]![cboEmployee]) " & _
"AND ((Max(tblLoginLog.DateIn)) Is Not Null) AND
((tblLoginLog.DateOut) Is Null))"
MsgBox loginSQL
' loginSessionID = loginSQL
Me.txtLoginSessionID = loginSessionID

Me.cboEmployee = DBLoginId

the first bit of sql causes a new line in the login table, the second bit
i
am trying to do is to find that new record and store it on my form. once i
have done that i can call it back from the form when the user logs out and
put both the login and log out date and time on the same record in the
table.
the problem is i dont know how to do a select sql statement in vba. i need
to
know how to tell it to do the sql and the result get passed to my
variable.

any and all help appreciated.