AreThereRecords

[groengoen]

I am using this function (AreThereRecords) to check if there are records in a table which I have to start from scratch each time I enter a form to check whether it is empty or not. I use the function a number of times successfully on other tables and it works but on the table I am emptying(PrintBills) it fails. The error is "Compile error: ByRef argument type mismatch". It is looking for a Boolean operator True or False. Can anyone suggest a reason for this and why it doesn't occur in the other instances. Here is some of the code I use (I have highlighted the problem area in Blue":

________________________________________________________________
'Create, declare and instantiate the recordset
Dim R As ADODB.Recordset ' Requests
Dim M As ADODB.Recordset ' Membership
Dim P As ADODB.Recordset ' PrintBills
Dim C As ADODB.Recordset 'Clients

Dim strSQLR As String
Dim strSQLM As String
Dim intAgtID As Integer

Set R = New ADODB.Recordset
Set M = New ADODB.Recordset
Set T = New ADODB.Recordset
Set C = New ADODB.Recordset

Dim SQLText As String

'Set the connection to current project
R.ActiveConnection = CurrentProject.Connection
M.ActiveConnection = CurrentProject.Connection
T.ActiveConnection = CurrentProject.Connection
C.ActiveConnection = CurrentProject.Connection
C.CursorType = adOpenStatic
T.CursorType = adOpenKeyset
T.LockType = adLockOptimistic

'Select unpaid Requests
R.Open "Select * from Requests", Options:=adCmdText
M.Open "Select * from Membership WHERE PaymentID = Null", Options:=adCmdText
T.Open "Select * from tblPrintBills", Options:=adCmdText
C.Open "Select * from Clients", Options:=adCmdText

'Delete all records from the tblPrintBills
Do Until T.EOF
T.Delete
T.MoveNext
Loop
'Call a routine to determine if the recordset contains
'any records
If Not AreThereRecords(R) Then
MsgBox "Recordset Empty...Unable to Proceed"
Else
'Read through Request table
Do Until R.EOF
If IsNull(R("PaymentID")) Then
'Get Clients record
intAgtID = R("AgtID")
C.MoveFirst
C.Find "[AgtID] =" & intAgtID
If Not C.EOF Then

'Check if on tblPrintBills

If Not AreThereRecords(T) Then

'Add record to tbPrintBills
With T
.AddNew
!AgtID = C("AgtID")
!Name = C("Name")
!PrintBill = False
!UnpaidMembership = False
!UnpaidRequests = !UnpaidRequests + 1
.Update
End With
Else

 

[pbaldy]

It might be helpful to see that function, but I'm curious why you check a table you know you just emptied? Also, why use an inefficient loop to delete all records instead of executing an SQL statement:

DELETE * FROM TableName

 

[neileg]

The easiest way to see if there are records is to use DCount(). If it returns 0, there are no records

 

[groengoen]

Thanks guys. The reason I check a table which I have just deleted is that I am trying to add records to a blank table in a loop, which, the first time round will be empty, but subsequently will definitely have records. If the key of the records is the same I just want to update. I will try the more efficient delete and the check for 0 records as suggested. Here is the function

_____________________________________________________

Function AreThereRecords(rstAny As ADODB.Recordset) As Boolean
'Return whether or not there are any rows
AreThereRecords = rstAny.RecordCount
End Function

 

[groengoen]

Thanks Neileg. The DCount() did the trick. I have yet to get the SQL part right. (DELETE * FROM TableName)

 

[groengoen]

The SQL worked fine as well. Muchas Gracias.