need to update recordset for a function to calculate correctly - clarified and continued

[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 calculation SHOULD include the record i am currently editing

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.
for example:
- the record set contains in [PrintOrder] values: 2, 3, and 5 (in 3
records)
- the next record to be entered (the NEW RECORD) ought to have,
[txtPrintOrder].default value of 6
- once i start typing in the NEW RECORD, a new "NEW RECORD" is
generated; the default value for [txtPrintOrder] should be 7; HOWEVER, what
i get is another 6
- once i start typing in that new NEW RECORD, a new NEW RECORD is
generated with a [txtPrintOrder]value of 7; by now, it ought to be 8
- I'm sort of doing a reverse leap-frog (i get values,for the above
example of 2, 3, 5, 6, 6, 7, 7, 8...

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

 

[Marshall Barton]

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 calculation SHOULD include the record i am currently editing

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.
for example:
- the record set contains in [PrintOrder] values: 2, 3, and 5 (in 3
records)
- the next record to be entered (the NEW RECORD) ought to have,
[txtPrintOrder].default value of 6
- once i start typing in the NEW RECORD, a new "NEW RECORD" is
generated; the default value for [txtPrintOrder] should be 7; HOWEVER, what
i get is another 6
- once i start typing in that new NEW RECORD, a new NEW RECORD is
generated with a [txtPrintOrder]value of 7; by now, it ought to be 8
- I'm sort of doing a reverse leap-frog (i get values,for the above
example of 2, 3, 5, 6, 6, 7, 7, 8...

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

No actually, it SHOULD NOT do what you expect.

The next new record displays the same value as the current
new record because of the way the DefaulValue works. The
DefaultValue is calculated at the time the first character
is entered in the new record. The next new record will
diaplay the same value because no character has been entered
yet. When the current new record is saved and you navigate
to the next new record, the DefaultValue for this new record
will be calculated and displayed correctly.

The bigger problem is that if you ever have two users
creating a new record they can both get the same number. If
one user takes a long time between typing the firsr
character and the time when the new record is saved, then a
second user can start and save a different new record during
that time lag. You can avoid this entire set of problems by
using a line of code in the form's BeforeUpdate event:

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

The BeforeUpdate event fires just before the record is saved
so there is pactically no chance that another user can
create another new record in the extremely short time
interval.

The only issue you might complain about is that the new
record will not display its number until it is saved, but
everything should work correctly.

 

[Mark Kubicki]

the field is an editable field that lets the end user reorder the printing
of the recordset
so, while I learned allot from what you told me, it won't be quite
appropriate for this application;

if the {PrintOrder] isn't updated till the after update event, chances are
that the user will have already filled a value in, and this line of code
will overwrite the entry.

I thought that I might do something with the dirty event, but then again,
the dirty may happen when the user is entering a value in [PrintOrder] of
the newly created record...

(there's more than one way to skin a problem,we'll need to just keep trying)
-mark

--------------------------------------------------------------------------------

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 calculation SHOULD include the record i am currently editing

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.
for example:
- the record set contains in [PrintOrder] values: 2, 3, and 5 (in
3
records)
- the next record to be entered (the NEW RECORD) ought to have,
[txtPrintOrder].default value of 6
- once i start typing in the NEW RECORD, a new "NEW RECORD" is
generated; the default value for [txtPrintOrder] should be 7; HOWEVER,
what
i get is another 6
- once i start typing in that new NEW RECORD, a new NEW RECORD is
generated with a [txtPrintOrder]value of 7; by now, it ought to be 8
- I'm sort of doing a reverse leap-frog (i get values,for the
above
example of 2, 3, 5, 6, 6, 7, 7, 8...

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

No actually, it SHOULD NOT do what you expect.

The next new record displays the same value as the current
new record because of the way the DefaulValue works. The
DefaultValue is calculated at the time the first character
is entered in the new record. The next new record will
diaplay the same value because no character has been entered
yet. When the current new record is saved and you navigate
to the next new record, the DefaultValue for this new record
will be calculated and displayed correctly.

The bigger problem is that if you ever have two users
creating a new record they can both get the same number. If
one user takes a long time between typing the firsr
character and the time when the new record is saved, then a
second user can start and save a different new record during
that time lag. You can avoid this entire set of problems by
using a line of code in the form's BeforeUpdate event:

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

The BeforeUpdate event fires just before the record is saved
so there is pactically no chance that another user can
create another new record in the extremely short time
interval.

The only issue you might complain about is that the new
record will not display its number until it is saved, but
everything should work correctly.

 

[Marshall Barton]

the field is an editable field that lets the end user reorder the printing
of the recordset
so, while I learned allot from what you told me, it won't be quite
appropriate for this application;

if the {PrintOrder] isn't updated till the after update event, chances are
that the user will have already filled a value in, and this line of code
will overwrite the entry.

I thought that I might do something with the dirty event, but then again,
the dirty may happen when the user is entering a value in [PrintOrder] of
the newly created record...

If you won't ever have two users doing this, you can still
use your DefaultValue approach. Just don't expect the next
new record to display the new number until something is
entered in that record.

What kind of values are people using for the print sorting?
Are they supposed to be sequential? Are they integers,
floating point numbers or text?

What mechanism do you have to change the print sorting? If
users can just edit the field however they please, how are
you preventing two records from getting the same value?

Does the form display the records sorted by the PrintOrder
field? Are the records resorted when a user changes a
record's PrintOrder value?

 

[Mark Kubicki]

"currently"

the sort mechanism is real dumb (and that maybe OK) (more likely it's beyond
my self-taught capabilities)
to answer your questions:
- the chances of 2 users editing the record at the same time is very
small (always possible, but immaterial)
- the values used for sorting are integers (typically there will be not
more than 3 to 6 records)
- the records should sort sequentially (although the number series may
have gaps)
- if 2 or more records have the same PrintOrder, I don't really care
which prints first (presumably all of them will print)
- if there are no [PrintOrder] values entered; then presumable the user
doesn't care what order they print in (the whole purpose of this field is to
allow the user the "possibility" of overwriting the sort order if they
choose to...
- if the new record won't display any thing until something is entered
in that record, what happens if the user has entered a [PrintOrder] value
first? will it be over written?
- resorting only happens at the OnCurrent event (when the parent forms
record changes); however, I'm should consider adding a resort command button

-----------------------------------------------------------------------------

the field is an editable field that lets the end user reorder the printing
of the recordset
so, while I learned allot from what you told me, it won't be quite
appropriate for this application;

if the {PrintOrder] isn't updated till the after update event, chances are
that the user will have already filled a value in, and this line of code
will overwrite the entry.

I thought that I might do something with the dirty event, but then again,
the dirty may happen when the user is entering a value in [PrintOrder] of
the newly created record...

If you won't ever have two users doing this, you can still
use your DefaultValue approach. Just don't expect the next
new record to display the new number until something is
entered in that record.

What kind of values are people using for the print sorting?
Are they supposed to be sequential? Are they integers,
floating point numbers or text?

What mechanism do you have to change the print sorting? If
users can just edit the field however they please, how are
you preventing two records from getting the same value?

Does the form display the records sorted by the PrintOrder
field? Are the records resorted when a user changes a
record's PrintOrder value?

 

[Marshall Barton]

"currently"

the sort mechanism is real dumb (and that maybe OK) (more likely it's beyond
my self-taught capabilities)
to answer your questions:
- the chances of 2 users editing the record at the same time is very
small (always possible, but immaterial)
- the values used for sorting are integers (typically there will be not
more than 3 to 6 records)
- the records should sort sequentially (although the number series may
have gaps)
- if 2 or more records have the same PrintOrder, I don't really care
which prints first (presumably all of them will print)
- if there are no [PrintOrder] values entered; then presumable the user
doesn't care what order they print in (the whole purpose of this field is to
allow the user the "possibility" of overwriting the sort order if they
choose to...
- if the new record won't display any thing until something is entered
in that record, what happens if the user has entered a [PrintOrder] value
first? will it be over written?
- resorting only happens at the OnCurrent event (when the parent forms
record changes); however, I'm should consider adding a resort command button

Ok, there are no significant requirements on these numbers.
You can use your DefaultValue approach as long as you can
live with the next new record not displaying the correct
value until the user starts to enter something in the next
new record.

If you want to not see anything until the current new record
is saved. then just check for the number being null before
setting the value in the form's BeforeUpdate event:

If IsNull(PrintOrder) Then
Me.PrintOrder = Nz(DMax("[PrintOrder]", _
"tblInstallationNotes", _
"[Type] = '" & Forms![Spec].Type & "'"), 0) + 1
End If

If you only want to do that for a new record and not all
edited records, then surround the above with:
If Me.NewRecord Then
and
End If