Run time error 3021- no current record

Question

I want to link the result of a query to a Textbox but I get this error: here is my code:

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT XValue, YValue,Wert FROM tb_DCM_Daten WHERE (FzgID=" & Forms!frm_fahrzeug!ID & " AND Name='" & List2.Value & "')")
Text10.Text = rst!XValue //error in this line

It should be return c.a 20 record

Why do I get this error and how can I solve it?

Cytochrome

Rearrange your code so your SQL Statement is in a string variable, and print it out to the immediate window. That way you'll see if there's anything wrong with it. If nothing obvious, that the output SQL and put it in a query and verify that there is a record that matches your criteria. – Godric

Answer 1

One possible reason for the error is that Name is a reserved word in Access, so you should use

... & " AND [Name]='" & ...

You could also test for rst.EOF before trying to use rst!XValue. That is, to verify whether or not your query is returning at least one row you can add the code

If rst.EOF Then
    MsgBox "The Recordset is empty."
End If

immediately after the .OpenRecordset call. If the Recordset is empty, then you'll need to verify your SQL statement as described by @GregHNZ in his comment above.

Iong

Thank you for your answer. I did it, but it doesn't help :/ – Cytochrome

The eof test is a life-saver. – Linnette

Answer 2

Usually, I would do this. Create a new query in Access , switch to SQL View , Paste my code there and go to Design >> Run.

SELECT XValue, YValue,Wert FROM [tb_DCM_Daten] WHERE [FzgID]=12 AND [Name]='ABC';

if your query syntax is correct you should see the result otherwise error mssg will tell where you are wrong. I used to debug a much more complicated query than yours and this is the way that I've done. If there is still error, maybe you should try

Dim sql as String
sql = "SELECT...."
Set rst = CurrentDb.OpenRecordset(sql)

Another possible reason might be your table name. I just wonder what is your table name exactly ? if your table contains white space you should make it like this [DCM Daten].

Pear

Answer 3

One more thing I like to add that may cause this, is your returning a sets of resultset that has "Reserved word" fields, for example:

Your "Customers" table has field name like the following:

Custnum  | Date | Custname

we know that Date field is a reserved word for most database

so when you get the records using

SELECT * FROM Customers

this will possible return "No Current Record", so instead selecting all fields for that table, just minimize your field selection like this:

SELECT custnum, custname FROM Customers

Statuary

Answer 4

After trying the solutions above to no avail, I found another solution: Yes/No fields in Access tables cannot be Null (See allenbrowne.com/bug-14)

Although my situation was slightly different in that I only got the "No current record." error when running my query using GROUPBY, my query worked after temporary eliminating the Yes/No field.

However, my Yes/No field surprisingly did not contain any Nulls. But, troubleshooting led me to find an associated error that was indeed populating my query result with Null Yes/No values. Fixing that associated error eliminated the Null Yes/No values in my results, thus eliminating this error.

Shied

Answer 5

I got the same error in the following situation:

In my case the recordset returned one record including some fields with Null value.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tbl WHERE (criteria)", dbOpenDynaset)
Textbox1 = rst!field1 'error in this line - Non-Null value
Textbox2 = rst!field2 'Null value
Textbox3 = rst!field1 'Null value

Viewing Locals when rst is opened and before asignments, shows the recordset as I expect it to be. The error is thrown when trying to a asign a value from this recordset.

What fixed this, is ensuring that all fields contained non-Null values.

Just posting this for future seekers.

Supposititious