ADODB recordset returns Runtime error 3021 Either BOF or EOF is true...

[AndyMb]

Hello,

I have a UNION query in Access that merges two tables - OLD vehicles and NEW vehicles

I have a form in Excel that brings Vehicle data in from that UNION query.

So when a user searches a vehicle reg number it matches the reg and should return other details about the vehicle.

When I search (using the form) for a reg that originated from the OLD vehicles table, it works fine. When I search a reg that originated from the NEW vehicle table I get the error that's in my title.

If I run the UNION query in Access and search for a reg from the NEW table it's there, so I know it exists.

Can anyone help me understand why I get this error?

"OrderBookMerge" is my UNION query

Here is my code in excel:

Private Sub CommandButton5_Click()

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim SearchX As Variant


Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=C:\Data\QCandWarranty.accdb"
Set rs = New ADODB.Recordset
rs.Open "OrderBookMerge", cn, adOpenKeyset, adLockOptimistic, adCmdTable


SearchX = ComboBox1.Value
Debug.Print SearchX
Set rs = cn.Execute("SELECT [WoNo],[LineX],[Cust],[Vehicle],[BOM], [SpecNo], [Chassis], [VehicleUniqueID] " & "FROM [OrderBookMerge]" & "WHERE [Reg] = '" & SearchX & "'")



TextBox1.Text = rs.Fields("Vehicle")
TextBox2.Text = rs.Fields("Cust")
TextBox3.Text = rs.Fields("BOM")
TextBox4.Text = rs.Fields("SpecNo")
TextBox5.Text = rs.Fields("WoNo")
TextBox6.Text = rs.Fields("LineX")
TextBox7.Text = ComboBox1.Value
TextBox8.Text = rs.Fields("Chassis")

End Sub

 

[AndyMb]

Try Like"111-%" and not like "%-4444"

Wow, that worked.

Can you explain what the difference between * and % is and how this might have affected my results?

thank you

 

[Legacy 456155]

I have no idea why. Maybe compatibility? Other dialects of SQL such as T-SQL use % as the wildcard char.

 

[AndyMb]

I have no idea why. Maybe compatibility? Other dialects of SQL such as T-SQL use % as the wildcard char.

Well thanks for helping me get to the bottom of this.

 

[Legacy 456155]

My pleasure. 14 Posts for one character! Have a nice day!

 

[AndyMb]

Ha, it seems that most solutions are often simple ones!

 

[xenou]

Nice catch