DMax on mainform referencing subform

[crossy5575]

Hi I have a form - purchase, and a subform purchasedetailds

they are linked with a field "puid"

I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.

I used the control source code:
=DMax("[forms]![purchase]![purchasedetailds]![pdID]","[forms]![purchase]![purchasedetailds]","[Forms]![purchse]![purchasedetailds]![puid]=" & [Forms]![purchase]![puid])

and the simpler:
=DMax("purchasedetailds]![pdID]","[purchasedetailds]","[purchasedetailds]![puid]=" &[puid])

and i get the #error in the box can any one see what i am doing wrong?
thanks
Simon

 

[spikepl]

Read the documentation for DMAX

 

[Solo712]

Hi I have a form - purchase, and a subform purchasedetailds

they are linked with a field "puid"

I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.

I used the control source code:
=DMax("[forms]![purchase]![purchasedetailds]![pdID]","[forms]![purchase]![purchasedetailds]","[Forms]![purchse]![purchasedetailds]![puid]=" & [Forms]![purchase]![puid])

and the simpler:
=DMax("purchasedetailds]![pdID]","[purchasedetailds]","[purchasedetailds]![puid]=" &[puid])

and i get the #error in the box can any one see what i am doing wrong?
thanks
Simon

DMax is a function which operates on tables, not forms or subforms. The DMax function on "pdid" will give you the same result whether it called by a form's or a subform's module.

Best,
Jiri

 

[crossy5575]

thanks Jiri - how then would you suggest I managed to get the largest number of Pdid on the subform? (which is a set of filtered records? can be VBA or expression)

 

[MarkK]

get the largest number of Pdid on the subform

Does the user have full control to apply filters and sorts to the data in the subform? In a worst case you could enumerate the records in a clone of the subform's recordset. You might also be able to use the filter applied to the subform in the criteria of a DMax()

First of all I would write a public function or property ON THE SUBFROM, since this value you are seeking is ultimately exposed by the subform, and will require repeated references too the subform, so write it on the subform.

Public Function MaxIDFiltered() as long
    MaxIDFiltered = DMax("pdid", "yourtable", me.filter)
End Function

But this will fail if the recordsource of the subform changes any field names, or joins any tables, or calculates fields, or anything like that. A more difficult, slow, but flexible solution would be with a cloned recordset .. .

public function MaxIDFiltered() as long
   dim max as long
[COLOR="Green"]   'grab a clone of the recordset[/COLOR]
   with me.recordsetclone
[COLOR="Green"]      'check every record[/COLOR]
      do while not .eof
[COLOR="Green"]         'compare max to current pdid and keep the larger number[/COLOR]
         If .fields("pdid") > max then max = .Fields("pdid")
         .movenext
      loop
   end with
[COLOR="Green"]   'assign to function[/COLOR]
   MaxIDFiltered = max
End Function

So now from a mainform you can just do . . .

Me.tbSubformMaxID = Me.fSubformName.Form.MaxIDFiltered

. . . but any consumer that can reference the subform has one-line-of-code access to that MaxID method exposed by the form.

 

[Solo712]

thanks Jiri - how then would you suggest I managed to get the largest number of Pdid on the subform? (which is a set of filtered records? can be VBA or expression)

There is a number of events that you can use, depending when you want to poll the "pdid" value. If the subform changes the "pdid" I would suggest using the sibform's after update event to do the DMax and display the textbox.

Best,
Jiri