Prior Record in Datasheet view

[rgreene]

In my db I am tracking different stages for an event. I have a subform that is in datasheet view. The last field (current) is a yes/no check box that says that this entry is the current stage of the process. In one of the fields I have a afterupdate event that says me.current = true. But what I need is to change the previous entry to show that stage's current to be false.
Does that make sense?

Date Event Location Current
02/01/07 Drying Dryer X (when next event added this =false)
02/05/07 Bagged Warehouse X

Up until I enter the 02/05 entry the 02/01 entry was the current stage and I want a check in the current box. But when I enter the 02/05 entry that now becomes my current stage so on the 02/01 event then current = false and on the 02/05 event current = True. I can get the 02/05 event to show current = True but how do I make the 02/01 current =False?

Thanks,
Rick

 

[The_Doc_Man]

OK, here's the rub. In any recordset, the order is unpredictable unless you have sorted it by something that imposes the order you need. Because, you see, internally there is no such thing as previous/next. The set theory on which SQL is based doesn't include that concept. Records in a recordset are unordered. The set is shapeless. Therefore, "previous" and "next" don't really mean anything in most cases.

You ABSOLUTELY cannot do this easily in SQL, though there are a few obscure ways around it. In VBA it is possible, BUT you risk locking the recordset against yourself - which would also shut you down.

You ain't gonna like my suggestion, but you need to hear this. You are attempting to do something that is unnatural to SQL. Rethink your problem so that you don't need to do this in the first place. OK, having pointed out the folly of your ways, never let it be said that I am a TOTAL drudge.

If you MUST do this, you can try some VBA code that opens the recordset via a QUERY that imposes the order you want, then move to the record in question (implied to be the last one from your discussion, so RECSET.MOVELAST work here). Update the field in question. Then do a RECSET.MOVEPREV and update the previous field.

NOTE: Look up recordset operations in order to see the steps involved in editing existing records via recordsets.

 

[rgreene]

Thanks for your honesty that is exactly what I needed to know. Not what I wanted to hear but what I needed to know.

Any suggestions on how to handle my issue. I need to be able to choose a product (or all my products) and report their current stage via report or query.

I'm open to suggestions. My knowledge of access is minimal so I try to keep it simple. or atleast need good directions.

 

[The_Doc_Man]

Structurally speaking, it APPEARS that you might have a denormalized database. If you have multiple events for the same object, you can define one/many relationships between objects and events. Keep the status in the parent (one) side of the relationship. List the events in the child (many) side of the relationship.

Then you have only one status field to update - and it is with THE object record (singular), independent of the number of events involved (zero, one, many).

Keywords to consider include many-to-one, parent/child, and normalization. If you look these up in Help and do some external reading on the subject, it might tell you the right way to go with your project.

 

[rgreene]

Thanks The_Doc_Man I will read up

I appreciate your help and comments

Rick