Write to table programmatically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RipperT - 17 Jan 2007 23:35 GMT

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!CellNo.OldValue
rs!Bunk = Me!Bunk.OldValue
rs!DateIn = Date
rs!TimeIn = Time
rs!User = [CurrentUser]
rs.Update

Many thanx,

Rip

Reply to this Message

Marshall Barton - 18 Jan 2007 05:44 GMT

>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
[quoted text clipped - 21 lines]
> 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

Signature

Marsh
MVP [MS Access]

Reply to this Message

RipperT - 18 Jan 2007 09:57 GMT

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

Signature

Ripper T Smith
rippertsmith<nospam>@comcast.net

>
>>I have code that looks like the following in the afterupdate event of a
[quoted text clipped - 43 lines]
> and a time part. You should have:
> rs.DateTimeIn = Now

Reply to this Message

Marshall Barton - 18 Jan 2007 15:30 GMT

>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

RipperT - 19 Jan 2007 03:42 GMT

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

Signature

Ripper T Smith
rippertsmith<nospam>@comcast.net

>
>>I tried setting the index per the help instructions. It also errored as
[quoted text clipped - 9 lines]
> probably defaulting to dbOpenDynaset. In this case you can
> use FindFirst instead of Seek.

Reply to this Message

Marshall Barton - 19 Jan 2007 06:07 GMT

>I did get it to go straight to the table in the back end:
>
[quoted text clipped - 19 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

RipperT - 19 Jan 2007 13:44 GMT

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.

>>I did get it to go straight to the table in the back end:
>>
[quoted text clipped - 64 lines]
> users will just enter stuff willy-nilly, sjut lock the text
> boxes when they come back to enter the timeout.

Reply to this Message