Finding the number of records in a query

[Guest]

Hi there!

I want to restrict my users from running a query if there are records in a
second query. The second query has 2 parameters based on forms,
[Forms]![frmInvBusNums]![InventoryNum] and [Forms]![frmInvBusNums]![BusUnit]

The code I'm trying to run, which is totally wrong looks something like this:

Dim db As DAO.Database
' Dim rst As DAO.Recordset
'
' Set db = CurrentDb
'
'
' set rst=db.QueryDefs "Discrepancy_Report"
' If rst.RecordCount > 0 Then
' MsgBox "There are conflicts"
' Exit Sub
' Else
' do something
' End If

Alternatively, I tried to open the query like this:


' DoCmd.OpenQuery "Discrepancy_Report"
and then somehow find the number of records returned. Will this work or
should I try the solution above?

Thanks for your help!

 

[Stuart McCall]

Hi there!

I want to restrict my users from running a query if there are records in a
second query. The second query has 2 parameters based on forms,
[Forms]![frmInvBusNums]![InventoryNum] and
[Forms]![frmInvBusNums]![BusUnit]

The code I'm trying to run, which is totally wrong looks something like
this:

Dim db As DAO.Database
' Dim rst As DAO.Recordset
'
' Set db = CurrentDb
'
'
' set rst=db.QueryDefs "Discrepancy_Report"
' If rst.RecordCount > 0 Then
' MsgBox "There are conflicts"
' Exit Sub
' Else
' do something
' End If

Alternatively, I tried to open the query like this:


' DoCmd.OpenQuery "Discrepancy_Report"
and then somehow find the number of records returned. Will this work or
should I try the solution above?

Thanks for your help!

Try:

If Dcount("*", "Discrepancy_Report") Then
MsgBox... etc.

 

[John W. Vinson]

Hi there!

I want to restrict my users from running a query if there are records in a
second query. The second query has 2 parameters based on forms,
[Forms]![frmInvBusNums]![InventoryNum] and [Forms]![frmInvBusNums]![BusUnit]

The code I'm trying to run, which is totally wrong looks something like this:

Dim db As DAO.Database
' Dim rst As DAO.Recordset
'
' Set db = CurrentDb
'
'
' set rst=db.QueryDefs "Discrepancy_Report"
' If rst.RecordCount > 0 Then
' MsgBox "There are conflicts"
' Exit Sub
' Else
' do something
' End If

Alternatively, I tried to open the query like this:


' DoCmd.OpenQuery "Discrepancy_Report"
and then somehow find the number of records returned. Will this work or
should I try the solution above?

Thanks for your help!

Simplest would be to just use

If DCount("*", "[Discrepancy_Report]") > 0 Then
<there are conflicts>
Else
<there aren't>
End Sub

It may be more efficient - given that you're just checking to see if there are
any records at all, and apparently don't care HOW many - to open a recordset
and look at its recordcount:

Set rst = db.OpenRecordset("[Discrepancy_Report]", dbOpenSnapshot)
If rst.RecordCount > 0 Then
<etc>
End If
rst.Close
Set rst = Nothing

The record count will be either 0 or 1 immediately after opening - Access
won't populate the rest of the recordset right away.

John W. Vinson [MVP]

 

[Guest]

Hey!

Thanks, that worked just great! Saves me a ton of time and code!

Hi there!

I want to restrict my users from running a query if there are records in a
second query. The second query has 2 parameters based on forms,
[Forms]![frmInvBusNums]![InventoryNum] and [Forms]![frmInvBusNums]![BusUnit]

The code I'm trying to run, which is totally wrong looks something like this:

Dim db As DAO.Database
' Dim rst As DAO.Recordset
'
' Set db = CurrentDb
'
'
' set rst=db.QueryDefs "Discrepancy_Report"
' If rst.RecordCount > 0 Then
' MsgBox "There are conflicts"
' Exit Sub
' Else
' do something
' End If

Alternatively, I tried to open the query like this:


' DoCmd.OpenQuery "Discrepancy_Report"
and then somehow find the number of records returned. Will this work or
should I try the solution above?

Thanks for your help!

Simplest would be to just use

If DCount("*", "[Discrepancy_Report]") > 0 Then
<there are conflicts>
Else
<there aren't>
End Sub

It may be more efficient - given that you're just checking to see if there are
any records at all, and apparently don't care HOW many - to open a recordset
and look at its recordcount:

Set rst = db.OpenRecordset("[Discrepancy_Report]", dbOpenSnapshot)
If rst.RecordCount > 0 Then
<etc>
End If
rst.Close
Set rst = Nothing

The record count will be either 0 or 1 immediately after opening - Access
won't populate the rest of the recordset right away.

John W. Vinson [MVP]