Using subform's recordset in DMax call?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Max Moor - 21 May 2007 22:40 GMT

Hi All,
     I have a subform (continuous forms) with a number of records in it.
Each record includes a "SortOrder" field used, as you might guess, to sort
the records. When a new record is added, I want to assign (Maximum Current
SortOrder + 1) to the SortOrder of the new record.
     I added AfterInsert event code, thinking I'd do a DMax call on the
form's recordset to get the current Max value, but I'm not specifying the
recordset in the call correctly. I have:

DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")

(Note that "fsub_Subform" is the name of the subform control on the main
form)

     Can someone straighten me out?

Thanks, Max

Reply to this Message

Perry - 21 May 2007 22:56 GMT

> DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")

If SortOrder is a field in targettable, change the above syntac to read:

DMax("[SortOrder]", "MyTable")
(whereby MyTable is the name of the table hosting the field SortOrder)

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> Hi All,
> I have a subform (continuous forms) with a number of records in it.
[quoted text clipped - 14 lines]
>
> Thanks, Max

Reply to this Message

Max Moor - 21 May 2007 23:43 GMT

"Perry" <drumper@gmail.com> wrote in news:C6F3687B-6947-49A6-A932-
43C1733C5179@microsoft.com:

>> DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")
>
[quoted text clipped - 6 lines]
> Krgrds,
> Perry

Hi Perry,
With a criteria string added, I certainly could do the lookup in the
underlying table. I think that accessing the records via the subform's
recordset should be doable with the right syntax, though. I'd like to learn
what that is.

Max

Reply to this Message

Perry - 21 May 2007 23:55 GMT

> think that accessing the records via the subform's
> recordset should be doable with the right syntax, though.

It's always safer to check in the underlying table...
Especially in a split (front-/backend) database

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

> "Perry" <drumper@gmail.com> wrote in news:C6F3687B-6947-49A6-A932-
> 43C1733C5179@microsoft.com:
[quoted text clipped - 18 lines]
>
> Max

Reply to this Message

Perry - 22 May 2007 00:58 GMT

Look at the contribution in the other thread:

You can only DMax() against a field in a table (query) object

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

>> think that accessing the records via the subform's
>> recordset should be doable with the right syntax, though.
[quoted text clipped - 32 lines]
>>
>> Max

Reply to this Message

Marshall Barton - 22 May 2007 00:45 GMT

>>> DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")
>>
[quoted text clipped - 7 lines]
>recordset should be doable with the right syntax, though. I'd like to learn
>what that is.

DMax can not search a form's (or any other) recordset.
Since the form's record source is sorted, you could try
using the form's recordset's last record to get the highest
value:
    With Me.fsub_Subform.Form.RecordsetClone
        .MoveLast
        newmax = !SortOrder
    End With
But, I advise against doing this because the largest value
might not be in the form's recordset if the form is filtered
in any way.

Use the DMax, but it should be in the subform's BeforeInsert
event.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Max Moor - 22 May 2007 19:37 GMT

>>>> DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")
>>>
[quoted text clipped - 24 lines]
> Use the DMax, but it should be in the subform's BeforeInsert
> event.

Hi Marshall and Perry,
Okay. I'ver gone back to the ulderlying table. I'd have sworn there
was a way to do that with DMax, but this isn't the first time I've gotten
off on a false belief.

Thanks for the help,
Max

Reply to this Message