Same code on Win10 does not work on Win11 - ADODB SQL Join (1 Viewer)

[mccarthy995]

Hello friends,

I'm in need of some help regarding a SQL VBA. I have already tested this on a Windows 10 machine which runs, but on Windows 11 for some reason this doesn't want to work. I've been on this for a week now and Its really starting to wind me up.

 Dim wkm, wkmsql
Set wkm = CreateObject("ADODB.Recordset")

   wkmsql = "Select * from InputWalls LEFT JOIN MasterSpecSheetExt ON (InputWalls.Client = MasterSpecSheetExt.Client) AND (InputWalls.KitchenMainCode = MasterSpecSheetExt.CODE) WHERE InputWalls.SITE = """ & Me.SITE.Value & """ AND InputWalls.PLOTNO = """ & Me.PLOTNO.Value & """ AND InputWalls.CLIENT = """ & Me.CLIENT.Value & """"
   wkm.Open wkmsql, CurrentProject.Connection
       
   MsgBox (wkm!Code) 'for debugging < Error line is here

The above joins two tables together, both InputWalls & MasterSpecSheetExt where InputWalls.Client and MasterSpecSheetExt.Client match, (Same for CODE)
And then pulls only the record that the user is looking at. Client Site and PlotNo is displayed on the form they're looking at.

Anyway -
I am executing this code now as I type this on the original machine I wrote this on and it works fine. Its a windows 10 machine with Access 2016.
I also have a windows 11 machine running access 2016 (Both versions from 365) and executing this code shows the following error:

Error:
"Either BOF or EOF is True, Or the current record is deleted. Requested Operation requires a current record."

I have checked VBA > Tools > References on both machines to make sure they match, I have looked on stack overflow for similar issues but I get nothing. I have also compiled my code and made sure that was all up to scratch, but I am running out of ideas.

 

[The_Doc_Man]

First, since this is your first post, Welcome to AWF!

First thing that comes to mind is that after a change of Windows Version, you might have to re-register your library files. I found a reference for Win10 as the target, but I'm betting that something similar will work for Win11.

Redirecting

Look up "reregister windows library files" or "reregister windows libraries" on the web for other ideas.

 

[arnelgp]

with ADODB.Recordset, you need first to test if there is at least a record is fetch:

 Dim wkm, wkmsql
Set wkm = CreateObject("ADODB.Recordset")

   wkmsql = "Select * from InputWalls LEFT JOIN MasterSpecSheetExt ON (InputWalls.Client = MasterSpecSheetExt.Client) AND (InputWalls.KitchenMainCode = MasterSpecSheetExt.CODE) WHERE InputWalls.SITE = """ & Me.SITE.Value & """ AND InputWalls.PLOTNO = """ & Me.PLOTNO.Value & """ AND InputWalls.CLIENT = """ & Me.CLIENT.Value & """"
   wkm.Open wkmsql, CurrentProject.Connection
  
   With wkm
           If Not (.Bof And .Eof) Then
            .MoveLast
            .MoveFirst
               MsgBox (!Code) 'for debugging < Error line is here       
        End If
    End With

 

[Gasman]

I would also Dim the variables correctly, if nothing other than a matter of course?

 

[ByteMyzer]

Gasman is correct. Because you did not explicitly set variable types, wkm and wkmsql are implicitly set as Variant types, and ADODB.Recordset requires an object type (Dim wkm As Object). This would not necessarily cause problems with late binding in earlier versions of Windows, but it has been seen to be a problem in the later versions. I'm not saying that that is necessarily the case here, but it would help to eliminate a possible cause by using the explicit type declaration in the Dim statement.