Updating Grouped Records by Selecting One From Fom

[xweyer]

I have a continuous form based on a query. Here's what I'm seeking to accomplish.

I want the user to be able to scroll through the records and select any one for further processing via a check box. However sometimes a single record is part of a larger group and in that case I wanted to automate the process of selecting all the records within that group.

My original thought was to attach some code to the after update property of the check box that would capture the value of the check box and the id of the "grouped" field and then loop through the recordset assigning the value of the check box to the check boxes of all other records in the same group.

The code below works fine until it gets to the rst.edit command. Then I get "could not update locked by other process" error. Which makes sense I suppose as the form already has the same recordset open.

Is there a safe way around the locking problem or a better means of getting the same desired end result

Dim booRlBk As Boolean
Dim lngTxNum As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb

booRlBk = Me.RlBk
lngTxNum = Me.Transaction_Number

Set rst = dbs.OpenRecordset("Q_Transaction_History", dbOpenDynaset)

If Not (rst.EOF And rst.BOF) Then 'rst is not empty
rst.MoveFirst

Do Until rst.EOF = True

rst.FindNext "[Transaction Number] =" & lngTxNum

If rst.NoMatch Then
'Do Nothing

Else

Do While rst("[Transaction Number]").Value = lngTxNum
rst.Edit
rst("RlBk").Value = Me.RlBk
rst.Update
rst.MoveNext
Loop

End If

rst.MoveNext
Loop

End If

 

[PWise]

dim mydb as database
set mydb=currentdb
mydb.execute "Update tablename set RlBk" = & Me.RlBk & " Where [Transaction Number]=" & Me.Transaction_Number & " And RlBk<> " & Me.RlBk

not tested

 

[xweyer]

Thought I'd post back how I eventually was able to resolve this problem pretty simply.

I tried pwise's suggestion which did suppress the error message but unfortunately not the error itself (the DAO code didn't execute).

Eventually I was able to cobble together a workaround that involved setting the form's record lock property to no locks and then trapping a sequence of related errors (3197, 7787, 7878).

I wasn't totally satisfied with this method because even though this is a single user database I wasn't happy about having to change the record locking level and there was one error I couldn't fully trap ("WRITE CONFLICT The record has been changed by another user ...") although I was able to customize the error message notification screen so that it would not be evident to the user that Access was complaining.

Anyway after all that I got to thinking about the original error and eventually hit on a simple solution. I inserted the following lines immediately after dimensioning the variables and everything ran fine even with the form's record lock property set back to the default "edited record".

If Me.Dirty = True Then
Me.Dirty = False
End If