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?

Solution 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.

Solution 2

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

Solution 3

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].