Error "No Current Record"

[bymarce]

I'm trying to make a list from a recordset with the following code. When I
run it, I get the error "No Current Record" but the query shows several
records. Thanks.
Marcie

'Make mlo list for subject.
Dim DEMLOqd As QueryDef
Set DEMLOqd = CurrentDb.QueryDefs("qryDEMLO")
Dim qryDEMLOsql As String
qryDEMLOsql = "SELECT DISTINCT DataEntry.[MLO] "
qryDEMLOsql = qryDEMLOsql & "FROM DataEntry;"
DEMLOqd.sql = qryDEMLOsql
DEMLOqd.Close
DoCmd.OpenQuery "qrydemlo"

Dim DEMLOdb As Database
Dim DEMLOrs As DAO.Recordset
Dim strMLO As String
Set DEMLOdb = CurrentDb()
Set DEMLOrs = DEMLOdb.OpenRecordset(qryDEMLOsql, dbOpenSnapshot)
If DEMLOrs.BOF = True And DEMLOrs.EOF = True Then
MsgBox "There are no MLO #s listed."
GoTo ErrExit
End If

Do Until DEMLOrs.EOF
strMLO = strMLO & DErs.Fields("MLO") & ", " (this line is
highlighted.)
DEMLOrs.MoveNext
Loop
Debug.Print strMLO

 

[Dirk Goldgar]

I'm trying to make a list from a recordset with the following code. When
I
run it, I get the error "No Current Record" but the query shows several
records. Thanks.
Marcie

'Make mlo list for subject.
Dim DEMLOqd As QueryDef
Set DEMLOqd = CurrentDb.QueryDefs("qryDEMLO")
Dim qryDEMLOsql As String
qryDEMLOsql = "SELECT DISTINCT DataEntry.[MLO] "
qryDEMLOsql = qryDEMLOsql & "FROM DataEntry;"
DEMLOqd.sql = qryDEMLOsql
DEMLOqd.Close
DoCmd.OpenQuery "qrydemlo"

Dim DEMLOdb As Database
Dim DEMLOrs As DAO.Recordset
Dim strMLO As String
Set DEMLOdb = CurrentDb()
Set DEMLOrs = DEMLOdb.OpenRecordset(qryDEMLOsql, dbOpenSnapshot)
If DEMLOrs.BOF = True And DEMLOrs.EOF = True Then
MsgBox "There are no MLO #s listed."
GoTo ErrExit
End If

Do Until DEMLOrs.EOF
strMLO = strMLO & DErs.Fields("MLO") & ", " (this line is
highlighted.)
DEMLOrs.MoveNext
Loop
Debug.Print strMLO

Is that code a copy/paste from your database? I ask because the highlighted
line refers to "DErs.Fields", but the recordset you declared and opened is
named "DEMLOrs".

Incidentally, you are doing a lot more work than you have to do. This would
be simpler:

Dim DEMLOrs As DAO.Recordset
Dim strMLO As String

Set DEMLOrs = CurrentDb.OpenRecordset( _
"SELECT DISTINCT DataEntry.[MLO] FROM DataEntry", _
dbOpenSnapshot)

With DEMLOrs

Do Until .EOF
strMLO = strMLO & ", " & !MLO
.MoveNext
Loop

.Close

End With

If Len(strMLO) = 0 Then
MsgBox "There are no MLO #s listed."
GoTo ErrExit
Else
strMLO = Mid$(strMLO, 3)
Debug.Print strMLO
End If

 

[Beetle]

Your recordset in name DEMLOrs but inside your loop you are
referring to DErs. Is this what is actually in your code, or is
it just a typo in your post?

Also, I'm not sure why you're using QueryDefs and changing the
SQL of a atored query just to ge a list of values. You could simplify
your code a bit by just using a recordset;

Dim strSQL As String, strMLO As String
Dim rs As Recordset

strSQL = "Select Distinct MLO From DataEntry"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
strMLO = ""

With rs
.MoveLast
.MoveFirst
If Not .EOF Then
Do Until .EOF
strText = strText & !MLO & ", "
.MoveNext
Loop
Debug.Print strMLO
Else
MsgBox "There are no MLO #'s"
End If
End With

Set rs = Nothing

 

[bymarce]

Thank you both for catching my typo. That fixed the problem.
Marcie

Your recordset in name DEMLOrs but inside your loop you are
referring to DErs. Is this what is actually in your code, or is
it just a typo in your post?

Also, I'm not sure why you're using QueryDefs and changing the
SQL of a atored query just to ge a list of values. You could simplify
your code a bit by just using a recordset;

Dim strSQL As String, strMLO As String
Dim rs As Recordset

strSQL = "Select Distinct MLO From DataEntry"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
strMLO = ""

With rs
.MoveLast
.MoveFirst
If Not .EOF Then
Do Until .EOF
strText = strText & !MLO & ", "
.MoveNext
Loop
Debug.Print strMLO
Else
MsgBox "There are no MLO #'s"
End If
End With

Set rs = Nothing
--
_________

Sean Bailey

I'm trying to make a list from a recordset with the following code. When I
run it, I get the error "No Current Record" but the query shows several
records. Thanks.
Marcie

'Make mlo list for subject.
Dim DEMLOqd As QueryDef
Set DEMLOqd = CurrentDb.QueryDefs("qryDEMLO")
Dim qryDEMLOsql As String
qryDEMLOsql = "SELECT DISTINCT DataEntry.[MLO] "
qryDEMLOsql = qryDEMLOsql & "FROM DataEntry;"
DEMLOqd.sql = qryDEMLOsql
DEMLOqd.Close
DoCmd.OpenQuery "qrydemlo"

Dim DEMLOdb As Database
Dim DEMLOrs As DAO.Recordset
Dim strMLO As String
Set DEMLOdb = CurrentDb()
Set DEMLOrs = DEMLOdb.OpenRecordset(qryDEMLOsql, dbOpenSnapshot)
If DEMLOrs.BOF = True And DEMLOrs.EOF = True Then
MsgBox "There are no MLO #s listed."
GoTo ErrExit
End If

Do Until DEMLOrs.EOF
strMLO = strMLO & DErs.Fields("MLO") & ", " (this line is
highlighted.)
DEMLOrs.MoveNext
Loop
Debug.Print strMLO

 

[Dirk Goldgar]

Thank you both for catching my typo. That fixed the problem.

Marcie, do you have Option Explicit specified at the top of your module?
That line is automatically added when you have the VB option "Require
Variable Declaration" checked (in the Options dialog of the VB Editor). If
Option Explicit is in effect, it will catch a lot of typos before they have
the chance to confuse you.