Making a count record function work

[Skip Bisconer]

I would like to get two table record count results to two unbound textboxes on a form. My need is to compare record counts on two tables to make sure they are the same before proceeding with a month end process.

I have located a function through this site that is suppose to return a record count of a table however I don't seem to be able to get it to return anything but errors using the click event of a command button. Obviously I don't know how to use this function. Can some one show me the correct way to use this function in a sub routine?

Thanks for looking at my problem

Public Function Table_RecordCount(sTable As String) As Long
    Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset(sTable)
 
    If Not (rs.EOF) Then
        rs.MoveLast
        Table_RecordCount = rs.RecordCount
    End If
 
    Set rs = Nothing
End Function

 

[pbaldy]

What error do you get? How are you calling it? I would include an "Else" to set the return value to zero if the recordset is empty.

 

[Skip Bisconer]

One of the things I tried was caused a variable not defined.

Private Sub Command42_Click()
Dim rs As DAO.Recordset
Set rs = CompletedMonthlyTransaction

Table_RecordCount

End Sub

 

[pbaldy]

Try simply this in the command button:

MsgBox Table_RecordCount "TableName"

 

[Skip Bisconer]

Thanks Paul,

This gives me a Syntax error

 

[pbaldy]

Exactly what is your code now and what line throws the error?

 

[Skip Bisconer]

Sub command42_click()
msgbox Table_RecordCount "CompletedMonthlyTransaction"
'MsgBox Table_RecordCount "TableName"
End Sub

 

[pbaldy]

Sorry, in the message box you'd need parentheses. Try:

MsgBox Table_RecordCount("CompletedMonthlyTransaction")

 

[Skip Bisconer]

Thanks Paul that worked well. Can you tell how I can put this result in a text box? I tried me.textbox.value = Table_RecorcdCount("CompletedMonthlyTransactions") in the before update event and it didn't return anything.

 

[pbaldy]

I just tested that and it worked fine (I tried it behind a button and in the before update event of another control). Can you post a sample?

 

[Skip Bisconer]

It works fine for me on a button but when I use this code on an unbound textbox called CountRecords and open the form there the textbox is blank.
I have attached the images in Design view and Form View. The table has 326 records posted to it at this point.

Private Sub CountRecords_BeforeUpdate(Cancel As Integer)
Me.CountRecords.Value = Table_RecordCount("CompletedMontlyTransaction")
End Sub

 

[pbaldy]

You have it in the update event of the textbox itself, which won't fire until the user enters something in that textbox. If you want the box populated when you open the form, try the load event of the form.

 

[Skip Bisconer]

Thanks Paul, it does the job for me.

 

[pbaldy]

No problem, Skip. Glad we got it working for you.