How to update a field when another field is updated? (1 Viewer)

[Kendall]

Hi all,

I have a form that is setup to display in data sheet view records returned from a query. Lets say there are two fields, called "QuantityMade" and "StopTime." I want the "StopTime" field to be updated to the value Now() when the "QuantityMade" field is updated.

I first thought to use the OnUpdate() event procedure to run an UPDATE...SET...WHERE statement to set StopTime, but I always get the error "Too Few Parameters: Expected 1." It seems you get that error if you specify a column name that does not exist in the table, but I've checked 1000 times and my column names are correct.

Next I then decided to change the UPDATE...SET...WHERE to a SELECT...FROM...WHERE and open a RecordSet. (Which the JET engine doesn't complain about, so I don't know why JET complained about the UPDATE.) I then try to edit the record set, but when it gets to rs.update, it says that another user is already editing the row. Well, I guess that makes sense because the query used to populate the form would have the record open, and I'm trying to update the record in a different spot. So I understand this behavoir.

So my question is...how the heck can I do this update? I'm probably missing something really obvious, but I can't seem to figure it out.

Maybe I'm going about this the complete wrong way? If there's a better way to go about this, I'm all ears.

Thank you very kindly,

Kendall

 

[John Big Booty]

In your field QuantityMade's After Update event put the following;

Me.StopTime = Now()

 

[Kendall]

You, of course, are right. I added the Text / Label boxes on the form, put in Me.StopTime in the OnUpdate function, and it works nicely.

Thank you!

edit:

Thinking about it, I wonder if not having the StopTime boxes when trying to use the UPDATE...SET...WHERE clause was causing it to fail.

In your field QuantityMade's After Update event put the following;

Me.StopTime = Now()