Routine not working

[Afrosheen via AccessMonster.com]

Thanks for reading this and the help I've received

This is what I'm trying to do. I have a program where you'd log in then go to
the main program.
It takes information from the login, Date, Time, and other information and
put it in a login table.
The table is built with LogId, Name, Datein, Timein, Dateout, Timeout.

When I use the record set to input the information in the login table, it
works.

Now what I want to do is when I log off the program I want to be able to go
to the LogId and put the Date in the Dateout, and the Time in the Timeout. I
have a variable {lg} as the orignal record number of the record.

I tried using: lout = DLookup("logid", "tbllog", "logid = lg")
but it keeps on giving me errors.
Once I find that record, I thought I could just:

me.Dateout=Date
me.Timeout=Time

to enter the information.

Am I on track?

Thanks again for the help

 

[Jinjer]

Hi.

I have a similar routine in my apps. My table has an autonumber key field,
which I put in a global variable (which is then put in a function) when the
routine initially enters the login data. When the user logs out, I run an
update query with the function as the criteria.

Also, I have my LogonDate and LogoffDate formatted as General Date and use
the Now() function to update them. This way, both the date and the time are
entered in one field.

I hope this helps.

 

[Daniel Pineault]

If lg is a variable you cannot embed it like you did in the DLookup, you'd
do something more like:

assuming lg is a number:
lout = DLookup("logid", "tbllog", "logid = " & lg)

However, how are you going to edit the record exactly? Why not open the
recordset directly using SQL instead of doing a lookup and then opening it?!
Do it in one shot, with something like (air code):


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT * FROM tbllog WHERE logid = " & lg"
Set db = CurrentDb()
Set rs = db.OpenRecordset(sSQL)

If rs.RecordCount = 1 Then 'ensure there is only 1 record that matched
With rs
.Edit
![Dateout] = Date
![Timeout] = Time
.Update
End With
End If

'Cleanup
rs.Close
Set rs = Nothing
Set db = Nothing

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[John W. Vinson]

Thanks for reading this and the help I've received

This is what I'm trying to do. I have a program where you'd log in then go to
the main program.
It takes information from the login, Date, Time, and other information and
put it in a login table.
The table is built with LogId, Name, Datein, Timein, Dateout, Timeout.

Given the way Access handles date/time values, you would probably do better to
use just one TimeIn and TimeOut field, set to Now() - the current date and
time. Splitting the date and time into two fields is usually just an
additional burden on searching and displaying. Also, Name is a reserved word
and should not be used as a fieldname - I'd suggest UserName or something of
that sort.

When I use the record set to input the information in the login table, it
works.

Now what I want to do is when I log off the program I want to be able to go
to the LogId and put the Date in the Dateout, and the Time in the Timeout. I
have a variable {lg} as the orignal record number of the record.

I tried using: lout = DLookup("logid", "tbllog", "logid = lg")

Close. Since lg is a variable, you need to concatenate its value, rather than
searching tblLog for its *name*; there are no records in tbllog with the text
string "lg" in the LogID! Try

lOut = DLookUp("LogID", "tblLog", "LogID = " & lg)

However... this doesn't do anything useful; it just sets lOut to the LogID
which you already know!!! It's just lg... right?

but it keeps on giving me errors.
Once I find that record, I thought I could just:

me.Dateout=Date
me.Timeout=Time

to enter the information.

Am I on track?

Not really. To edit a record you need to either run an Update query, or open a
Recordset, use the recordset's Edit method, etc. Try this instead:

Dim strSQL As String
strSQL = "UPDATE tbllog SET DateOut = #" & Date() & _
"#, TimeOut = #" & Time() & "# WHERE LogID = " & lg
Application.Execute strSQL, dbFailOnError

or, if you take my advice on the time field, just

strSQL = "UPDATE tbllog SET TimeOut = #" & Now() & "# WHERE LogID = " & lg

 

[John W. Vinson]

I have a similar routine in my apps. My table has an autonumber key field,
which I put in a global variable (which is then put in a function) when the
routine initially enters the login data. When the user logs out, I run an
update query with the function as the criteria.

Just one warning, Jinjer - global variables lose their values if there's ever
a break in the code. You may want to stash the ID in a table or an invisible
form control instead.

 

[Afrosheen via AccessMonster.com]

Hi John, Thanks for your reply.
Here's what's happening. When I used your code:

strSQL = "UPDATE tbllog SET TimeOut = #" & Now() & "# WHERE LogID = " & lg

and I put a break and hover over the strSQL it will show the date and time ok,
but at the end it shows "Where LogID=" & ...

I put: msgbox lg and it shows the correct lg number.

Am I missing something?

Thanks for reading this and the help I've received

[quoted text clipped - 3 lines]

put it in a login table.
The table is built with LogId, Name, Datein, Timein, Dateout, Timeout.

Given the way Access handles date/time values, you would probably do better to
use just one TimeIn and TimeOut field, set to Now() - the current date and
time. Splitting the date and time into two fields is usually just an
additional burden on searching and displaying. Also, Name is a reserved word
and should not be used as a fieldname - I'd suggest UserName or something of
that sort.

When I use the record set to input the information in the login table, it
works.

[quoted text clipped - 4 lines]

I tried using: lout = DLookup("logid", "tbllog", "logid = lg")

Close. Since lg is a variable, you need to concatenate its value, rather than
searching tblLog for its *name*; there are no records in tbllog with the text
string "lg" in the LogID! Try

lOut = DLookUp("LogID", "tblLog", "LogID = " & lg)

However... this doesn't do anything useful; it just sets lOut to the LogID
which you already know!!! It's just lg... right?

but it keeps on giving me errors.
Once I find that record, I thought I could just:

[quoted text clipped - 5 lines]

Am I on track?

Not really. To edit a record you need to either run an Update query, or open a
Recordset, use the recordset's Edit method, etc. Try this instead:

Dim strSQL As String
strSQL = "UPDATE tbllog SET DateOut = #" & Date() & _
"#, TimeOut = #" & Time() & "# WHERE LogID = " & lg
Application.Execute strSQL, dbFailOnError

or, if you take my advice on the time field, just

strSQL = "UPDATE tbllog SET TimeOut = #" & Now() & "# WHERE LogID = " & lg

 

[John W. Vinson]

Hi John, Thanks for your reply.
Here's what's happening. When I used your code:

strSQL = "UPDATE tbllog SET TimeOut = #" & Now() & "# WHERE LogID = " & lg

and I put a break and hover over the strSQL it will show the date and time ok,
but at the end it shows "Where LogID=" & ...

I put: msgbox lg and it shows the correct lg number.

Is lg a VBA variable or what? Try stepping to the line after this one and
typing

?strSQL

in the Immediate window: what do you see?

What is the datatype of LogID and the value of lg?

 

[Afrosheen via AccessMonster.com]

Hi John,

When I ?strSQL I got:
UPDATE tbllog SET TimeOut = #10/28/2009 8:40:19 PM# WHERE LogID = 384

This is correct.

The TimeOut field is set for Time/Date
The logid is an autonumber.
The lg is a VBA variable.

The 384 would be the lg variable.

Hi John, Thanks for your reply.
Here's what's happening. When I used your code:

[quoted text clipped - 5 lines]

I put: msgbox lg and it shows the correct lg number.

Is lg a VBA variable or what? Try stepping to the line after this one and
typing

?strSQL

in the Immediate window: what do you see?

What is the datatype of LogID and the value of lg?

 

[John W. Vinson]

Hi John,

When I ?strSQL I got:
UPDATE tbllog SET TimeOut = #10/28/2009 8:40:19 PM# WHERE LogID = 384

Please post the actual code (the whole sub) and indicate what's happening (or
not happening) when you run it.

 

[Afrosheen via AccessMonster.com]

Hi again John. Here is the complete sub

'-----------------------------------------------------------------------------
----------
' Procedure : cmdQuit_Click
' Author : Tom
' Date : 5/12/2009
' Purpose :
' Notes :
'-----------------------------------------------------------------------------
----------
'
Private Sub cmdQuit_Click()
Dim strSQL As String
10 On Error GoTo cmdQuit_Click_Error
20 strSQL = "UPDATE tbllog SET TimeOut = #" & Now() & "# WHERE LogID =
" & lg
30 DoCmd.Quit

Exit_cmdQuit_Click:
40 Exit Sub


50 On Error GoTo 0
60 Exit Sub

cmdQuit_Click_Error:
70 Err.Description = Err.Description & " In Procedure " &
"cmdQuit_Click of VBA Document Form_frmPersonnel_Menu"
80 Call LogError(Err.Number, Err.Description, "cmdQuit_Click")


End Sub

In the tbllog I want to insert the time or the NOW() in the TimeOut field
where the lg variable is = to the record number. In other words go to the
record number {lg} and insert the NOW() in the TimeOut field.

The thing is that it is not updating the TimeOut field. Maybe I'm doing
something wrong.

Thanks for the help.

 

[John Spencer]

You never execute the sql string. You only create the string.

Simplest method would be

CurrentDb().Execute strSQL, dbFailOnError



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Afrosheen via AccessMonster.com]

Thanks to both John S. and John V.

It now works.

You never execute the sql string. You only create the string.

Simplest method would be

CurrentDb().Execute strSQL, dbFailOnError

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi again John. Here is the complete sub

[quoted text clipped - 44 lines]

 

[David W. Fenton]

Splitting the date and time into two fields is usually just an
additional burden on searching and displaying.

I disagree on that, mostly because I usually need the time only for
sorting, but use the date for primary data retrieval. Having the
date as a separate field makes the criteria a lot simpler.