Recordset Question

[bonekrusher]

Hi All, I have the following recordset count the number of records, if there are no records I get an error. is there a way around this?

(some info: the table "tempMX110" is based on a query "qrytempMX" and sometimes will return no records)

' Count Number of MX-110s
DoCmd.OpenQuery ("qrytempMX")
Dim myconnection As ADODB.Connection
Dim myrecordset As New ADODB.Recordset
Dim howmany As Integer
Set myconnection = CurrentProject.Connection
myrecordset.ActiveConnection = myconnection
myrecordset.Open "tempMX110", , adOpenStatic

myrecordset.MoveFirst

While Not myrecordset.EOF
    myrecordset.MoveNext
Wend

howmany = myrecordset.RecordCount

Forms![StartNew]![Vender_Setup].Form![amount] = howmany

myrecordset.Close
Set myrecordset = Nothing
Set myconnection = Nothing

 

[modest]

yes, you can do two things:

1) don't use recordsets for this - there is already a built-in function that queries for a count. Perform a search on "dcount()"

2) if you want to still use the recordset method --> after you've open the recordset, place the following code

With <recordset variable>
    If .BOF and .EOF Then
        <recordset variable>.Close
        Set <recordset variable> = Nothing
        Exit Sub 'Or you can use goto here
    End If
End With

 

[modest]

Here I've done it for you

' Count Number of MX-110s
DoCmd.OpenQuery ("qrytempMX")
Dim myconnection As ADODB.Connection
Dim myrecordset As New ADODB.Recordset
Dim howmany As Integer
Set myconnection = CurrentProject.Connection
myrecordset.ActiveConnection = myconnection
myrecordset.Open "tempMX110", , adOpenStatic

[COLOR="Red"]With myrecordset
    If .BOF and .EOF Then
        myrecordset.Close
        Set myrecordset = Nothing
        Exit Sub         [COLOR="SeaGreen"]' this may be "Exit Function", or you can use "Goto" here[/COLOR]
    End If
End With

myrecordset.Movelast     [COLOR="seagreen"]' you want to include this - it makes sure all records are loaded[/COLOR][/COLOR]
myrecordset.MoveFirst

While Not myrecordset.EOF
    myrecordset.MoveNext
Wend

howmany = myrecordset.RecordCount

Forms![StartNew]![Vender_Setup].Form![amount] = howmany

myrecordset.Close
Set myrecordset = Nothing
Set myconnection = Nothing

 

[bonekrusher]

Thats the trick!

The latter worked perfect... Thanks for the quik response.

Bones

 

[bonekrusher]

Hi,

I also tried:

DoCmd.OpenQuery ("qryTempMX")
Dim Amt As String
howmany = DCount("*", "tempMX110")
Forms![StartNew]![Vender_Setup].Form![amount] = howmany

And it is cleaner and works great. As you can see I ran a make table query but I counted records in table "tempMX110". I did this becasue DCOunt didnt work with a query. My question is. Can you use Dcount() on a query?

Thanks