I've been having problems getting this to work: We have a search function which creates a recordset in ADO from the given search parameters, through a SQL statement. I want to be able to then navigate throught that recordset via a button click. This is to allow viewing of multiple identical entries in the table. (patients actually, it is a healthcare-oriented database project). When I attempt a rs.MoveNext method on a button_click event I get runtime error 3704 - "Operation is not allowed when object is closed". Argh! How do I keep that recordset open????
email me if you have ideas and I'll send code if needed.
Hey, thanks. I've already opened the recordset, in my Find sub. here is what works.... I can do an initial search, which creates the recordset based on my search parameters and populates the controls on my form with the appropriate information, but if I want to move to the next duplicate, the error in generated, because is says the recordset is closed. I have made the find procedure a seperate sub and so the button_click() event is outside that sub. Here is a sample of the working code:
Dim Conn As New ADODB.Connection
Dim rsPtInfo As New ADODB.Recordset
Dim strCriteria As String, strLastN As String, strStreet As String, strSQL As String, strFields As String
Dim strRsFields As Variant
Dim strID As Double
Dim MultSrch As Integer
Set Conn = CurrentProject.Connection
If Not IsNull(Me![txtID]) Then
strID = Val(Me![txtID])
strSQL = "SELECT tblMailingList.* FROM tblMailingList WHERE tblMailingList.ID=" & strID & " "
rsPtInfo.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
'populate controls if match if found. If no match is found a messagebox pops up.
If Found= True Then 'Found flag is a global boolean which indicates that
rsPtInfo.MoveNext 'a recordset has been created with at least one
'matching patient based on the search.
***This is where the error occurs***
this all works fine until I want to move through the recordset by clicking on a button (cmd_Find).