Getting first and last date from records

[reginaldmerritt]

Hello

I'm have a form displaying records from a table holding details of events.

I'm using DFirst and DLast to work out what is the First and Last date.

I have the following code which simply writes this information on a message box after updating the field "Event Date"

Private Sub EventDate_AfterUpdate()
 
MsgBox ("First Date = " & DFirst("EventDate", "TBBookedEvents") & _
        vbNewLine & "Last Date = " & DLast("EventDate", "TBBookedEvents"))
 
End Sub
 

Its a bit hard to explaine but the problem i have is that the code is kinda showing the OldValue rather than the new value just updated.

So if i have the first date as 01/02/11 and then change this to 02/02/11 the message box shows 01/02/11. but then if i change the first date again this time to 03/02/11 the message box shows the 02/02/11.

Strange things a happening, any idea what i need to change to get this to work properly. Any suggestions welcome.

P.S. tried using DMin and DMax instead but had the same issue.

[reginaldmerritt]

Well i suppose it did only take me 4hrs to sovle.

I was getting the same effect on a main form when updating a sub form. I tried all sorts to get this to work. I noticed that when deleting a record on a sub form via a piece of code that runs a delete query that the field i was trying to update updated.

This has to be something to do with focus so something but basically i have to requery the subform which the code is running before running the code. Don't fully understand why this is or why this works. But hey its 4:10am now and i'm just happy i can go to sleep.

Here is my actual code, hope this can be of help to someone else:

Private Sub CalcStartEndDate()
 
'Have to requery this subform for some reason ???
Forms!FRMBookingDetails.BookedEventsSubForm.Form.Requery
 
'Update a field on the main form
Forms!FRMBookingDetails.StartDate = DMin("EventDate", "TBBookedEvents",_
      "[BookingID] = Forms!FRMBookingDetails.BookingID")
 
'Update a field on the main form
Forms!FRMBookingDetails.EndDate = DMax("EventDate", "TBBookedEvents",_
      "[BookingID] = Forms!FRMBookingDetails.BookingID")
 
End Sub
 

[Stewart Ross]

Hi. Problem is that the modified record that is underneath the form's controls is still in edit mode. The changes made have not yet been stored, so at the time you are running your domain aggregates DMax and DMin these pick up the previously-stored values.

The requery method you inserted stores the pending change to the record before requerying the form, hence you find that DMax and DMin work correctly.

A simpler approach is to test the form's Dirty property (i.e. record has been changed but not stored), and reset this property (i.e. save the record before proceeding):

Private Sub CalcStartEndDate() 
 
If Me.Dirty then
  Me.Dirty = False
End If
 
'Update a field on the main form 
Forms!FRMBookingDetails.StartDate = DMin("EventDate", "TBBookedEvents",_ 
      "[BookingID] = Forms!FRMBookingDetails.BookingID") 
 
'Update a field on the main form 
Forms!FRMBookingDetails.EndDate = DMax("EventDate", "TBBookedEvents",_ 
      "[BookingID] = Forms!FRMBookingDetails.BookingID") 
 
End Sub

If this sub is not on the form concerned then you would need to qualify the reference to the Dirty property:

If Forms!FRMBookingDetails.Dirty then
  Forms!FRMBookingDetails.Dirty = False
End If

-Stewart

[reginaldmerritt]

I understand what you mean. Thanks for your input Steward, much appreciated.