"Operation Not Allowed in this Context" Error 1

[timoteo]

I have some code that allows users to filter a list of street address. It works fine except for when a user makes changes to a record then goes to search for a street. I get error number 3219 "Operation not allowed in this context". And the program stops on the Close method (see code below. Is there a way around this problem?

Thank you in advance for any assistance.

Private Sub txtStrFilter_Change()
'Limits the accounts displayed in dgdMain to those which have street names that begin with _
'the letters keyed in by the user

'For SQL query use to open rsSubMain
Dim strSQL As String

strSQL = "SELECT AcctNum, FName, LName, HouseNum, Fraction, Street, AptNum, City, State, ZipPlus, DoNotMail, UseAltAddress, SendWelcome " & _
"FROM SubMain " & _
"WHERE Street LIKE '" & txtStrFilter.Text & "%' " & _
"ORDER BY Street, HouseNum, Fraction, AptNum, LName, FName"

'Closes then immediatly opens rsSubMain. Each time rsSubMain is opened it will _
'only contain the records where the street name starts with the letters keyed in by the user

With rsSubMain
.Close
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strSQL, conSubscription, , , adCmdText
End With

'Sets dgdMain's datasource equal to the newly reopened recordset
Set dgdMain.DataSource = rsSubMain

'Runs the SetupDataGrids subprocedure to ensure the date in dgdMain in displayed in _
'the proper format
SetupDataGrids

'Run the BindData Subprocedure to ensure the text boxes display the correct data
BindData

End Sub

 

[imterpsfan3]

Could it be you are closing the recordset before you are opening it?

 

[ArtieChoke]

Do you perform an update after the user changes before reusing the recordset for the search? If not, you must cancel the update operation before trying to close it.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[timoteo]

imterpsfan2,

Excellent suggestion, at first I thought that was the problem too. I tried this trick and still got the error:

If rsSubMain.State = 1 Then
rsSubMain.Close
End If

This way the program will only try to close the recordset if it is open.

ArtieChoke,

Thank you for the reply.

I do not perform an update using the Update Method. I allow the user to make changes using a bound datagrid, so I'm not sure if I can cancel the update operation without undoing the user's changes.

What I am trying to do is allow the user to look up an account using the street name by typing the first few letters of the street's name into txtStrFilter. The user then picks the account out of a datagrid and makes changes to the account using the datagrid. Then the user looks up the next account by typing a new street name in txtStrFilter. This is when the error occurs. Interestingly enough, if the user moves to another record before typing anything into txtStrFilter, the error does not occur. I think this is because of what you are saying about the update needing to be completed or canceled before the recordset can be closed.

So I think I need a way to complete the update before attempting to close the recordset. I tried to use the Update Method triggerd by the FieldChangeComplete and the RecordChangeComplete events, but this generates an error.

Any suggestions on how I can get this to work?

 

[timoteo]

I think I found a solution. I used the Update Method for the recordset and trigger it with the LoseFocus Event for the datagrid.


Private Sub dgdMain_LostFocus()
rsSubMain.Update
End Sub

Thanks ArtieChoke for pointing me in the right direction.