Logging last update for a record

[KevinK]

I have a Master Item form. This displays all the information on an item in
inventory. The header of this form contains 3 filters the first filter sets
the category (china, crystal, pottery, etc.). The next filter sets the
manufacturer and the last filter sets the pattern. In the body of the form I
have 2 fields to log when the current item was last updated. The code I use
is this;
Private Sub Form_AfterUpdate()
On Error GoTo AfterUpdate_Err
'Set bound controls to system date and time
DateModified = Date
TimeModified = Time()
AfterUpdate_End:
Exit Sub
AfterUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume AfterUpdate_End
End Sub
I tried this code in the before update event, but the filters would not
work.
With the current code, when I update a record, it disable going to the next
or previous record and I get an error that the record cannot be saved, even
though it does save the date and time.

 

[Douglas J. Steele]

That code really needs to go into the form's BeforeUpdate event. In essence,
you're creating an infinite loop: you update the record, then change the
value in two of its fields so that it has to be updated again.

As well, you should be storing the combination of Date and Time in a single
field, and populate it with the Now function. In Access, the date data type
is designed to hold both: it's an eight byte floating point number, where
the integer portion represents the date as the number of days relative to 30
Dec, 1899 and the decimal portion represents the time as a fraction of a
day. If you're storing them as two separate fields, in actual fact
DateModified contains the date at midnight, and TimeModified contains the
time on 30 Dec, 1899 (try using the Format function with an explicit yyyy mm
dd hh nn ss to check, if you don't believe me!) Should you have occasion to
only use the Date part or the Time part of the value, use the DateValue or
TimeValue functions respectively.

Perhaps if you describe what problem you were having setting the filters
(and showing how you're setting them, for that matter), we'll be able to
help you overcome the problem you had using the correct event.

 

[KevinK]

The filters in the header are CategoryFilter, which is a combo box that was
wizard created from the Category table. The fields used are CategoryID and
Category.

The MfgFilter was also wizard created and has the fields MfgID, MfgName and
CategoryID. The CategoryID field has a criteria CurCategoryID() which is set
in the global module

VBcode in the AfterUpdate event for the CategoryFilter requeries the
MfgFilter which sets the CategoryID global to list only manufacturers in that
category.

The same is done for the PatternFilter with the MfgFilter.

The PatternFilter then requeries the ItemMasterqry, which is the data behind
the detail portion of the ItemMaster form. The CategoryID, MfgID and
PatternID fields in this query all contain the global for each filter.

Doug does this help?

 

[Douglas J. Steele]

Sorry, no, that doesn't help at all.

You said that "the filters would not work" when you had your code in the
BeforeUpdate event. What does that mean? Did you get an error? If so, what
was the error? If you didn't get an error, what did you get (and what did
you expect to get instead)?

 

[KevinK]

Well Doug

The problem was, I could select a category from the first filter, but the
MfgFilter would be blank.

I just moved the code back to the BeforeUpdate event and now it works. I
probably had a some other error that was unrelated to this problem.

Thanks for your help.