Using subform's recordset in DMax call?

  • Thread starter Thread starter Max Moor
  • Start date Start date
M

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
 
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
 
DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")
Click to expand...

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)
Click to expand...


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
 
think that accessing the records via the subform's
recordset should be doable with the right syntax, though.
Click to expand...

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



Max Moor said:
DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")
Click to expand...

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)
Click to expand...


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
Click to expand...
 
Max said:
Perry said:
DMax("[SortOrder]", "Forms!frmMain!fsub_Subform.Form.Recordset")
Click to expand...

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)
Click to expand...


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.
Click to expand...


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.
 
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



Perry said:
think that accessing the records via the subform's
recordset should be doable with the right syntax, though.
Click to expand...

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



Max Moor said:
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)
Click to expand...


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
Click to expand...
Click to expand...
 
Max said:
Perry said:
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)
Click to expand...


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.
Click to expand...


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.
Click to expand...

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
 
Back
Top