Find end of table and create new record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm a new user, so please bear with me, because I'm
certain this is a very simple question.

I created a table to log who enters my database.

I wrote the following code, and put it on the open event
for my database startup form.

I want to capture the date/time and username of each
person as they enter the database. This code works except
it writes over the only record in the table everytime. I
need to know how to move to the end of the table and add a
record for each time someone enters.

Can someone please help.

Here's the Code:

Private Sub Form_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Dim UseDate As Date
Dim User As String

UseDate = Now()
User = CurrentUser()

Set rs = DBEngine(0)(0).OpenRecordset("Usage Log",
dbOpenDynaset)
With rs
.Edit
![LogOnDate] = UseDate
![LogOnUser] = User
.Update
End With
rs.Close
Set rs = Nothing
End Sub
 
Using the .Edit method, as you are doing, updates an existing record. You
will need to make just one little change in your code to add a new record:

With rs
.AddNew 'replaces .Edit

I notice that you are using the CurrentUser() function to return the name of
each user. This function will return the UserId only if you have
implemented Access Security for this database; otherwise, it will return the
default user name, Admin, for all users. If Access Security is not
implemented for your database, you will need another method to retrieve the
UserID. The recommended way to do this is to copy the code found at the
following link at The Access Web into a public module:

http://www.mvps.org/access/api/api0008.htm

Then, instead of using CurrentUser() to get the User ID, you would change :

User = CurrentUser()

to

User = fOSUserName()

hth,
 
Thanks so much for the help. Works perfectly.
By the way I do have user level security on the database,
so the CurrentUser() function works as I intended.

Have a happy holiday season!
-----Original Message-----
Using the .Edit method, as you are doing, updates an existing record. You
will need to make just one little change in your code to add a new record:

With rs
.AddNew 'replaces .Edit

I notice that you are using the CurrentUser() function to return the name of
each user. This function will return the UserId only if you have
implemented Access Security for this database; otherwise, it will return the
default user name, Admin, for all users. If Access Security is not
implemented for your database, you will need another method to retrieve the
UserID. The recommended way to do this is to copy the code found at the
following link at The Access Web into a public module:

http://www.mvps.org/access/api/api0008.htm

Then, instead of using CurrentUser() to get the User ID, you would change :

User = CurrentUser()

to

User = fOSUserName()

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I'm a new user, so please bear with me, because I'm
certain this is a very simple question.

I created a table to log who enters my database.

I wrote the following code, and put it on the open event
for my database startup form.

I want to capture the date/time and username of each
person as they enter the database. This code works except
it writes over the only record in the table everytime. I
need to know how to move to the end of the table and add a
record for each time someone enters.

Can someone please help.

Here's the Code:

Private Sub Form_Open(Cancel As Integer)
Dim rs As DAO.Recordset
Dim UseDate As Date
Dim User As String

UseDate = Now()
User = CurrentUser()

Set rs = DBEngine(0)(0).OpenRecordset("Usage Log",
dbOpenDynaset)
With rs
.Edit
![LogOnDate] = UseDate
![LogOnUser] = User
.Update
End With
rs.Close
Set rs = Nothing
End Sub
Click to expand...


.
Click to expand...
 
If you are still reading, be aware that there is no such thing as the "end"
of a table. In a relational database, a table is an unordered bucket of
information. It does not have a "start" (or "first" record), or "end" (or
"last" record). You only get a predictable order by using the ORDER BY
clause in an SQL statement, or the OrderBy[On] properties of a form or
report.

HTH,
TC
 
Back
Top