Error Trapping a Empty Recordset for a Parameter Query

[ssalem]

I am ready to tear my hair out. Something simple seems to be such a pain. I have done alot of research with different ideas but I just keep coming up with problems.

I have a simple query based on a form for the user to put in the start and end dates they want to retreive totals for. I want to error check if an empty recordset is returned.

Not sure what I am doing wrong. This is erroring out on the parameters. I had tried me.OpenRecordset.reccount = 0 but that did not work. When I hovered over it in VB code it displayed my TxtEndDate as the openrecordset.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb

'Get the parameter query
Set qfd = dbs.QueryDefs("Query Total by Month")

' set query parameters
qdf.Parameters("TxtStartDate") = Me.TxtStartDate ' ("TxtStartDate") = Parameter in Query and me.TxtStartDate is the parameter entered on Form
qdf.Parameters("TxtEndDate") = Me.TxtEndDate ' ("TxtEndDate") = Parameter in Query and Me.TxtEndDate is the parameter entered on Form


' Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()

Me.Requery

' Check to see if query returns an empty record set and reset the form

If rst.RecordCount = 0 Then
MsgBox "No records Found for Parameters", vbExclamation
Me.TxtStartDate = "01/01/2010" ' I have a blank record to reset all totals on form to blank and makes the form empty with 1 record in the query to start
Me.TxtEndDate = "01/01/2010"
Me.Requery

Me.TxtStartDate = ""
Me.TxtEndDate = ""
Me.TxtMonth = ""
Me.TxtYear = ""
Exit Sub


End If

[June7]

You want to open a query with parameters that reference form controls for input?

If only purpose of recordset is to verify that records will return for the input criteria, can maybe just use a DLookup or DCount domain aggregate.

If DCount("ID","tablename","TxtStartDate=#" & Me.TxtStartDate & "# AND TxtEndDate=#" & Me.TxtEndDate & "#") = 0 Then
...
End If

[orange]

Are you getting a 0 count even when there are records in the record set?

The standard approach ( I've been told it isn't necessary for tables) that is often used is

...
If rst.Bof =rst.Eof then MsgBox "There are no records in the recordset",vbOkOnly  ' reccount is 0

rst.MoveLast  'needed to get correct count
rst.MoveFirst 'needed to get to start of recordset
Debug.print rst.RecordCount
....

Oooops: I see June7 has posted while I was typing. I agree with her --depends on what you are actually doing. The DCount will work, as will the snippet I've shown.

[ssalem]

Thank you thank you........One of my problems worked out!!! Thank god I still have hair on my head...lol. I used the Dcount() and that did exactly what I wanted it to. I just replaced the query name where the tablename was and a couple other little tweaks within the flow of the sub and now it works!!! HARAYYYYY!!!!!!


Thanks Again June 7