Using subform's recordset in DMax call?

[Max Moor]

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

 

[Perry]

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

 

[Max Moor]

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)

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

 

[Perry]

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

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)

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

 

[Marshall Barton]

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)

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.

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.

 

[Perry]

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.

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

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)

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

 

[Max Moor]

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)

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.

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.

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