Problems with DMax

[aexley]

I have the following piece of code:

Dim Mpty As Boolean
Dim earliest As Date

Mpty = (IsNull(DMin("[NextCallDate]", "CallDate&quot))

If Mpty = False Then
If Me.NextCallDate.Value < DMin_(&quot;[NextCallDate]&quot;, &quot;CallDate&quot And IsNull_(Me.NextCallDate.Value) = False Then
earliest = Me.NextCallDate
Else
earliest = DMin(&quot;[NextCallDate]&quot;, &quot;CallDate&quot
End If

Forms!FrmSite!NextCallDateSite = earliest
Else
End If

This code is activated by the after update event of the control 'NextCallDate' on a form on the many side of a one-many relationship. What it is supposed to do is this -

1) It checks to see if there are any other values in the NextCallDate field.
2) If there are it compares the current value of the NextCallDate control on my form to these values.
3) It takes the lowest value that isn't a Null and puts it into the appropriate control on another, already open, form.

My problem is this:

If I have three records on the many side and I delete the lowest date value my code doesn’t alter the controls value on the one side to the next lowest value. However if I delete the value exit the form, go back to the empty date put something in and delete it straight away it DOES put the next lowest value in the appropriate control.

I’ve been staring at this bit of code for too long and it’s starting to drive me scatty.

Any help would be much appreciated.

Aexley

QFTD: “Fortune vomits on my eiderdown once again.” – Edmund Blackadder

 

[Steve101]

Just based on a quick assessment of what I think you're doing, have you tried putting the code on the BeforeUpdate event, as opposed to the AfterUpdate event.

Intuitively, I think that it might be more appropriate to call this code on the OnCurrent event. You might only want to dirty the record though if the computed new value of Forms!FrmSite!NextCallDateSite changes; ie. something like:

if Forms!FrmSite!NextCallDateSite <> Earliest then
Forms!FrmSite!NextCallDateSite = Earliest
endif

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[aexley]

When I first wrote the code (it was about three times as long) it was in the BeforeUpdate event and it was this problem that made me look at it again. I have moved it back to BeforeUpdate with no noticeable difference.

Re: '...more appropriate to call this code on the OnCurrent event..' Wouldn't it be a bit inefficient to call the code when it may not be needed? I'm going to give it try anyway but just as your intuition says 'yes' mine says 'no'. :-/

I'll let you know how I get on.

Thanks for your help

aexley

QFTD: 'Intuition - natures way of telling you to duck.' - Me

 

[Steve101]

If efficiency is an issue, then you might want to consider writing some custom code which determines and updates the the minimum call date only when it changes (ie. when a new record is created, or an existing one changed, one deleted), so that major table scans are avoided - I think that I see 3 invocations in your post). How many records in the table - also is the table indexed by the dmin
field.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[aexley]

You'll have to forgive my poor VBA programming skills (self taught). By '...invocations...' do you mean the three DMin functions I've got in there and if so do you think I could replace them with a variable? e.g.

Dim LowestDate As Date

LowestDate = DMin(&quot;[NextCallDate]&quot;, &quot;CallDate&quot

Mpty = (IsNull(LowestDate))

'How many records are in the table?'

CallDate is a query with approx 1000 records. However, there are rarely more than 3 of these records on the many side of my relationship.

Question: Would my DMin function look at all the records in the query or just the 3 or so that are pertinent to the particular record on the one side?

'Is the table indexed by the DMin?'

If by that you mean is there an index on the NextCallDate field in the table that the query is based on, then the answer is yes.

I haven't had the opportunity to try the code in the OnCurrent event yet (am waiting for everyone to go to lunch) but I will do.

aexley