More help with code - Dirk Goldgar?

[Noel]

Hi. Dirk Goldgar helped me produce the following code but
its behaving rather oddly. My original post is at the end
of this one. When I click on the command button that
calls up this code, I get the message "The Microsoft Jet
database engine stopped the process because you and
another user are attempting to change the same data at
the same time." This is not correct as I am in a backup
copy. In any case, when I immediately click on the button
again, the process runs correctly. If I look at the
results immediately after it fails, I see that the first
part of the process has worked - this is the bit where
the existing Checks are cleared using an update query
called "School Addr Labels Clear Checks". So the two
parts of the code dont seem to want to run together. Can
anyone, or Dirk if you read this, see whats wrong? Is
there a more elegant way to clear all existing checks
before running Dirks code? Thanks, Noel


Private Sub cmdCheckFilteredSet_Click()
On Error GoTo Err_cmdCheckFilteredSet_Click
DoCmd.SetWarnings False
Dim stDocName As String

stDocName = "School Addr Labels Clear Checks"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

With Me.RecordsetClone
If .RecordCount = 0 Then Exit Sub
.MoveFirst
Do Until .EOF
.Edit
!PrintLabel = True
.Update
.MoveNext
Loop
End With

Exit_cmdCheckFilteredSet_Click:
Exit Sub

Err_cmdCheckFilteredSet_Click:
MsgBox Err.Description
Resume Exit_cmdCheckFilteredSet_Click

End Sub

Original Post:
I have a form with a Check Box which is used to determine
which records are picked up by a Mail Merge document. The
users simply go through the records and tick the ones
they want to Merge.

This is fine for a small number of records but heres the
question. Assume the user filtered the recordset to show,
say, all records appropriate to a certain County. Say
this produces 200 records. Rather than the user having to
go through all 200 records ticking the boxes, would it be
possible to have a command button that calls up some sort
of process that automatically ticks the Check Box in each
of the records in the filtered set?

 

[Dirk Goldgar]

Hi. Dirk Goldgar helped me produce the following code but
its behaving rather oddly. My original post is at the end
of this one. When I click on the command button that
calls up this code, I get the message "The Microsoft Jet
database engine stopped the process because you and
another user are attempting to change the same data at
the same time." This is not correct as I am in a backup
copy. In any case, when I immediately click on the button
again, the process runs correctly. If I look at the
results immediately after it fails, I see that the first
part of the process has worked - this is the bit where
the existing Checks are cleared using an update query
called "School Addr Labels Clear Checks". So the two
parts of the code dont seem to want to run together. Can
anyone, or Dirk if you read this, see whats wrong? Is
there a more elegant way to clear all existing checks
before running Dirks code? Thanks, Noel


Private Sub cmdCheckFilteredSet_Click()
On Error GoTo Err_cmdCheckFilteredSet_Click
DoCmd.SetWarnings False
Dim stDocName As String

stDocName = "School Addr Labels Clear Checks"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

With Me.RecordsetClone
If .RecordCount = 0 Then Exit Sub
.MoveFirst
Do Until .EOF
.Edit
!PrintLabel = True
.Update
.MoveNext
Loop
End With

Exit_cmdCheckFilteredSet_Click:
Exit Sub

Err_cmdCheckFilteredSet_Click:
MsgBox Err.Description
Resume Exit_cmdCheckFilteredSet_Click

End Sub

Original Post:
I have a form with a Check Box which is used to determine
which records are picked up by a Mail Merge document. The
users simply go through the records and tick the ones
they want to Merge.

This is fine for a small number of records but heres the
question. Assume the user filtered the recordset to show,
say, all records appropriate to a certain County. Say
this produces 200 records. Rather than the user having to
go through all 200 records ticking the boxes, would it be
possible to have a command button that calls up some sort
of process that automatically ticks the Check Box in each
of the records in the filtered set?

Noel -

The "other user" in these situations is almost always you -- often
because you have unsaved changes on the form, and then you change the
same record using code. Do you get the error even if you haven't yet
modified any record using the form?

I don't *think* it has anything to do with your update query as such,
but try this revision of your code:

'----- start of revised code -----
Private Sub cmdCheckFilteredSet_Click()
On Error GoTo Err_cmdCheckFilteredSet_Click

Dim stDocName As String

stDocName = "School Addr Labels Clear Checks"

' Make sure the current record has been saved.
If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

' Clear all "PrintLabel" checks.
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

' Refresh the form's data to reflect the update.
Me.Refresh

' Set the PrintLabel checks for all fields in form's
' current recordset.
With Me.RecordsetClone
If .RecordCount = 0 Then Exit Sub
.MoveFirst
Do Until .EOF
.Edit
!PrintLabel = True
.Update
.MoveNext
Loop
End With

Exit_cmdCheckFilteredSet_Click:
Exit Sub

Err_cmdCheckFilteredSet_Click:
MsgBox Err.Description
Resume Exit_cmdCheckFilteredSet_Click

End Sub
'----- end of revised code -----

 

[Noel]

Hi Dirk. Thanks for the quick reply and for the time
taken to create and explain your revised code. Ive just
tried it and its no better Im afraid. Ive noticed
something that might help. If I start off with no Check
Boxes checked, the process runs perfectly. But if there
is at least one box checked, the process runs as far as
clearing the checks but then stops and presents the
message mentioned previously. Thats why it works every
second time - the first time it clears the checks and the
second time it completes because theyre all cleared.
Perhaps I should remove the Update query from the code
and just tell the users to clear all the checks first,
using a button I have already provided for this purpose.
Thanks again for the help, Noel

 

[Dirk Goldgar]

Hi Dirk. Thanks for the quick reply and for the time
taken to create and explain your revised code. Ive just
tried it and its no better Im afraid. Ive noticed
something that might help. If I start off with no Check
Boxes checked, the process runs perfectly. But if there
is at least one box checked, the process runs as far as
clearing the checks but then stops and presents the
message mentioned previously. Thats why it works every
second time - the first time it clears the checks and the
second time it completes because theyre all cleared.
Perhaps I should remove the Update query from the code
and just tell the users to clear all the checks first,
using a button I have already provided for this purpose.
Thanks again for the help, Noel

Hmm, that's odd. I'd like to ask you to try one more thing, and if that
doesn't work I wonder if you'd mind sending me your database to look at.

First try this:

After your code that executes the query -- that is, after these lines
....

DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

.... insert this line:

DBEngine.Idle dbRefreshCache

See if that makes any difference. If it doesn't, and you're willing to
send me a copy of your database to look at, please make a *cut-down*
copy of your database, containing only the elements necessary to
demonstrate the problem, compact it, and and then zip it to less than
1MB in size (preferably much smaller). You can send it to the address
derived by removing NO SPAM from the reply address of this message.
I'll have a look at it, time permitting.

 

[Noel]

Hi Dirk. Thanks for this. Ive tried your latest
suggestion but still no luck. Ive just sent you a sample
mdb that illustrates the problem, together with some
further comments. Thanks again for the help. Cheers, Noel

 

[Dirk Goldgar]

Hi Dirk. Thanks for this. Ive tried your latest
suggestion but still no luck. Ive just sent you a sample
mdb that illustrates the problem, together with some
further comments. Thanks again for the help. Cheers, Noel

Here, Noel, this works for me:

'---- start of *new* revised code ----

Private Sub cmdCheckFilteredSet_Click()
On Error GoTo Err_cmdCheckFilteredSet_Click

Dim stDocName As String

stDocName = "School Addr Labels Clear Checks"

' Make sure the current record has been saved.
If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

' Clear all "PrintLabel" checks.
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True

' Set the PrintLabel checks for all fields in form's
' current recordset.
With Me.RecordsetClone
.Requery
If .RecordCount = 0 Then Exit Sub
.MoveFirst
Do Until .EOF
.Edit
!PrintLabel = True
.Update
.MoveNext
Loop
End With

Exit_cmdCheckFilteredSet_Click:
Exit Sub

Err_cmdCheckFilteredSet_Click:
MsgBox Err.Description
Resume Exit_cmdCheckFilteredSet_Click

End Sub

'---- end of code ----

I found that none of the things I suggested before had any bearing on
the problem (<sigh>), except that you really do want to make sure the
current record is saved before running the query and update process.
What was really needed was a requery of the recordsetclone, to make sure
that the records it has in its store are up to date after the update
query has been run. The ".Requery" line I added takes care of this.

 

[Noel]

That got it Dirk, it works perfectly now. Thanks for
sticking with me on this one. I do appreciate the time
youve devoted to it. Cheers, Noel