need to update recordset for a function to calculate correctly

[Mark Kubicki]

I have a function (PrintOrder -see below) that fires as the default value
for a textbox on a continuous subform
(It sets a value of the field based on what entries have been previously
made...)

The problem:
When I start typing in a new record, Access generates a new "new record";
however, the default value for the text box (in the new "new record") is
incorrect .. The function it does not yet take into account the record that
I am currently editing.

I thought that including the update method in my code might help, but it is
not

Any suggestions would be greatly appreciated.
-Mark


Public Function PrintOrder(frm As Access.Form)

'Update the recordset
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tblInstallationNotes", dbOpenDynaset)
Rs.Edit
Rs.Update

vStr = "[Type] = '" & frm.Parent.Type & "'"
varx = DLookup("[Type]", "tblInstallationNotes", vStr)
vLen = Len(Nz(varx))
If vLen > 0 Then
Printorder = DMax("[PrintOrder]", "tblInstallationNotes", "[Type] =
'" & Forms![Spec].Type & "'") + 1
Else
Printorder = 1
End If
End Function

 

[Graham Mandeno]

Hi Mark

You don't want the calculation to include the new record do you? It appears
the code is finding the maximim PrintOrder for the given type and
incrementing it by one, so as to use the new value for the record currently
being edited. The current record doesn't yet have a PrintOrder, so would
not take part in the DMax calculation even if it had been saved!

I believe you should be using your Form_BeforeInsert event procedure for
this. The BeforeInsert event fires the moment a new record starts to be
created, which is usually on the first keystroke in a new record.

Insert this code in that event procedure:

Me![PrintOrder] = Nz( DMax( "[PrintOrder]", "tblInstallationNotes", _
"[Type] = '" & Me.Parent!Type & "'"), 0 ) + 1