Solved

Move to record without Bookmark

Posted on 2015-01-08
37
116 Views
Last Modified: 2015-01-20
I have a form (unbound) with 2 sub forms (unbound)
First subform is a datasheet (let's call it DS)
Second subform is data entry (let's call it DE)

After I enter data on DE and save the record, form DS needs to be repopulated and the cursor moved back to the record it was on.

Code Snippet:

         Dim rsTmp As ADODB.Recordset
         Dim varBkMrk As Variant
         Dim strCompSQL As String
         Dim IOD As Long

5        On Error GoTo ErrorHandler
         
10       varBkMrk = DS.Recordset.Bookmark
         
15       Set rsTmp = DS.Recordset.Clone
20       rsTmp.Bookmark = varBkMrk
30       strCompSQL = "SELECT * FROM MyView"

35       Set DS.Recordset = OpenSQLRecordset(strCompSQL) 'Function that returns a temp Recordset
         
40       DS.Recordset.Bookmark = varBkMrk

Open in new window


Error returned: Error 3704 (Operation is not allowed when the object is closed.)  Which makes sense because the records in DS are pseudo static

Any ideas on workarounds to get the cursor to move to the correct record?
0
Comment
Question by:Kurt Bergman
  • 16
  • 8
  • 5
  • +2
37 Comments
 
LVL 57
ID: 40538969
1. In the OnCurrent Event, save the key for the current record.   Global variable, hidden text control on the parent, or whatever.

2. After a requery or re-open of the forms recordset, do a FindFirst based on the stored key.

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40538987
PS - your forms ARE bound.  Unbound forms can only show one record at a time unless you hardcode an array.  A bound form has something in the RecordSource property or in rare cases you might create a recordset in code and bind that to the form.  I think you are talking about the master/child links which Access uses to tie a form to a subform or one subform to another.
0
 

Author Comment

by:Kurt Bergman
ID: 40539055
Hi Pat,
These are unbound forms with unbound controls using no linked tables, only population via VBA/ADO from SQL Server Tables.
Master/Child links are not possible with this scenario.

Jim:
First go at it Using ADO Recordset.Find throws no errors but won't move the cursor on the form. Still trying stuff.
0
 

Author Comment

by:Kurt Bergman
ID: 40539084
Pat:
Here is How I populate the unbound form controls.
Control names are the same as the intended field names
15       Set rs = OpenSQLRecordset(strSQL)
         
20       For Each fld In rs.Fields
25          frmFld = fld.Name
30          PopulateControls Me, frmFld, fld.Value
35       Next fld

Open in new window


Procedure that does the grunt work:
Public Sub PopulateControls(FormName As Form, FieldName As String, varValue As Variant)
         Dim ctl As Control

5        On Error GoTo ErrorHandler

10       For Each ctl In FormName.Controls
15          If ctl.Name = FieldName Then
20             ctl = varValue
25          End If
30       Next

35       On Error GoTo 0
40       Exit Sub

45       On Error GoTo 0
50       Exit Sub

ErrorHandler:
         Dim strErrMsg As String
55       strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
         "In procedure: PopulateControls of modForms" & vbCrLf & _
         "Error Line: " & Erl

60       SendError strErrMsg
End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40539125
You shouldn't have to populate the controls one at a time, you should be able to set the form's RecordSource to the  recordset.  Then the form will be "bound" to the recordset.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40539159
@PatHartman
Look at @ConcordCA's history and read through 88 posts at
http://www.experts-exchange.com/Database/MS_Access/Q_28588537.html

You'll get a feel for the landscape involved.
It ain't ordinary by a long stretch.

Nick67
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40539305
After I enter data on DE and save the record
So in the saving of the DE record, you are going to need to a PK value from the DS.
I am thinking that for the subform's Exit event, you might be able to snag that PK and what control had the focus, because that is what you want.
I've played with building global handlers which you may need a global handler for every control on the subform's  LostFocus event.  That's when you'd get the last valid value for the PK and control that had the cursor.
If you are doing stuff on the DE and saving,  you won't have things like ActiveControl or CurrentRecord anymore.

Unbound ADO of course makes it that much more fun.

This is how I get after tossing the focus to the last record in a DAO environment
'are there flange results
Set rs = db.OpenRecordset(Me!subFlangeResults.Form.RecordSource, dbOpenDynaset, dbSeeChanges)
rs.Filter = "SpoolFlangeID = " & Me!subSpoolPartIDs.Form!SpoolFlangeID
Set rs1 = rs.OpenRecordset(dbOpenDynaset, dbSeeChanges)
If rs1.RecordCount <> 0 Then rs1.MoveLast
If rs1.RecordCount = 0 Then
    Me.ResultsID.SetFocus 'Return to main form primary key field
    Exit Sub ' no where for focus to go
End If

Me!subFlangeResults.SetFocus 'Set the focus to the nested subForm
Me!subFlangeResults.Form!SpoolFlangeID.SetFocus 'Set the focus to the subForm primary key field
DoCmd.GoToRecord , , acLast 'Move to the last record
Me.ResultsID.SetFocus 'Return to main form primary key field

Open in new window


Instead of DoCmd.GoToRecord , , acLast 'Move to the last record
You'd be looking for something else.
Normally, I'd do that with a rcordsetclone & bookmarks, but they're unlikely to be available in an ADO environment
If you have a PK value, then an ADO .Seek would be part of it.
But how do you navigate an ADO form?

Since Microsoft Access creates a unique bookmark for each record in a form's recordset when a form is opened, a form's bookmark will not work on another recordset, even when the two recordsets are based on the same table, query, or SQL statement. For example, suppose you open a form bound to the Customers table. If you then open the Customers table by using Visual Basic and use the ADO Seek or DAO Seek method to locate a specific record in the table, you can't set the form's Bookmark property to the current table record. To perform this kind of operation you can use the ADO Find method or DAO Find methods with the form's RecordsetClone property.

But your form isn't Bound in the regular sense of that word, so I don't know if that'll give you any joy.
0
 
LVL 84
ID: 40539957
How do users navigate through the records now? Can you show that code? If you can, we could probably help you to wire up something using the ADO.Seek method and your navigation code.
0
 
LVL 57
ID: 40539979
As Scott and the others have said, if this is truly unbound (form has no recordsource set), then it's up to you to find the record based on the key and populate the controls.

If the form is bound, then you need to move through the forms recordset (not meaning your resetting it to something new, but actually carrying out operations against it - MoveFirst, Find, etc).

It's one or the other.

Jim.
0
 

Author Comment

by:Kurt Bergman
ID: 40540235
Hi Scott,
There are no users yet. I'm in the development phase.
0
 

Author Comment

by:Kurt Bergman
ID: 40540331
:-/
Here is some information that might make things less (or) more muddy.
In the image you see a portion of form DS and below it form DE
Form DS controls have their ControlSource properties entered. Opening an ADO.recordset and making the form's recordset = to the ADO.recordset. Navigating DS provides us the Index for populating form DE.
Form DE is displaying data from multiple tables (Parent and Child tables) and all Controls are unbound. My code walks the recordset and populates the controls as opposed to the cumbersome (MyControl = rs!MyField)
When I perform an update on DE via the Save button I need to replace the recordset on form DS to reflect the changes. Repopulating the data causes the cursor to move to the first record.  From there I need the cursor to move back to the record being displayed on form DE.

Capture.JPG
ADO.Seek doesn't appear to work with non Jet tables. I get:
Error 3251 (Current provider does not support the necessary interface for Index functionality.)
In procedure: UpdateParent of Form_frmSDL_RvwChild
Error Line: 70

According to article You cannot set index property...

There are a few possible reasons for this error
1  .CursorType = adOpenKeyset is not available
2  .CursorLocation = adUseClient MUST be used for data to be returned (discovered this the first day of developing this app)

The recordset is derived from two tables joined to provide the user a datasheet view (from DS). Navigating these records updates form DE.
Here is my procedure.
Private Sub UpdateParent()
         Dim rsTmp As ADODB.Recordset
         Dim strCompSQL As String
         Dim IOD As Long
         Dim InspID As Long
         
5        InspID = Me.InspectionID
         
10       On Error GoTo ErrorHandler
         
15       IOD = CurrentISO.ISOID
         
20       strCompSQL = "SELECT C.CompID, C.ISO_ID, C.GroupText, " & _
                "C.Component, C.Description, C.PipeSize, " & _
                "I.DefectLocation, C.PipeSchedule, I.MeasuredThickness, " & _
                "C.NominalThickness, I.WallLossPercent, I.CaConsumedPercent, " & _
                "I.Reviewed, I.Finding, I.FindingTrackText, " & _
                "i.Defect, C.PipeSpec, I.Location, InspectionID " & _
                "FROM tblISOComponent AS C INNER JOIN " & _
                "tblSDLInspection AS I ON C.CompID = I.CompID " & _
                "WHERE (C.ISO_ID = " & IOD & ") " & _
                "ORDER BY C.GroupID, C.GroupText, CAST(LEFT(SUBSTRING(C.Component, " & _
                "PATINDEX('%[0-9.-]%', C.Component), 8000), PATINDEX('%[^0-9.-]%', " & _
                "SUBSTRING(C.Component, " & _
                "PATINDEX('%[0-9.-]%', C.Component), 8000) + 'X') - 1) AS NUMERIC), C.Component"

25       OpenSQLConnection

30       Set rsTmp = New ADODB.Recordset
         
35       With rsTmp
40          Set .ActiveConnection = rs_cnn
45          .Source = strCompSQL
50          .LockType = adLockOptimistic
55          .CursorType = adOpenDynamic
60          .CursorLocation = adUseClient
65          .Open
70          .index = "InspectionID"
75          .Seek InspID
80       End With

85       Set Forms!frmSDL_Rvw.Form.frmSDL_RvwParent.Form.Recordset = rsTmp
         
90       On Error GoTo 0
95       Exit Sub

ErrorHandler:
         Dim strErrMsg As String
100      strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
         "In procedure: UpdateParent of Form_frmSDL_RvwChild" & vbCrLf & _
         "Error Line: " & Erl

105      SendError strErrMsg
         
End Sub

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40540477
You seem to be fully committed to this design model but why?  Access is a rapid application development tool and as such, does a great deal for you behind the scenes but ONLY if you let it.  Your approach is definitely not the "Access way".  Why even use Access at all?  You are not using Jet/ACE and you are not using bound forms so you are getting no benefit from the RAD environment.  You are only writing lots of code and fighting with the environment at every turn.  You must hate Access by now.

Almost all the apps I develop are linked to SQL Server or some other server-side database and some of them work with tables with row counts in the millions.  I always use linked tables and bound forms.  The key to successfully doing this is using criteria to limit the records returned.  Occasionally, if I have a batch process that requires processing large recordsets, I'll use a pass through query or possibly even a stored procedure.  I also make use of updateable views which seem to improve performance when queries join to lots of lookup tables.
0
 

Author Comment

by:Kurt Bergman
ID: 40540509
Pat,
Please understand I sincerely appreciate your help.  

Per the Client's requirements the app HAS to be in Access with SQL Server back end. There can be NO linked objects.
The app uses password and connection string encryption and Active Directory Services to identify users/permissions.

The app would be built and in use by now if I was using .Net. Corporations often have deeper pockets than logic.

(Not trying to be cocky, just letting you know my level of experience)
I've been doing Access development since version 1 (pre 1.1) so I'm familiar with the 'standard' approaches.
0
 

Author Comment

by:Kurt Bergman
ID: 40540539
BTW...
this cannot be done as a view because the view will change the sort on it's own
strISOSQL = "SELECT * " & _
            "FROM tblISO " & _
            "WHERE UnitID = '" & CurrentISO.UnitID & "' " & _
            "ORDER BY " & _
            "CAST(LEFT(SUBSTRING(UnitID, " & _
            "PATINDEX('%[0-9.-]%', UnitID), 8000), " & _
            "PATINDEX('%[^0-9.-]%', SUBSTRING(UnitID, " & _
            "PATINDEX('%[0-9.-]%', UnitID), 8000) + 'X') -1) AS NUMERIC), " & _
            "CAST(LEFT(SUBSTRING(ISOSystem, " & _
            "PATINDEX('%[0-9.-]%', ISOSystem), 8000), " & _
            "PATINDEX('%[^0-9.-]%', SUBSTRING(ISOSystem, " & _
            "PATINDEX('%[0-9.-]%', ISOSystem), 8000) + 'X') -1) AS NUMERIC)"

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40540567
There can be NO linked objects.
Is that because the client is afraid of a stored password?  There is no other logical reason.  So it is a restriction made without full understanding since you can link the tables without storing the password and provide it when the user logs in.  In fact, I recommend this.  I frequently work in environments where I have savvy users who use Access themselves.  In order to prevent them from simply creating their own Access FE and linking to the SQL Server, we obfuscate their SQL Server login and use a password that they don't know and is different from what they use when logging into Access.  They log into Access and my code links to the SQL Server on their behalf.

In the end, you have to do what the client want regardless of how stupid and wasteful as long as the request isn't illegal.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40540587
I haven't run into that but I rarely sort views anyway.  Access can apply the sort locally when the recordset is returned.  There is not point doing this always when I might want it to be different under different circumstances.
0
 
LVL 84
ID: 40540588
If you can't use SEEK, have you tried using the ADODB.Recordset FIND method? That might work ...
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40540591
@ConcordCA
It almost seems backward from the way you describe it.
You select a record on the subform and it changes everything on the main form.
It is usually the other way around. You navigate a main form and the contents of the subform change.

Now, I do get some of why your organized this way around.
You can't put a subform on a continuous form--I always thought this was an absolute.
It turns out it is not.
You can put a datasheet view form in the form header or form footer of a continuous form.
@Rey Obrero knows the details of that trick.

Now, I know that nothing is bound in design view -- and yet I still see multiple records on the subform.
And I don't think you've created a plethora of unbound controls on that, and you're populating, moving and showing them all dynamically, are you?
So, even done dynamically, that form is bound to data, right?
So can it have a RecordsetClone object, or does that throw an error?

And the error is
Error 3704 (Operation is not allowed when the object is closed.)
Now, for fun, after the last go around, I built a combobox populated by an ADO call to a sproc
Dim dbCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
dbCon.ConnectionString = Forms!tblDummy!ADOConnString.Value 
dbCon.Open
dbCon.CursorLocation = adUseClient
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "QryFREquipType"
    .Parameters.Append .CreateParameter("@GeneralEquipTypeID", adInteger, adParamInput, , 23)
    .ActiveConnection = dbCon
    .NamedParameters = True
    Set rst = .Execute
End With

Set Me.Combo24.Recordset = rst
dbCon.Close
Set dbCon = Nothing
Set cmd = Nothing
Set rst = Nothing

Open in new window


But here I closed out the ADO objects.
I think you are doing that too.
But is that what is generating the error?
What if for the subform, you leave the ADO objects open until they become irrelevant or the form closes?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:Kurt Bergman
ID: 40540620
Hi Scott,
Tried recordset.find... (the app basically said "that's nice" and ignored it.
0
 

Author Comment

by:Kurt Bergman
ID: 40540638
Hey Nick,
Here is the design view of the forms. The form DS has controlsource named where as form DE is completely unbound. Both are based on the same parent and child tables. The form DS is showing all the Components that have inspections. The form DE is for updating data in both the Inspections and Components table.  

Capture.JPG
0
 
LVL 84
ID: 40540639
I'm assuming it's some sort of "Master/Detail" record, much like the new Split form - you select a record in the header, and the details of that record display in the detail section.

Still, the flow would be the same:

1. Modify data in Form DE
2. Store the ID value needed to find the record in Form DS
3. Rebuild the Recordset
4. Bind Form DS to that Recordset

From there, you're working with a typical bound form, and should be able to use the Bookmark method:

Dim rst As ADODB.Recordset
Set rst = Me.Parent.RecordsetClone

rst.Find "ID=" & YourStoredIDValue
If Not (rst.EOF and rst.BOF) Then
  Me.Parent.Bookmark = rst.Bookmark
End If
0
 

Author Comment

by:Kurt Bergman
ID: 40540660
Sometimes it's the 'duh' method that works best:

75       While Forms!frmSDL_Rvw.Form.frmSDL_RvwParent.Form.InspectionID <> InspID
80          Forms!frmSDL_Rvw.Form.frmSDL_RvwParent.Form.Recordset.MoveNext
85       Wend

Open in new window


LOL on to the next brick wall
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40540670
And the error is
Error 3704 (Operation is not allowed when the object is closed.)

So, leave the object open!
The title of the question is Move to record without Bookmark

From what I have seen the only answer to that is DoCmd.GoToRecord with the offset option.
Repopulating the data causes the cursor to move to the first record
You'd have to code to work out the offset.
Likely by opening the same recordset ordered the same way as the subform and walking down it, counting the step until you hit the PK saved from before the update.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40540679
Sometimes it's the 'duh' method that works best:
That would work, but be aware it can be quite clunky in practice.

I have a long continuous form where it is not practical to update a certain value programmatically
So I walk down the form in the manner you suggest and toggle the checkbox
It works.
It's very slow to watch though.
I suppose you could turn on/off repainting.
0
 

Author Comment

by:Kurt Bergman
ID: 40540703
Hi Nick,
I'm using existing data and there is no visible delay. (Got lucky?)

The real problem was my Open Recordset method
 which caused the form's recordset to be closed. By opening the recordset locally it stays open. a.k.a. "oh look! there's a foot, let's shoot it!"

Public Function OpenSQLRecordset(Optional ByVal SQLStatement As String) As ADODB.Recordset

5        On Error GoTo ErrorHandler

10       If Not rs_Temp Is Nothing Then
15          If rs_Temp.State = adStateOpen Then rs_Temp.Close
20       End If

25       If Not OpenSQLRecordset Is Nothing Then
30          If OpenSQLRecordset.State = adStateOpen Then OpenSQLRecordset.Close
35       End If

40       OpenSQLConnection
         
45       Set rs_Temp = New ADODB.Recordset
         
50       With rs_Temp
55          Set .ActiveConnection = rs_cnn
60          .Source = SQLStatement
65          .LockType = adLockOptimistic
70          .CursorType = adOpenDynamic
75          .CursorLocation = adUseClient
80          .Open
85       End With

90       Set OpenSQLRecordset = rs_Temp

95       On Error GoTo 0
100      Exit Function

ErrorHandler:
         Dim strErrMsg As String
105      strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
         "In procedure: OpenSQLRecordset of modForms" & vbCrLf & _
         "Error Line: " & Erl

110      SendError strErrMsg


End Function

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40540736
I had wondered about that
Error 3704 (Operation is not allowed when the object is closed.)
And my combobox and your code in the earlier Q show that you can bind an ADO recordset to an object and then close it.
"oh look! there's a foot, let's shoot it!"
You don't want a whack of orphan open connections hanging around, though, so closing them when you are done with them is both needful and sound.

You just weren't done with that recordset is all :D

Nick67
0
 

Author Comment

by:Kurt Bergman
ID: 40540743
Like this?  ;-)

 
 CloseRecordset rs_Temp

Open in new window


Public Sub CloseRecordset(RecordSet_Name As ADODB.Recordset)
   On Error GoTo ErrorHandler
   
   If Not RecordSet_Name Is Nothing Then
      If RecordSet_Name.State = adStateOpen Then
         RecordSet_Name.Close
         Set RecordSet_Name = Nothing
      End If
   End If

   On Error GoTo 0
   Exit Sub

ErrorHandler:
   Dim strErrMsg As String
   strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") " & vbCrLf & _
   "In procedure: CloseRecordset of modADO" & vbCrLf & _
   "Error Line: " & Erl

   SendError strErrMsg

End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40540755
You (and everyone else here) may know more about the subject than me, but my reading suggests that
.CursorLocation = adUseClient
is something you want to use only when the recordset returned by adUseServer won't perform the operations you need it to do.

Is that correct?
And do you generate different recordset types with other procedures?
0
 

Author Comment

by:Kurt Bergman
ID: 40540813
Hi Nick,
For this application, adUseClient proved to be the only method that would work. Access throws a fit when you attempt certain things without linked objects. There wasn't much information out there so I discovered it via trial and error.

And do you generate different recordset types with other procedures?

Not sure I understand the question
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40540822
If adUseCilent is the only thing working, then it's the only type you generate
Public Function OpenSQLRecordset is only going to generate adUseClient, adOpenDynamic recordsets.
I was wondering if you had another procedure that generated recordsets with differing options.
0
 

Author Comment

by:Kurt Bergman
ID: 40540839
I wrote this one for this app. After 20 years of writing all this stuff out I decided to take the 5 minutes and wrap it in a procedure. I don't know why I didn't do this years ago for all the different recordset types.
They would be very simple to build. All you need is to create a procedure and set the return to be the recordset type desired. Fill the Procedure with the necessary info and return the recordset to the requesting procedure.

I just threw this one together...

Public Function OpenADORS(SQLStatement As String, CursorType As CursorTypeEnum, LockType As LockTypeEnum) As ADODB.Recordset

   Dim cnn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim strSQL As String
   
   
   Set cnn = CurrentProject.Connection
   Set rs = New ADODB.Recordset
   
   rs.Open strSQL, cnn, CursorType, LockType
   
   Set OpenADORS = rs
       
   'Clean up connection and recordset
   rs.Close
   cnn.Close
   Set rs = Nothing
   Set cnn = Nothing

End Function

Open in new window

0
 

Accepted Solution

by:
Kurt Bergman earned 0 total points
ID: 40540876
I feel really stupid at this point... my OpenSQLRecordset procedure was the whole issue. Recordset.Find works as it should when I create the recordset in the same procedure. So, OpenSQLRecordset obviously has it's limitations...
0
 
LVL 57
ID: 40540877
Yes, it works very well<g>.  I have:

Public Sub OpenADORecordset(rstName As ADODB.Recordset, strSQL As String, Optional intCursorType As Integer = adOpenKeyset, Optional intLockType As Integer = adLockOptimistic)

Public Sub OpenADORecordsetOnCnn(cnn As ADODB.Connection, rstName As ADODB.Recordset, strSQL As String, Optional intCursorType As Integer = adOpenKeyset, Optional intLockType As Integer = adLockOptimistic)

Public Sub OpenADORecordsetOnDSN(rstName As ADODB.Recordset, strSQL As String, strDSN As String, Optional intCursorType As Integer = adOpenKeyset, Optional intLockType As Integer = adLockOptimistic)

 But note that your doing extra work with that recordset.  No reason for rs.  Just do:

          ' Opens a recordset on the current JET connection
          ' This is the old call and uses JET as the OLEDB provider.

          Const RoutineName = "OpenADORecordset"

10        On Error GoTo OpenADORecordset_Err

20        Set rstName = New ADODB.Recordset
30        rstName.ActiveConnection = CurrentProject.Connection
40        rstName.Open strSQL, rstName.ActiveConnection, intCursorType, intLockType

OpenADORecordset_Exit:
50        Exit Sub

OpenADORecordset_Err:
60        UnexpectedError ModuleName, RoutineName, Version, Err.Number, Err.Description, _
                          Err.Source, VBA.Erl

Open in new window


But the main reason for the post is the cursor types.  There's a lot of in's and out's to using ADO in Access.  Here are my notes:

' ADO Cursor types (lighest to heavyest)
' adOpenUnspecified -1 Unspecified type of cursor
' adOpenForwardOnly - 0 A forward-only cursor. Cannot move backward.  If you do, recordset is closed and source requiried.
' adOpenStatic - 3 A static copy of a set of records. Additions, changes, or deletions by other users are not visible.
' adOpenKeyset - 1 Static snapshot of PK values.  As you scroll through records, records are fetched.  You can see updates made by other users, but not new records added.
' adOpenDynamic - 2 All changes by other users are visible.  This cursor type however is not supported by the JET OLEDB provider.

' Lock types
' adLockUnspecified - -1 Unspecified type of lock. Clones inherits lock type from the original Recordset.
' adLockReadOnly - 1 Default. Read-only records
' adLockPessimistic - 2 Pessimistic locking, record by record. The provider lock records immediately after editing
' adLockOptimistic - 3 Optimistic locking, record by record. The provider lock records only when calling update
' adLockBatchOptimistic - 4 Optimistic batch updates. Required for batch update mode

' Resulting cursor type based on type requested and lock type for JET OLE DB Provider:
' adOpenForwardOnly/adLockReadOnly - Forwardonly
' adOpenForwardOnly/adLockPessimistic - Keyset
' adOpenForwardOnly/adLockOptimistic - Keyset
' adOpenForwardOnly/adLockBatchOptimistic - Keyset

' adOpenStatic/adLockReadOnly - Static
' adOpenStatic/adLockPessimistic - Keyset
' adOpenStatic/adLockOptimistic - Keyset
' adOpenStatic/adLockBatchOptimistic - Keyset

' adOpenKeyset/adLockReadOnly - Keyset
' adOpenKeyset/adLockPessimistic - Keyset
' adOpenKeyset/adLockOptimistic - Keyset
' adOpenKeyset/adLockBatchOptimistic - Keyset

' adOpenDynamic/adLockReadOnly - Keyset
' adOpenDynamic/adLockPessimistic - Keyset
' adOpenDynamic/adLockOptimistic - Keyset
' adOpenDynamic/adLockBatchOptimistic - Keyset

' NOTE TO ABOVE:
'  Command type of adCmdTableDirect always yields a Keyset cursor.
'  Command type of adCmdText and adCmdTable yield types above
'  Requesting a client side cursor always gives you a Static cursor.
0
 
LVL 57
ID: 40540883
<<I feel really stupid at this point... my OpenSQLRecordset procedure was the whole issue. Recordset.Find works as it should when I create the recordset in the same procedure. So, OpenSQLRecordset obviously has it's limitations... >>

  The idea works fine.  Just whatever is receiving the return needs to persist.

 I prefer declaring in the calling procedure and pass the recordset object as an argument and having xyz procedure work on it rather than grabbing it as a return value.

Jim.
0
 

Author Comment

by:Kurt Bergman
ID: 40540893
Hi Jim,
Thank you for the code examples

I prefer declaring in the calling procedure and pass the recordset object as an argument and having xyz procedure work on it rather than grabbing it as a return value.

Good food for thought
0
 
LVL 84
ID: 40542008
For this application, adUseClient proved to be the only method that would work.
I've used the Form.Recordset method several times, and found very quickly that I had to set "adUseClient" if I wanted things to work.

See this article, which is geared to 2003, but is still relevant for Cursor types:

https://support.microsoft.com/kb/281998/en-us

In specific, the "Requirements for Microsoft Jet". Unless you're using a very specific provider, you must use a client-side cursor to have success with this sort of setup.
0
 

Author Closing Comment

by:Kurt Bergman
ID: 40559340
Needed to create Recordset within procedure in order to keep it open for Recordset.Find
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now