Write to table programmatically

[RipperT]

I have code that looks like the following in the afterupdate event of a
text field that programmatically writes a record to a table. For
instance, if an inmate moves into a bunk, the record is filled with an
"in" date and time (as below). When the inmate later moves out of the
bunk, I need to locate that same record and write the date and time to
the "out" fields. How can I locate this record and write to it? I've
tried using .Seek "=" .InmateId.OldValue, but an error says it's not
supported. I am using a table-type recordset with an index, so I don't
understand why.
How do I call up this record and add the data?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblLockHistory")
rs.AddNew
rs!InmateId = Me!InmateId.OldValue
rs!Institution = Me!Institution.OldValue
rs!HousingUnit = Me!HousingUnit.OldValue
rs!CellNo = Me!CellNldValue
rs!Bunk = Me!Bunk.OldValue
rs!DateIn = Date
rs!TimeIn = Time
rs!User = [CurrentUser]
rs.Update

Many thanx,

Rip

 

[Marshall Barton]

I have code that looks like the following in the afterupdate event of a
text field that programmatically writes a record to a table. For
instance, if an inmate moves into a bunk, the record is filled with an
"in" date and time (as below). When the inmate later moves out of the
bunk, I need to locate that same record and write the date and time to
the "out" fields. How can I locate this record and write to it? I've
tried using .Seek "=" .InmateId.OldValue, but an error says it's not
supported. I am using a table-type recordset with an index, so I don't
understand why.
How do I call up this record and add the data?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblLockHistory")
rs.AddNew
rs!InmateId = Me!InmateId.OldValue
rs!Institution = Me!Institution.OldValue
rs!HousingUnit = Me!HousingUnit.OldValue
rs!CellNo = Me!CellNldValue
rs!Bunk = Me!Bunk.OldValue
rs!DateIn = Date
rs!TimeIn = Time
rs!User = [CurrentUser]
rs.Update

According to Help:
"You must set the current index with the Index
property before you use Seek. If the index identifies
a nonunique key field, Seek locates the first record
that satisfies the criteria."

There's supposed to be a comma between the arguments to Seek

Assuming the index name is the same as the field name???

rs.Index = "InmateId"
rs.Seek "=", Me.InmateId.OldValue

I have no idea what that OldValue stuff is all about, but it
sure looks strange.

Note that it is a waste of time and space to use two fields
for date and time. A standard date value has both a date
and a time part. You should have:
rs.DateTimeIn = Now

 

[RipperT]

I tried setting the index per the help instructions. It also errored as not
being supported. I will try it again with the comma.

Thanks for the reply.

Rip

--
Ripper T Smith
rippertsmith<nospam>@comcast.net

I have code that looks like the following in the afterupdate event of a
text field that programmatically writes a record to a table. For
instance, if an inmate moves into a bunk, the record is filled with an
"in" date and time (as below). When the inmate later moves out of the
bunk, I need to locate that same record and write the date and time to
the "out" fields. How can I locate this record and write to it? I've
tried using .Seek "=" .InmateId.OldValue, but an error says it's not
supported. I am using a table-type recordset with an index, so I don't
understand why.
How do I call up this record and add the data?

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblLockHistory")
rs.AddNew
rs!InmateId = Me!InmateId.OldValue
rs!Institution = Me!Institution.OldValue
rs!HousingUnit = Me!HousingUnit.OldValue
rs!CellNo = Me!CellNldValue
rs!Bunk = Me!Bunk.OldValue
rs!DateIn = Date
rs!TimeIn = Time
rs!User = [CurrentUser]
rs.Update

According to Help:
"You must set the current index with the Index
property before you use Seek. If the index identifies
a nonunique key field, Seek locates the first record
that satisfies the criteria."

There's supposed to be a comma between the arguments to Seek

Assuming the index name is the same as the field name???

rs.Index = "InmateId"
rs.Seek "=", Me.InmateId.OldValue

I have no idea what that OldValue stuff is all about, but it
sure looks strange.

Note that it is a waste of time and space to use two fields
for date and time. A standard date value has both a date
and a time part. You should have:
rs.DateTimeIn = Now

 

[Marshall Barton]

I tried setting the index per the help instructions. It also errored as not
being supported. I will try it again with the comma.

I assumed that tblLockHistory is the name of a table in the
same mdb file. If it is a linked table or a query, then you
can not use Seek because it only works with recordsets
opened with dbOpenTable.

For anything other than a local table, the recordset is
probably defaulting to dbOpenDynaset. In this case you can
use FindFirst instead of Seek.

 

[RipperT]

I did get it to go straight to the table in the back end:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("H:\ITS_DEVELOP\ITS_BE.mdb")
Set rs = db.OpenRecordset("tblLockHistory")
rs.Index = "LockHistory"
rs.Seek "=", .InmateId.OldValue, DateTimeIn <> "", DateTimeOut = ""
rs.Edit
rs!DateTimeOut = Now
rs.Update
rs.Close

I created an index called LockHistory that consists of the 3 fields. I don't
get any errors, but the code behaves strangely. It seems to just write to
the first record it finds that meets the first criteria and ignore the 2nd
and 3rd keys, writing the date/time to the field, even if it already
contains data. I'll fiddle around with more test data.

BTW, .OldValue captures the value that was in the field before the user
entered whatever fired the After_Update event. It works nicely. Also, if a
single field can hold both date and time, what do I tell my query to look
for in a WHERE clause?

Thanks for the help,

Rip

 

[Marshall Barton]

I did get it to go straight to the table in the back end:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase("H:\ITS_DEVELOP\ITS_BE.mdb")
Set rs = db.OpenRecordset("tblLockHistory")
rs.Index = "LockHistory"
rs.Seek "=", .InmateId.OldValue, DateTimeIn <> "", DateTimeOut = ""
rs.Edit
rs!DateTimeOut = Now
rs.Update
rs.Close

I created an index called LockHistory that consists of the 3 fields. I don't
get any errors, but the code behaves strangely. It seems to just write to
the first record it finds that meets the first criteria and ignore the 2nd
and 3rd keys, writing the date/time to the field, even if it already
contains data. I'll fiddle around with more test data.

BTW, .OldValue captures the value that was in the field before the user
entered whatever fired the After_Update event. It works nicely. Also, if a
single field can hold both date and time, what do I tell my query to look
for in a WHERE clause?

OK, you can use Seek by going directly to the back end mdb
that way. While there can be a terrific speed advantage to
using Seek, it is only noticible if you are doing a lot of
them on a very large table.

Side note: If you have a linked table in the front end, you
can get the path to the back end mdb from the link:
BEpath = Mid(FEdb.TableDefs!tblLockHistory.Connect, 11)
BEdb = OpenDatabase(Bepath)
so you can avoid hard coding the BE path in your code.

I think the problem with your seek is that the date/time
fields never contain a ZLS (zero length string). They
should be either Null or contain a date/time value. You
also can not specify the comparison for each field in the
index.

With all that in mind I really think you should give up on
Seek, it just isn't flexible enough for all this. Using a
Dynaset type recordset, the code would probably be something
like:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblLockHistory", dbOpenDynaset)
rs.FindFirst "InmateId=" & InmateId.OldValue &_
" And DateTimeIn Is Not Null And DateTimeOut Is Null"
If Not rs.NoMatch Then
rs.Edit
rs!DateTimeOut = Now
rs.Update
Else
MsgBox "Whoops"
End If
rs.Close: Set rs = Nothing
Set db = Nothing


I still thing there's something funny about using OldValue.
Yes that gets the value of the fields before user edits, but
why should these fields be changed? If you're afraid that
users will just enter stuff willy-nilly, sjut lock the text
boxes when they come back to enter the timeout.

 

[RipperT]

I am all for abandoning Seek. The whole while, I'm thinking "there must be
an easier way to do this". I will try FindFirst. Thanx for your time, code
and help.

R.