This code works in one database, but not the other...

[Kelvin Beaton]

I have this code (see below) in a form that is to write an audit record.
I have sample database that this code runs just fine in, but when I import
the same form and table into one of my production databases, I get an error.
"Run-time error '3265': Item not found in this collection."
When is stops it stops on " rs!UserName = CurrentUser"

I'm not a code writer so have little skill in trouble shooting this problem.

Any help would be appreciated

Kelvin

++++++++++++++++++++
Sub WriteAuditUpdate(txtTableName, lngRecordNum, txtFieldName, OrgValue,
CurValue)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_AuditTable")

rs.AddNew
rs!tableName = txtTableName
rs!RecordPrimaryKey = lngRecordNum
rs!FieldName = txtFieldName
rs!LoginName = GetCurrentUserName
rs!MachineName = GetComputerName
rs!UserName = CurrentUser (it stops here)
rs!OriginalValue = OrgValue
rs!NewValue = CurValue
rs!DateTimeStamp = Now()
rs.Update
rs.Close
db.Close
End Sub
+++++++++++++++++++

 

[Guest]

go to access Help and enter 'currentuser', it will provide you with some
useful information.

-Dorian

 

[Allen Browne]

The CurrentUser() function comes from the DAO library. There may be a
problem with the DAO reference in the other database.

See:
Solving problems with Library References
at:
http://allenbrowne.com/ser-38.html

 

[Dirk Goldgar]

In

The CurrentUser() function comes from the DAO library. There may be a
problem with the DAO reference in the other database.

Allen, I may be misunderstanding you, but I believe you are mistaken.
It seems to me that CurrentUser is a method of the Access Application
object, and is not dependent on a DAO reference.

The most likely cause of Kelvin's error is that the field UserName does
not exist in tbl_AuditTable. Even though we're told that the table was
imported from a database where it works, I'd like to know if Kelvin did
a manual check to see that the field exists in that table.

 

[Allen Browne]

Dirk, you are correct.

A quick look in the Object Browser (F2 in the code window) confirms that
CurrentUser is a member of Access.Application.

Thanks

 

[Kelvin Beaton]

Hi Dirk

I checked and it is called "USER" in both databases...
There is no UserName field in either database...

Kelvin

 

[Dirk Goldgar]

In

Hi Dirk

I checked and it is called "USER" in both databases...
There is no UserName field in either database...

So were you mistaken in quoting the code from the "working" database, or
did Name Autocorrect maybe fix it there but not in the new database?

I assume that changing the field reference to rs!USER took care of the
problem you posted about.

 

[Kelvin Beaton]

Both the working and not working databases have the
These are the fields in Audit table.
++++++++++++++++
ID
LoginName
MachineName
User
TableName
RecordPrimaryKey
FieldName
OriginalValue
NewValue
DateTimeStamp
++++++++++++++++

This is the code that it stops on "rs!User = CurrentUser"
++++++++++++++++
Sub WriteAuditUpdate(txtTableName, lngRecordNum, txtFieldName, OrgValue,
CurValue)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_AuditTable")

rs.AddNew
rs!TableName = txtTableName
rs!RecordPrimaryKey = lngRecordNum
rs!FieldName = txtFieldName
rs!LoginName = GetCurrentUserName
rs!MachineName = GetComputerName
rs!User = CurrentUser (it stops here)
rs!OriginalValue = OrgValue
rs!NewValue = CurValue
rs!DateTimeStamp = Now()
rs.Update
rs.Close
db.Close
End Sub
++++++++++++++++

Thanks for your time..........

Kelvin

 

[Dirk Goldgar]

In

Both the working and not working databases have the
These are the fields in Audit table.
++++++++++++++++
ID
LoginName
MachineName
User
TableName
RecordPrimaryKey
FieldName
OriginalValue
NewValue
DateTimeStamp
++++++++++++++++

This is the code that it stops on "rs!User = CurrentUser"
++++++++++++++++
Sub WriteAuditUpdate(txtTableName, lngRecordNum, txtFieldName,
OrgValue, CurValue)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_AuditTable")

rs.AddNew
rs!TableName = txtTableName
rs!RecordPrimaryKey = lngRecordNum
rs!FieldName = txtFieldName
rs!LoginName = GetCurrentUserName
rs!MachineName = GetComputerName
rs!User = CurrentUser (it stops here)
rs!OriginalValue = OrgValue
rs!NewValue = CurValue
rs!DateTimeStamp = Now()
rs.Update
rs.Close
db.Close
End Sub
++++++++++++++++

I don't see anything wrong with that code, except that there's no point
in saying

db.Close

when db is a reference set from CurrentDb. The current database won't
be closed.

You might try two things. You might try putting square brackets around
"User", as:

rs![User] = CurrentUser()

just because User is a reserved word and may be misinterpreted.

If you haven't turned off Name AutoCorrect, do that and see if it makes
any difference.