Cannot navigate a form connected using ADODB

A have an Access form and have connected it to an sql server table  using ADODB by setting  the  form's recordset property to the ADO recordset.  The first record comes up fine, but when I navigate to the next record, my ADO recordset property goes to a state of  0.  While the form displays the next record in the set, the form's OnCurrent event won't fire.
What is closing my recordset?  I'm not doing any requering or anything?
Mel BrooksCITOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Not exactly an answer I guess, but a warning. I have tried a few times to use ADODB recordsets instead of DAO recordsets, but never got it work well. Its just too unstable, and there are various small gotchas, where they just perform differently than DAO recordsets.

Try adding a filter or a sort, and you will likely see what I mean.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the code you're using to set the Form's Recordset, and the code you're using to create the Recordset? You have to create that recordset correctly, or you can run into some issues.
Mel BrooksCITOAuthor Commented:
The connection variable and recordset variables are saved in a public module

Public cnContacts as New Adodb.connection
Public rsContacts as  New Adodb.recordset

In the Main form Open Event:
      cnContacts.ConnectionString = strdbConnection
      cn.Contacts.CursorLocation = adUseClient
      cnContacts.OPen

On a combobox after update event:
str = "select * from Contacts where ClientID = " &  ComboboxName
Set frm = Contacts_SubForm.Form
If rsContacts.STATE = 1 Then
    rsContacts.Close
    Set rsContacts = Nothing
End If
rsContacts.Open str, cnContacts, adOpenDynamic, adLockPessimistic
Set frm.Recordset = rsContacts


The subform  populates with the records, but when navigating to the next record , rsContacts state changes to 0 even though  the form displays the data of the next record
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Fabrice LambertConsultingCommented:
Looks like a variable scope issue.
Where are they declared ?

Please, post the full code of your form's open and combobox's after update events, there are not enough informations in those snipets.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you seen this article about using an ADO recordset with an Access form:

https://docs.microsoft.com/en-us/office/vba/access/concepts/activex-data-objects/bind-a-form-to-an-ado-recordset

That article mentions two caveats:

-- The underlying ADO recordset must be updatable via ADO.
-- The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.

Does your recordset adhere to those caveats?
Mel BrooksCITOAuthor Commented:
Due to time constraints on this project,  I rewrote the code so that in essence I'm using an unbound form. When I advance to the next record in my recordset,  I set each control value to the corresponding field value in the recordset.  If data is changed on the form, then  I run a separate routine to update the  selected record by assigning the control values back to the field values.  If there is an add record, then I run that  on a separate routine  and then re-open the recordset to include the new record on the display.
This wasn't the solution I wanted as its tedious and time consuming to write the code for this, but I had already spent hours trying to determine why my initial recordset state kept getting closed even though stepping through the code line by line  did not include anything that even referenced the recordset or connection prior to its state being changed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
re: "This wasn't the solution I wanted as its tedious and time consuming to write the code for this"

Which is why, on your  other post, several of us recommended using linked tables and bound forms.

Dale
Mel BrooksCITOAuthor Commented:
I thank everyone for their comments.   I just want  to mention that my form was originally setup using linked tables, but  we recently found that  a memo field in the table would not take more than 8000 characters even the though the sql column is  set as varchar(max).  We're not sure if this has always been the case or a recent  driver update started causing the problem.
Dale FyeOwner, Developing Solutions LLCCommented:
What SQL Server driver are you using?
Mel BrooksCITOAuthor Commented:
Not totally sure because we have well over 100 machines, mostly Windows 10.  I believe the majority are using SQLNCL11, but some may be using SQLNCL10.  I know that on my development machine I'm using SQLNCL11, and I got the ODBC error when  trying to paste more than 8000 characters into memo field with the ODBC link.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ADODB

From novice to tech pro — start learning today.