rsClone bookmark & beforeupdate error?

[Dymondjack]

Thanks in advance

I'm running a search field on my form using the dao findfirst/bookmark
method. This worked great during development, and for some reason now I am
getting an error. Here's the problem area code...

'CODE START
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Form_BeforeUpdate
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
'=========================
Me.ctlDateModified = Now()
If Me.NewRecord = True Then
Me.ctlDateCreated = Now()
End If
'=========================
Exit_Form_BeforeUpdate:
Exit Sub
Error_Form_BeforeUpdate:
End Sub


'AND...



Private Sub ctlGetDoc_Exit(Cancel As Integer)
On Error GoTo Error_ctlGetDoc_Exit
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
Dim rs As Recordset
'=========================
Set rs = Me.RecordsetClone
rs.FindFirst "[fldID] = '" & Me.ctlGetDoc.Text & "'"
If rs.NoMatch Then
MsgBox "The Document you have entered does not exist."
Me.ctlGetDoc.Text = ""
Else
Me.ctlGetDoc.Text = ""
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing


'CODE END (left out error handling... redundant)

Here's whats happening... the Me.bookmark line is executing, at which point
the code jumps to Form_BeforeUpdate, where I now recieve 'Error 6 - Overflow'
on the me.ctlDateModified = Now() line (this particular record has not been
changed, so I'm not even sure why its going here...). Then it jumps directly
to the error handler of ctlGetDoc_Exit. When debugging to Resume (one down
from Resume Exit_ctlGetDocExit), the error comes from the Me.Bookmark =
rs.Bookmark line.

This error is 3020: Update or CancelUpdate without Addnew or Edit in module
Form_frmDocuments, procedure ctlGetDoc_Exit

My only thought on this is that the control in the header portion of my form
(ctlGetDoc) is triggering an update to the form, even though it is unbound,
at which point the error is being thrown...

Any ideas?

Thanks much...

 

[Allen Browne]

If Form_BeforeUpdate is being triggered, then the form is dirty. If
cltGetDoc is unbound, then something else (user or procedure) has dirtied
the form.

Suggestions:
1. Explicitly save before any process that requires you to move record.

2. Use the AfterUpdate event procedure of the unbound control. You don't
want to fire the update just because the user visited it. Side benefits:
a) Inappropriate values are trapped first.
b) No need to specify .Text.

3. To clear the control again afterwards, assign Null rather than a
zero-length string. (This approach will work with numbers, dates, anything,
where a zls is not appropriate.)

4. Test if the Value is Null before trying to use the value.

5. Be explicit about the kind of Recordset you want (DAO or ADO or ...)

6. If you use a string variable for the search criteria, it may be easier to
debug, e.g.:
Debug.Print strWhere
Also, if fldID is a Number field (not a Text field), lose the extra quotes.

7. Reset the control last. (Just reduces the amount of stuff that's not yet
done.)

8. There might be other reasons for the document not being found than its
non-existence (e.g. if the form has a Filter applied, or is in DataEntry
mode.)

9. Show the form's Record Selector so you can see at what point the form
goes dirty. This may help you to track down bad code (e.g. if it is dirtied
in Form_Current or Form_AfterUpdate.)

So something like this:

Private Sub ctlGetDoc_AfterUpdate()
'On Error GoTo Error_ctlGetDoc_Exit
Dim rs As DAO.Recordset
Dim strWhere As String

If Me.Dirty Then Me.Dirty = False

Set rs = Me.RecordsetClone
strWhere = "[fldID] = """ & Me.ctlGetDoc & """"
rs.FindFirst strWhere

If rs.NoMatch Then
MsgBox "Document not found."
Else
Me.Bookmark = rs.Bookmark
End If

ExitHandler:
Me.ctlGetDoc = Null
Set rs = Nothing
Exit Sub

Error_ctlGetDoc_Exit:
MsgBox "Error " & err.Number & ": " & Err.Description
Resume ExitHandler
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks in advance

I'm running a search field on my form using the dao findfirst/bookmark
method. This worked great during development, and for some reason now I
am
getting an error. Here's the problem area code...

'CODE START
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Form_BeforeUpdate
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
'=========================
Me.ctlDateModified = Now()
If Me.NewRecord = True Then
Me.ctlDateCreated = Now()
End If
'=========================
Exit_Form_BeforeUpdate:
Exit Sub
Error_Form_BeforeUpdate:
End Sub


'AND...



Private Sub ctlGetDoc_Exit(Cancel As Integer)
On Error GoTo Error_ctlGetDoc_Exit
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
Dim rs As Recordset
'=========================
Set rs = Me.RecordsetClone
rs.FindFirst "[fldID] = '" & Me.ctlGetDoc.Text & "'"
If rs.NoMatch Then
MsgBox "The Document you have entered does not exist."
Me.ctlGetDoc.Text = ""
Else
Me.ctlGetDoc.Text = ""
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing


'CODE END (left out error handling... redundant)

Here's whats happening... the Me.bookmark line is executing, at which
point
the code jumps to Form_BeforeUpdate, where I now recieve 'Error 6 -
Overflow'
on the me.ctlDateModified = Now() line (this particular record has not
been
changed, so I'm not even sure why its going here...). Then it jumps
directly
to the error handler of ctlGetDoc_Exit. When debugging to Resume (one
down
from Resume Exit_ctlGetDocExit), the error comes from the Me.Bookmark =
rs.Bookmark line.

This error is 3020: Update or CancelUpdate without Addnew or Edit in
module
Form_frmDocuments, procedure ctlGetDoc_Exit

My only thought on this is that the control in the header portion of my
form
(ctlGetDoc) is triggering an update to the form, even though it is
unbound,
at which point the error is being thrown...

Any ideas?

Thanks much...

 

[Marshall Barton]

I'm running a search field on my form using the dao findfirst/bookmark
method. This worked great during development, and for some reason now I am
getting an error. Here's the problem area code...

'CODE START
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Form_BeforeUpdate
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
'=========================
Me.ctlDateModified = Now()
If Me.NewRecord = True Then
Me.ctlDateCreated = Now()
End If
'=========================
Exit_Form_BeforeUpdate:
Exit Sub
Error_Form_BeforeUpdate:
End Sub

'AND...

Private Sub ctlGetDoc_Exit(Cancel As Integer)
On Error GoTo Error_ctlGetDoc_Exit
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
Dim rs As Recordset
'=========================
Set rs = Me.RecordsetClone
rs.FindFirst "[fldID] = '" & Me.ctlGetDoc.Text & "'"
If rs.NoMatch Then
MsgBox "The Document you have entered does not exist."
Me.ctlGetDoc.Text = ""
Else
Me.ctlGetDoc.Text = ""
Me.Bookmark = rs.Bookmark
End If

'CODE END (left out error handling... redundant)

Here's whats happening... the Me.bookmark line is executing, at which point
the code jumps to Form_BeforeUpdate, where I now recieve 'Error 6 - Overflow'
on the me.ctlDateModified = Now() line (this particular record has not been
changed, so I'm not even sure why its going here...). Then it jumps directly
to the error handler of ctlGetDoc_Exit. When debugging to Resume (one down
from Resume Exit_ctlGetDocExit), the error comes from the Me.Bookmark =
rs.Bookmark line.

This error is 3020: Update or CancelUpdate without Addnew or Edit in module
Form_frmDocuments, procedure ctlGetDoc_Exit

My only thought on this is that the control in the header portion of my form
(ctlGetDoc) is triggering an update to the form, even though it is unbound,
at which point the error is being thrown...

It sounds like the current record is dirty and fails to save
for some reason.

Or maybe something in the form's Current event is dirtying
the found record, which in turn is triggering another
sequence of events that is getting in the way.

I suggest that you place a breakpoint in every event
procedure to see if there are some things going on that you
are not aware of.

 

[Dymondjack]

Thanks guys...

I will try to explicitly save this record before jumping to the bookmark,
hopefully that will solve the issue.

Marshall: I have tried running breakpoints on all form procedures, and
could not find anything out of order. Regardless, thanks for the tip.

Allen: Thanks also for the tips... most of them I don't think are causing
the issue (fldID is a text string, the ADO reference is off, ect., ect.).
There is one thing in particular you mentioned that I am curious about
though: setting the event on ctlGetDoc to AfterUpdate rather than on the Exit
event.

I seem to recall last time I was invlolved with an access development (I'm
hardly a guru at this), that the control AfterUpdate and BeforeUpdate events
do not trigger on unbound controls, due to the fact that they are not
actually tied to a table (Access 03 I'm running, maybe even 02 last time I
tried that).

In any case I'll play around with these ideas some and hopefully be able to
get this straightened out. As is usually the case, I probably just missed a
line somewhere and haven't found it yet.

If either of you (or anyone else) would like to elaborate more on the
Before/AfterUpdate with unbound controls, I would be interested to hear more.
Though like I said, it was a while ago I tried it, and maybe I'm thinking
form updates rather than control updates.

Thanks!!!

I'm running a search field on my form using the dao findfirst/bookmark
method. This worked great during development, and for some reason now I am
getting an error. Here's the problem area code...

'CODE START
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Error_Form_BeforeUpdate
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
'=========================
Me.ctlDateModified = Now()
If Me.NewRecord = True Then
Me.ctlDateCreated = Now()
End If
'=========================
Exit_Form_BeforeUpdate:
Exit Sub
Error_Form_BeforeUpdate:
End Sub

'AND...

Private Sub ctlGetDoc_Exit(Cancel As Integer)
On Error GoTo Error_ctlGetDoc_Exit
Dim xMsg As String, xBtns As Variant, xTitle As String
'=========================
Dim rs As Recordset
'=========================
Set rs = Me.RecordsetClone
rs.FindFirst "[fldID] = '" & Me.ctlGetDoc.Text & "'"
If rs.NoMatch Then
MsgBox "The Document you have entered does not exist."
Me.ctlGetDoc.Text = ""
Else
Me.ctlGetDoc.Text = ""
Me.Bookmark = rs.Bookmark
End If

'CODE END (left out error handling... redundant)

Here's whats happening... the Me.bookmark line is executing, at which point
the code jumps to Form_BeforeUpdate, where I now recieve 'Error 6 - Overflow'
on the me.ctlDateModified = Now() line (this particular record has not been
changed, so I'm not even sure why its going here...). Then it jumps directly
to the error handler of ctlGetDoc_Exit. When debugging to Resume (one down
from Resume Exit_ctlGetDocExit), the error comes from the Me.Bookmark =
rs.Bookmark line.

This error is 3020: Update or CancelUpdate without Addnew or Edit in module
Form_frmDocuments, procedure ctlGetDoc_Exit

My only thought on this is that the control in the header portion of my form
(ctlGetDoc) is triggering an update to the form, even though it is unbound,
at which point the error is being thrown...

It sounds like the current record is dirty and fails to save
for some reason.

Or maybe something in the form's Current event is dirtying
the found record, which in turn is triggering another
sequence of events that is getting in the way.

I suggest that you place a breakpoint in every event
procedure to see if there are some things going on that you
are not aware of.

 

[Allen Browne]

I seem to recall last time I was invlolved with an access development (I'm
hardly a guru at this), that the control AfterUpdate and BeforeUpdate
events
do not trigger on unbound controls ...

No, that's not right. The Before/AfterUpdate event of an unbound *control*
does work.

The Before/AfterUpdate event of an unbound *form* (thats Form_BeforeUpdate)
doesn't work, which makes sense: Access fires the event just before saving
the record to the table, but an unbound form doesn't save anything to a
table because its unbound.

 

[David W. Fenton]

Dim rs As Recordset
'=========================
Set rs = Me.RecordsetClone
rs.FindFirst "[fldID] = '" & Me.ctlGetDoc.Text & "'"
If rs.NoMatch Then
MsgBox "The Document you have entered does not exist."
Me.ctlGetDoc.Text = ""
Else
Me.ctlGetDoc.Text = ""
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing

This code should be rewritten as:

With Me.RecordsetClone
.FindFirst "[fldID] = '" & Me.ctlGetDoc.Text & "'"
If .NoMatch Then
MsgBox "The Document you have entered does not exist."
Else
Me.Bookmark = .Bookmark
End If
Me.ctlGetDoc = Null
End With

There is no point whatsoever in initializing a recordset variable to
point to an existing recordset that can be used directly.

Likewise, you are setting your control to a zero-length string in
both branches of your If/Then/Else clause. Secondly, you attempted
to use the .Text property of the control, which is available only
when the control has the focus. Last of all, it makes no sense to
use a zero-length string, especially if it's a bound control. Null
is always preferred.

 

[David W. Fenton]

Me.Bookmark = rs.Bookmark

This should always be preceded by:

If Me.Dirty Then Me.Dirty = False

The reason for that is that bookmark navigation does not always
properly save data, and it can lead to lost errors caused by the
save. You should always explicitly save the current record before
you move to a different one.