Re: need to update recordset for a function to calculate correctly - clarified and continued
- From: "Mark Kubicki" <Mark@xxxxxxxxxxxxxxxxxxx>
- Date: Tue, 16 Jun 2009 17:41:42 -0400
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
--------------------------------------------------------------------------------
"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message
news:lcvf35p1it86c63dj1s8evtdp4ppv5npng@xxxxxxxxxx
Mark Kubicki wrote:
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.
--
Marsh
MVP [MS Access]
.
- Follow-Ups:
- References:
- Prev by Date: IFF Null or NZ
- Next by Date: Dlookup in table won't work
- Previous by thread: Re: need to update recordset for a function to calculate correctly - clarified and continued
- Next by thread: Re: need to update recordset for a function to calculate correctly - clarified and continued
- Index(es):
Relevant Pages
|
