Move to next record in a MSAccess ADO Subform using VBA

Question

I get error 2105 when I try to move to next record by using DoCmd.GotoRecord in a MS Access Subform.

In the .accdb file, there is a MainForm with a Subform and a Module, and there is one simple table in SQL Server. That's it.

I think I have simplified the problem as far as possible and if you want to help me you can follow these three simple steps:

In SQL Server:

Create a table and fill with some data (use SSMS):

CREATE TABLE ATable (AColumn INT)
INSERT INTO ATable VALUES (6)
INSERT INTO ATable VALUES (126)
INSERT INTO ATable VALUES (210)
INSERT INTO ATable VALUES (11)

In MS Access 2016

Create a Module and write just one row in it:

Public SQLDB As Object

Create a SubForm, create an OnLoad Event and write this code in it's module:

Dim rsa As Object

Private Sub Form_Load()
  Set rsa = CreateObject("ADODB.Recordset")
End Sub

Public Sub Fill(ByVal sql As String)
  rsa.Open sql, SQLDB
  Set Me.Recordset = rsa
  DoCmd.GoToRecord , , acNext
End Sub

Finally, create a MainForm, put a subform control on it and bind to SubForm, Create an OnLoad Event and write this code in it's module (of course, you have to change the connection string to your environment):

Private Sub Form_Load()
  Set SQLDB = CreateObject("ADODB.Connection")
  SQLDB.Open "Driver={SQL Server Native Client 11.0};Server=YourSQLServer;Database=Yourdatabase;Trusted_Connection=yes;"
  SQLDB.CursorLocation = 3 ' adUseClient
  Me.UO.Form.Fill "ATable"
End Sub

When you open the MainForm you'll get Error 2105 cannot go to specified record when executing the line DoCmd.GotoRecord,,acNext

Why?

It works fine if you put all code in Subform, so I see no reason why it wouldn't work just because the SubForm is "in a subform control".

If you have any other idea how to move cursor forward and backward in a subform's recordset, I'll be glad to hear that. But it's very important that the forms recordsets is ADO.

The code works for me. But I am not using SQLServer backend. I also have never used Recordset property. — June7
Yes, It works fine if you use a .accdb backend (or DAO). I encountered this problem when migrating to SQL Server — andarvi
I use recordset because I know no other way to let SQL Server execute the query (for performance reason, the query is very complex in the real world), and that's one of many reasons why I migrate at all. — andarvi
You can use passthrough queries which are executed in server side. — Krish
Have you checked the order of the load events of both forms? — Wolfgang Kais

andarvi andarvi · Accepted Answer · 2019-10-19T10:04:26

I found the reason. it was because the SubForm control was not active (or "focused") on the MainForm. So the solution is to add a line in Subform's code:

Dim rsa As Object

Private Sub Form_Load()
  Set rsa = CreateObject("ADODB.Recordset")
End Sub

Public Sub Fill(ByVal sql As String)
  rsa.Open sql, SQLDB
  Set Me.Recordset = rsa
  Me.Parent("UO").SetFocus ' <--- New line
  DoCmd.GoToRecord , , acNext
End Sub

Move to next record in a MSAccess ADO Subform using VBA

1 Answers