Using with statement to Open aTable

I

iholder

I am setting up login tracking. Need to open table in the add mode and data
to the following fields . [UserID], [LogInTime], [LogDate]

Table is not attached any form or report.
Is the "with" statement the right approach, if not please suggest.
 
J

Joerg Ackermann

iholder said:
I am setting up login tracking. Need to open table in the add mode
and data to the following fields . [UserID], [LogInTime], [LogDate]

Table is not attached any form or report.
Is the "with" statement the right approach, if not please suggest.
Click to expand...

I would not split Logdate and logintime in two fields.
It's simple to get Date or time using Format-Function if needed.

CurrentDb.Execute "insert into tblLogin (userID, Login) Values
(CurrentUser(), Now());"

Acki
 
I

iholder

Please note the [UserId] is the id of the first intial of first name and the
last. ex:iholder CurrentUser() is giving me the system admin id. this is not
what I want
 
J

Joerg Ackermann

iholder said:
Please note the [UserId] is the id of the first intial of first name
and the last. ex:iholder CurrentUser() is giving me the system admin
id. this is not what I want
Click to expand...

Ok, then do this:

dim idUser as string

idUser = GetWhatEverYouWant()

CurrentDb.Execute "insert into tblLogin (UserID, Login) Values ('" &
idUser & "', Now());"

(Must be one line)
Please note the [UserId] is the id of the first intial of first name
and the last. ex:iholder
Click to expand...

Sorry, what is an ex:iholder?
(my english is not so good)

The Username of logged-in User you can get from:
Environ("UserName")

Acki
 
I

iholder

CurrentDb.Execute "insert into tblLogin (userID, Login) Values
(CurrentUser(), Now());"
Click to expand...
This code is not working for me. All I need is a easy way to open a table
and add a record. I am not trying to get network user info. using a
recordset and "with statement.


Joerg Ackermann said:
iholder said:
I am setting up login tracking. Need to open table in the add mode
and data to the following fields . [UserID], [LogInTime], [LogDate]

Table is not attached any form or report.
Is the "with" statement the right approach, if not please suggest.
Click to expand...

I would not split Logdate and logintime in two fields.
It's simple to get Date or time using Format-Function if needed.

CurrentDb.Execute "insert into tblLogin (userID, Login) Values
(CurrentUser(), Now());"

Acki
Click to expand...
 
B

Brendan Reynolds

You can still use the Execute method, you just have to replace the reference
to CurrentUser() with a reference to wherever your user ID is stored. And
this will be the most efficient way to do it. if you really want to use a
recordset though ...

Public Sub AddRecord()

'Using DAO

Dim db As DAO.Database
Dim rstd As DAO.Recordset

Set db = CurrentDb
Set rstd = db.OpenRecordset("SELECT TestText, TestDate FROM tblTest", ,
dbAppendOnly)
With rstd
.AddNew
.Fields("TestText") = "My User ID 1"
.Fields("TestDate") = Now()
.Update
.Close
End With

'Using ADO
Dim rsta As ADODB.Recordset

Set rsta = New ADODB.Recordset
With rsta
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.Source = "SELECT TestText, TestDate FROM tblTest"
.Open
.AddNew
.Fields("TestText") = "My User ID 2"
.Fields("TestDate") = Now()
.Update
.Close
End With

End Sub

--
Brendan Reynolds (MVP)


iholder said:
CurrentDb.Execute "insert into tblLogin (userID, Login) Values
(CurrentUser(), Now());"
Click to expand...
This code is not working for me. All I need is a easy way to open a table
and add a record. I am not trying to get network user info. using a
recordset and "with statement.


Joerg Ackermann said:
iholder said:
I am setting up login tracking. Need to open table in the add mode
and data to the following fields . [UserID], [LogInTime], [LogDate]

Table is not attached any form or report.
Is the "with" statement the right approach, if not please suggest.
Click to expand...

I would not split Logdate and logintime in two fields.
It's simple to get Date or time using Format-Function if needed.

CurrentDb.Execute "insert into tblLogin (userID, Login) Values
(CurrentUser(), Now());"

Acki
Click to expand...
Click to expand...
 
J

Jörg Ackermann

Hi,

iholder schreibselte:
This code is not working for me. All I need is a easy way to open a
table and add a record. I am not trying to get network user info.
using a recordset and "with statement.
Click to expand...

Do you mean that:

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

strUser = "IH"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogin", dbOpenDynaset, dbAppendOnly)

With rs
.AddNew
!UserID = strUser
!Login = Now
.Update
End With

rs.Close: Set rs = Nothing
Set db = Nothing

If yes, this makes the same (a little shorter):

CurrentDb.Execute "insert into tblLogin (userID, Login) " & _
"Values ('IH', Now());"

Acki
 
I

iholder

Thank You Jorg,

This is what I want to do. The recordset method. The other method will not
work. It does not allow for more than two field parameters. I need to
append four fields of info. The fields are
[UserId],[LogInTime],[LogInDate],[LogOutTime]. The time field must be
separate. I need to do a time calculation. How long the user was logged on
the system.

What do you think now. Is this a okay approach?
 
I

iholder

I am getting an error with the code.
Dim db As DAO.Database "this line"
Error message: Compile error - user define type not defined.



iholder said:
Thank You Jorg,

This is what I want to do. The recordset method. The other method will not
work. It does not allow for more than two field parameters. I need to
append four fields of info. The fields are
[UserId],[LogInTime],[LogInDate],[LogOutTime]. The time field must be
separate. I need to do a time calculation. How long the user was logged on
the system.

What do you think now. Is this a okay approach?

Jörg Ackermann said:
Hi,

iholder schreibselte:


Do you mean that:

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

strUser = "IH"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblLogin", dbOpenDynaset, dbAppendOnly)

With rs
.AddNew
!UserID = strUser
!Login = Now
.Update
End With

rs.Close: Set rs = Nothing
Set db = Nothing

If yes, this makes the same (a little shorter):

CurrentDb.Execute "insert into tblLogin (userID, Login) " & _
"Values ('IH', Now());"

Acki
Click to expand...
Click to expand...
 
J

Jörg Ackermann

Hi,
I am getting an error with the code.
Dim db As DAO.Database "this line"
Error message: Compile error - user define type not defined.
Click to expand...

You must add a DAO reference to your project.
If you go under the 'Extra' menu in VBA and under 'References' put a
tick next to "Microsoft DAO 3.x Object Library".

If you use A2k and above it should be
Microsoft DAO 3.6 Object Library.

Acki
 
I

iholder

Everything work fine. Now I need to code the LogOut Process.

That table and record need to be updated upon logout the [ LogOutTime] needs
to be update with the Log Out Time.

Do I need to lock this record or search for this record by userid to insure
that the correct log out time is apply to the correct user.

What is the correct approach and code needed

Thank You
 
J

Joerg Ackermann

iholder said:
Everything work fine. Now I need to code the LogOut Process.

That table and record need to be updated upon logout the [
LogOutTime] needs to be update with the Log Out Time.

Do I need to lock this record or search for this record by userid to
insure that the correct log out time is apply to the correct user.

What is the correct approach and code needed
Click to expand...

Take the .AddNew out and set a .Findfirst... in.
Synthax and samples you can find in the Access-help Findfirst<F1>

Acki
 
Top