Updateing a record with a Null value field, fails?

  • Thread starter Thread starter Jan T
  • Start date Start date
J

Jan T

I am using Access 2010 (accdb) and ADO with VBA. Trying to write code
to log whenever a person
is loged in or out of the program. I am using the following table for
loging this:

ID (autonumber)
User (text)
LogedIn (date/time)
LogedOut (date/time)

When open the database, an macro are adding a new record and fills out
all fields except
the LogedOut field.
When closing the program, the code should look for a record with
UserName so and so and that still have no LogedOut field data in it.
That is, LogedOut is Null (date/time field), right? When found, the
macro should fill in the right date and time (using Now() function).

However, the code never adds date/time to the empty LogedOut field?
and I just don't see why?
It looks like it does not recognise that LogedOut = Null? and therefor
do not add data to the empty field.

Here is the code I use:

Sub LogOut()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
strCriteria = ReturnUserName

rst.Open "tblLogedIn", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

With rst
.MoveFirst
Do While Not rst.EOF
If ![User] = strCriteria Then
If ![LogedOut] = Null Then ' /// the code never
returns true here, why?
![LogedOut] = Now()
.Update
End If
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub

Any help is very much appreciated.

Regards Jan T
 
To start with, "Null" means "undefined" and doesn't participate in
arithmetic equations. You can test for it, using the IsNull built-in
function.

For a couple of reasons, I won't try to correct your example: (1) ADO is
effectively deprecated, I've never had occasion to use ADO outside ADPs, and
I won't encourage people to 'do the wrong thing' *, and (2) using WITH in
examples makes them more difficult to read and you should make the examples
for your questions as easy to read for the volunteers who expend time and
energy to assist you.

* but, by the way, the code you posted surely
appears to be identical to DAO code

Larry Linson
Microsoft Office Access MVP
 
To start with, "Null" means "undefined" and doesn't participate in
arithmetic equations.  You can test for it, using the IsNull built-in
function.

For a couple of reasons, I won't try to correct your example: (1) ADO is
effectively deprecated, I've never had occasion to use ADO outside ADPs, and
I won't encourage people to 'do the wrong thing' *, and (2) using WITH in
examples makes them more difficult to read and  you should make the examples
for your questions as easy to read for the volunteers who expend time and
energy to assist you.

 * but, by the way, the code you posted surely
    appears to be identical to DAO code

 Larry Linson
 Microsoft Office Access MVP




I am using Access 2010 (accdb) and ADO with VBA. Trying to write code
to log whenever a person
is loged in or out of the program. I am using the following table for
loging this:
Click to expand...
ID    (autonumber)
User (text)
LogedIn (date/time)
LogedOut (date/time)
Click to expand...
When open the database, an macro are adding a new record and fills out
all fields except
the LogedOut field.
When closing the program, the code should look for a record with
UserName so and so and that still have no LogedOut field data in it.
That is, LogedOut is Null (date/time field), right? When found, the
macro should fill in the right date and time (using Now() function).
Click to expand...
However, the code never adds date/time to the empty LogedOut field?
and I just don't see why?
It looks like it does not recognise that LogedOut = Null? and therefor
do not add data to the empty field.
Click to expand...
Here is the code I use:
Click to expand...
Sub LogOut()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
   strCriteria = ReturnUserName
Click to expand...
   rst.Open "tblLogedIn", CurrentProject.Connection, _
     adOpenDynamic, adLockOptimistic
Click to expand...
   With rst
       .MoveFirst
       Do While Not rst.EOF
           If ![User] = strCriteria Then
               If ![LogedOut] = Null Then ' /// the code never
returns true here, why?
                   ![LogedOut] = Now()
                   .Update
               End If
           End If
           .MoveNext
       Loop
   End With
   rst.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub
Click to expand...
Any help is very much appreciated.
Click to expand...
Regards Jan T– Skjul sitert tekst –
Click to expand...

– Vis sitert tekst –
Click to expand...


Thank you for your good answer. I tried to change it and then
it worked like a dream. Here is the changed code (removed the
WITH keyword to make it easier to read:

Sub LogOut()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
strCriteria = ReturnUserName

rst.Open "tblLogedIn", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

rst.MoveFirst
Do While Not rst.EOF
If rst![User] = strCriteria Then
If isNull( rst![LogedOut] ) Then
' ///
rst![LogedOut] = Now()
rst.Update
End If
End If
.MoveNext
Loop

rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub

Sounds like DAO is now the preferred one, and not ADO as I think was
introduced
in Access 2000. Well, if that is so, it is probably a good idea to
start using DAO.

Thanks again for your help and advice.


Regards
Jan T.
 
My apologies -- on re-reading my response, I sounded more than a little
"snippy" and that was not my intent.

DAO is the "native language" for the Jet database engine and for its
descendant the ACE database engine. There was a big "push" from someone in
Microsoft to promote the use of ADO back around 1999 - 2000, but "classic
ADO" turned out to be just another "flavor of the release" access method
from Redmond. In Microsoft's "real world of development" (that is, DotNet),
it was quickly superceded by ADO.NET which is built on a different object
model and shares only part of its name with "classic ADO" as still used in
Access. Experienced developers, especially those who'd gone through other
"flavor of the release" access methods with the VB development tool, just
didn't find it worthwhile, and didn't leap feetfirst into adopting it just
because someone in Redmond was hyping it.

Interestingly, ADO.NET has lasted quite a while -- it was/is not just
another in the long line of "flavor of the release" access methods.

Thanks for your patience. I'll make an effort to be less of a "grumpy old
man".

Larry Linson
Microsoft Office Access MVP


To start with, "Null" means "undefined" and doesn't participate in
arithmetic equations. You can test for it, using the IsNull built-in
function.

For a couple of reasons, I won't try to correct your example: (1) ADO is
effectively deprecated, I've never had occasion to use ADO outside ADPs,
and
I won't encourage people to 'do the wrong thing' *, and (2) using WITH in
examples makes them more difficult to read and you should make the
examples
for your questions as easy to read for the volunteers who expend time and
energy to assist you.

* but, by the way, the code you posted surely
appears to be identical to DAO code

Larry Linson
Microsoft Office Access MVP




I am using Access 2010 (accdb) and ADO with VBA. Trying to write code
to log whenever a person
is loged in or out of the program. I am using the following table for
loging this:
Click to expand...
ID (autonumber)
User (text)
LogedIn (date/time)
LogedOut (date/time)
Click to expand...
When open the database, an macro are adding a new record and fills out
all fields except
the LogedOut field.
When closing the program, the code should look for a record with
UserName so and so and that still have no LogedOut field data in it.
That is, LogedOut is Null (date/time field), right? When found, the
macro should fill in the right date and time (using Now() function).
Click to expand...
However, the code never adds date/time to the empty LogedOut field?
and I just don't see why?
It looks like it does not recognise that LogedOut = Null? and therefor
do not add data to the empty field.
Click to expand...
Here is the code I use:
Click to expand...
Sub LogOut()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
strCriteria = ReturnUserName
Click to expand...
rst.Open "tblLogedIn", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic
Click to expand...
With rst
.MoveFirst
Do While Not rst.EOF
If ![User] = strCriteria Then
If ![LogedOut] = Null Then ' /// the code never
returns true here, why?
![LogedOut] = Now()
.Update
End If
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub
Click to expand...
Any help is very much appreciated.
Click to expand...
Regards Jan T– Skjul sitert tekst –
Click to expand...

– Vis sitert tekst –
Click to expand...


Thank you for your good answer. I tried to change it and then
it worked like a dream. Here is the changed code (removed the
WITH keyword to make it easier to read:

Sub LogOut()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
strCriteria = ReturnUserName

rst.Open "tblLogedIn", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

rst.MoveFirst
Do While Not rst.EOF
If rst![User] = strCriteria Then
If isNull( rst![LogedOut] ) Then
' ///
rst![LogedOut] = Now()
rst.Update
End If
End If
.MoveNext
Loop

rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub

Sounds like DAO is now the preferred one, and not ADO as I think was
introduced
in Access 2000. Well, if that is so, it is probably a good idea to
start using DAO.

Thanks again for your help and advice.


Regards
Jan T.
 
There's actually no reason to use a recordset at all.

strSQL = "UPDATE tblLogedIn " & _
"SET LogedOut = Now() " & _
"WHERE User = '" & strCriteria & "' " & _
"AND LogedOut IS NULL"

"Jan T" wrote in message

– Vis sitert tekst –
Click to expand...


Thank you for your good answer. I tried to change it and then
it worked like a dream. Here is the changed code (removed the
WITH keyword to make it easier to read:

Sub LogOut()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
strCriteria = ReturnUserName

rst.Open "tblLogedIn", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

rst.MoveFirst
Do While Not rst.EOF
If rst![User] = strCriteria Then
If isNull( rst![LogedOut] ) Then
' ///
rst![LogedOut] = Now()
rst.Update
End If
End If
.MoveNext
Loop

rst.Close
Set rst = Nothing
Set cnn = Nothing
End Sub

Sounds like DAO is now the preferred one, and not ADO as I think was
introduced
in Access 2000. Well, if that is so, it is probably a good idea to
start using DAO.

Thanks again for your help and advice.


Regards
Jan T.
 
There's actually no reason to use a recordset at all.

strSQL = "UPDATE tblLogedIn " & _
  "SET LogedOut = Now() " & _
  "WHERE User = '" & strCriteria & "' " & _
  "AND LogedOut IS NULL"

"Jan T"  wrote in message


Vis sitert tekst
Click to expand...

Thank you for your good answer. I tried to change it and then
it worked like a dream. Here is the changed code (removed the
WITH keyword to make it easier to read:

Sub LogOut()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset
    strCriteria = ReturnUserName

    rst.Open "tblLogedIn", CurrentProject.Connection, _
      adOpenDynamic, adLockOptimistic

        rst.MoveFirst
        Do While Not rst.EOF
            If rst![User] = strCriteria Then
                If isNull( rst![LogedOut] ) Then
' ///
                    rst![LogedOut] = Now()
                    rst.Update
                End If
            End If
            .MoveNext
        Loop

    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
End Sub

Sounds like DAO is now the preferred one, and not ADO as I think was
introduced
in Access 2000. Well, if that is so, it is probably a good idea to
start using DAO.

Thanks again for your help and advice.

Regards
Jan T.
Click to expand...

Thank you so much for your contributions. I do appriciate so much the
help that are available in these groups. Both solutions, the SQL and
the
recordset, I am learning from both examples. Thank's Douglas J Steele.

And Larry Linson, thank you very much for your comment on your own
stuff. You made it up in deed. You are a winner! Thanks a lot for the
information about ADO versus DAO. I did not know so much about that.

Best wishes
Jan T.
 
Back
Top