What is the event to trap for a record change? (1 Viewer)

[ST4RCUTTER]

When a user changes from one record to another, I want to perform a check to see if the current record has a related record in another table. What event should I be trapping to execude the code?

 

[pbaldy]

The current event fires when changing records.

 

[ST4RCUTTER]

Does it fire after the new record is loaded or before the new record is loaded?

 

[pbaldy]

From Help:

This event occurs both when a form is opened and whenever the focus leaves one record and moves to another. Microsoft Access runs the Current macro or event procedure before the first or next record is displayed.

Referring to a control's value in the current event will be referring to the value of the upcoming record, not the record being left.

 

[ST4RCUTTER]

Judging by the text you gleaned for the On Current event (which I was aware of but was not sure if this would be the correct event) it appears that it runs before the next record is displayed.

Microsoft Access runs the Current macro or event procedure before the first or next record is displayed.

This sounds to me like it is referring to the original record which is what I am needing to check. I'll try it out and post my results.

 

[pbaldy]

I don't think so, but you can test:

Referring to a control's value in the current event will be referring to the value of the upcoming record, not the record being left.

If you want to check the original record, try one of the update events. If you're trying to validate data before leaving the record, use the before update event and include this to stop the update and stay on the original record:

Cancel = True

 

[ST4RCUTTER]

I can see that some description is in order.

This main form tracks tasks and my problem is that users are creating open tasks with no due dates. Due dates are tracked in a related table, tblDates and viewed/added/modified through a subform. The query that is the recordset for the main form does not have any of this date information so I need to test for a record in the related table before moving to another record. BeforeUpdate wouldn't work because the user is not necessarily updating anything on the main form. They could/should be updating a subform for dates. Make sense?

 

[Banana]

In one of my project where I needed transaction spanning multiple forms, I used Current event, Dirty event, a bookmark and a function to check for conditions.

Here's the code off the top of my head.

In Current event:

If Not Me.Bookmark = CurrentBookmark Then
   CheckCondition(Me, CurrentBookmark)
End If

In Dirty event:

CurrentBookmark = Me.Bookmark

In subform's Dirty event:

Me.Parent.Form.Dirty = True

In CheckCondition function: (Actually, it can be whatever you want to call it; just be consistent)

Public Function CheckCondition(frm As Form, vOldBkmk as Variant) As Boolean

If <condition> Then
   'Do nothing.
   CheckCondition=True
Else
  'Condition wasn't satisfied. Return to the old record
  frm.Bookmark=vOldBkmk
End If

End Function

This is the minimum needed; you may need to add more for your specific situation, presentability, etc. (In my case, I displayed a dialog box giving my user a choice of deleting the edited record, returning to that record, or save the record.)

In your case where you're checking for a Due Date, you could open a recordset based on the query of due dates column, and check if it's .BOF and .EOF, which if it is, move back to the edited record.
HTH.

Note: this is not foolproof and definitely not ACID-complaint, thought it will work generally well proving Access isn't prevented from being executing all the above properly (e.g. three finger salute may result in incomplete records, and it is still up to you to implement checks if so desired.)

 

[gemma-the-husky]

I can see that some description is in order.

This main form tracks tasks and my problem is that users are creating open tasks with no due dates. Due dates are tracked in a related table, tblDates and viewed/added/modified through a subform. The query that is the recordset for the main form does not have any of this date information so I need to test for a record in the related table before moving to another record. BeforeUpdate wouldn't work because the user is not necessarily updating anything on the main form. They could/should be updating a subform for dates. Make sense?

instead of this, you could build your table based on a query that left-joins the related table. if there are no records, then somefield in the related table will be null - so you don't have to check at all - you can instantly get/see the details for the related record

 

[ST4RCUTTER]

gemma-the-husky,

I did think of that as a possible solution. Initially my concern was to have this data present in both the datasource of the parent form as well as have a subform with this same data. Perhaps I should have designed my query for the main form in this way from the start to avoid the use of subforms...still mulling this over as there may be some unforeseen consequences to that. Either way, I'll create a query for this and test it out on the form to see how this works. Thanks for all the feedback guys!