Record Counter Problem

[ImLost]

Hi - I'm fairly new to Access and have jumped in head first. I do not know much about it, but I am very familiar with the other MS programs. I also don't know anything about visual basic.

I found an article online that described how to put in a custom record counter by using the Oncurrent Event. I have a form with a subform and I'd like have a custom record counter on both, however, when I open a new record, I get an error telling me there is no current record. This is more a problem for the subform, as the message pops up repeatedly until I put something into it to make it a current record.

The error is run-time error '3021': No Current Record.

This is the code that I used (keep in mind that I only copied this and used it, I didn't write it):

Private Sub Form_Current()

' Provide a record counter for using with
' custom navigation buttons (when not using
' Access built in navigation)

Dim rst As DAO.Recordset
Dim lngCount As Long

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

'Show the result of the record count in the text box (txtRecordNo)

Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount

End Sub


Any help is appreciated. Thanks!!

 

[ghudson]

Welcome!

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

Record X of Y

My sample will show you how to test if the current record is new.

 

[Telecom]

I would suggest using the built in access record count. Though if you wanted to go ahead and make a custom recordcount...

You'll need the following...

Under Visual Basic for Access go to Tools then References
Scroll down and select the following two then press ok.
Microsoft DAO (version # here) Object Library
Microsoft ADO Ext. (version # here) for DDL and security

...choose highest version # on the list.

Under Visual Basic for Access go to Debug then Compile...
Once Debug runs without an error save the VB code and try it out.

Code as follows:

Private Sub Form_Current()

'Declare DIMs
Dim RecordSet As New ADODB.RecordSet
Dim lngCount As Long

RecordSet.Open "tablenamehere", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

With RecordSet
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With

'Show the result of the record count in the text box (tboxRecordCounter)

Me.tboxRecordCounter = "Record " & Me.CurrentRecord & " of " & lngCount

End Sub

 

[ImLost]

I did search, but I'm new and didn't really know what I was looking for. Thanks for your help. I'll check this out.

 

[ImLost]

Telecom - I did what you said and had a couple of things happen -

When I went in to put in: RecordSet.Open "tablenamehere", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, the program provided a format to follow. When I didn't follow that format, I would get an error and after entering Debug, this whole line was highlighted.

If I did follow the format, it asked for more information and gave me a Compile Error: Expected: =

The format it gave was: Open([Source], [ActiveConnection], [CursorType As CursorTypeEnum=adOpenUnspecified], [LockType As LockTypeEnum=adLockUnspecified], [Options As Long=-1])

The bolded part is where I got stuck.

Thanks again.

 

[Telecom]

Just want to make sure where I put "tablenamehere" that you entered the exact name of your table.

So if you table is called tblInformation your code statement would be...

RecordSet.Open "tblInformation", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

 

[ImLost]

Yeah I did that. My table name is Service Provider and I put it in with no spaces.

 

[Telecom]

If your table name is "Service Provider" with a space then typing it with no spaces will cause a problem.

I would suggest renaming your table name with no spaces. Having spaces in your control names can cause problems.

I would also suggest using prefixes for your control name. Prefixes can help disguish between other types of controls. See website for an example of a naming convention: http://www.mvps.org/access/general/gen0012.htm.

RecordSet.Open "tblServiceProvider", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

See my attachment for an example:

 

[Pat Hartman]

There is no need to open a new ADO recordset. Besides, if your tables are Jet, DAO is more efficient.

In any event, the RecordSetClone IS a DAO recordset so your code is correct. There is no need to include the .movefirst and .movelast. Older versions of Jet had trouble with .recordcount until the entire recordset was read but I believe those problems were corrected. If it turns out that you don't get a correct recordcount for a large recordset, put the code with the .movefirst/.movelast in the form's Load event because no matter what, you only have to move to the last record ONCE. You would never have to move there and back every time you changed pages.

 

[Telecom]

Thanks for the code help on .movefirst and .movelast. I tried that out and the code still worked.

 

[Jamet1234]

I am sorry to bring up such an old thread but I am tring to use this code and am getting an error: Invalid SQL Statement;expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update'.

Code:

Dim RecordSet As New ADODB.RecordSet
Dim IngCount1 As Long

RecordSet.Open "Maintenance Work Request", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

With RecordSet
.MoveFirst
.MoveLast
IngCount1 = .RecordCount
End With
Me.txtMachineup = IngCount1

I am tring to do this on the Switchboard is that an issue?
I also need to do a record count of 2 different tables and then some math with the counts before displaying the answer on the switchboard is that possible?

Thanks
Anthony

 

[Telecom]

Your tablename "Maintenance Work Request" is what is causing your error.

I would suggest changing "Maintenance Work Request" to something like this:

tblMaintWorkRequests

So the code statement would be.

RecordSet.Open "tblMaintWorkRequests", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

To answer your 2nd question. Yes, you can count two different tables then add them together.

'Declare DIMs
Dim RecordSet1 As New ADODB.RecordSet
Dim RecordSet2 As New ADODB.RecordSet
Dim lngTbl1Count As Long
Dim lngTbl2Count As Long
Dim lngFinalRecordCount as Long

'First Table Count
RecordSet1.Open "tblMaintWorkRequests", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

With RecordSet1

If .EOF = False Then 'Test for end-of-file
    lngTbl1Count = .RecordCount 'Counts the records
Else
    'Do nothing if EOF = True
End If

End With

RecordSet1.Close 'Closes the Recordset

'Second Table Count
RecordSet2.Open "tblTable2", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

With RecordSet2

If .EOF = False Then 'Test for end-of-file
    lngTbl2Count = .RecordCount 'Counts the records
Else
    'Do nothing if EOF = True
End If

End With

RecordSet2.Close 'Closes the Recordset

lngFinalRecordCount = lngTbl1Count + lngTbl2Count

If lngFinalRecordCount > 0 Then
'Code here
    
Else
'Code here
    
End If