CODEWITHSUNDEEP
sqlvbams-access
David Cady
David Cady

Reputation: 61

No results in record set

I have a temporary table being created from a query. From that table I am selecting the items and placing them in a specific order to display in a listbox. Everything works great until I try to add a where clause to my select statement.

What I'd like to do is, when I run the query that isn't working below, just have the variables return as "0".

I have tried to do an if statement that just makes my variable "0", but I still get the same "No Current Record" error.

The below query it works fine;

  strQuery = "SELECT * FROM " & currentTime & " Order By CVT, CVY, CVW, 
  AdjusterName"

But when I add in a where clause of <=1 then I get a "No Current Record" error. The second query is;

  strQuery = "SELECT * FROM " & currentTime & " WHERE CVT <=1 Order By 
  CVT, CVY, CVW, AdjusterName"

When I run this second query I get a "No Current Record" error. This is where I'd like it to just display a zero instead.

Just in case it's needed, here is the entire function;

    If ClaimTypeCB = "MO" Then
        strQuery = "SELECT * FROM " & currentTime & " WHERE CVT <=1 Order 
        By CVT, CVY, CVW, AdjusterName"
    Else
        strQuery = "SELECT * FROM " & currentTime & " Order By CVT, CVY, 
        CVW, AdjusterName"
    End If
        Set daoQueryResult = db.OpenRecordset(strQuery)

        daoQueryResult.MoveLast
        intRecordCount = daoQueryResult.RecordCount
        daoQueryResult.MoveFirst

    If intRecordCount = 0 Then
        intCVT = 0
    End If

(The above If Statement is where I tried to insert an If statement, but it didn't work, and I still get a "No Current Record" error.)

    If intRecordCount <> 0 Then
        With daoQueryResult
            Do While Not .EOF

                If IsNull(!CVT) Then
                    intCVT = 0
                Else
                    intCVT = !CVT
                End If

                If IsNull(!CVY) Then
                    intCVY = 0
                Else
                    intCVY = !CVY
                End If

                If IsNull(!CVW) Then
                    intCVW = 0
                Else
                    intCVW = (!CVW)
                End If

                AvailAdjList.AddItem !AdjusterName & "," & intCVT & "," & intCVY & "," & intCVW
                .MoveNext
            Loop
        End With
    End If

I really just want CVT to return as zero if it is 0 or 1.

Upvotes: 0

Views: 119

Answers (2)

forpas
forpas

Reputation: 164184

Try this query:

strQuery = "SELECT CVT, CVY, CVW, AdjusterName FROM " & currentTime & " WHERE CVT <= 1 Order By CVT, CVY, CVW, AdjusterName" _
         & " UNION ALL " _
         & "SELECT DISTINCT 0, NULL, NULL, NULL FROM " & currentTime & " WHERE NOT EXISTS " _
         & "(SELECT 1 FROM " & currentTime & " WHERE CVT <= 1)"

It uses UNION ALL so that if the 1st query does not return any rows, the 2nd query will return a row with values: 0, NULL, NULL, NULL
I hope I have no typos.

Upvotes: 1

Andre
Andre

Reputation: 27644

After this line:

Set daoQueryResult = db.OpenRecordset(strQuery)

you need to check for Recordset.EOF

With daoQueryResult
    If .EOF Then
        intRecordCount = 0
    Else
        .MoveLast
        intRecordCount = .RecordCount
        .MoveFirst
    End If
End With

Though from your code you don't seem to really need RecordCount.

You could simply start with Do While Not .EOF

Upvotes: 0

Related Questions