Displaying Correct Recordcount/CurrentRecord (1 Viewer)

[Tim L]

I've got a form where I've used a text box and the OnCurrent event to display the current record number and recordsetcount. When I open the form from the forms dialogue everything is fine however the form is opened from the menu form I get "1 of 1" when I know that more than 1 records exist; a change of record, to 2 or the end of the recordset updates the display correctly.

I noticed that on opening the form there is sometimes a delay, "calculating", so I placed code in the OnTimer event to update the text box (then cancels itself so that it doesn't hog the processor), this works but is a fudge. Also, if the dB is used on a slower machine the OnTimer event may not be in time.

So:

1. Is it really the 'calculating' that is preventing the text box being correctly updated?

2. Is it possible to detect when the calculating has completed so that I can then update the text box accordingly and if so, how? For instance, I would like to put a message box on the screen informing the user that calculations are in progress and then update the text box and close it on completion. (User's don't always look at the status bar do they )

Tim

 

[RoyVidar]

One thing to try, is issuing a .movelast on the recordset from which you retrieve the recordcount.

 

[modest]

Nmi

Depending on where you're getting your recordset from. If you're opening a DynaSet, I think it always returns a 1 as the recordcount. For instance:

Set rs = CurrentDb.OpenRecordset("Table Name", dbOpenDynaSet)
rs.MoveLast
Debug.Print rs.RecordCount                  '= 1

To get around this, open a forwardonly recordset or loop through:

Set rs = CurrentDb.OpenRecordset("Table Name", dbOpenDynaSet)
With rs
    .MoveLast
    Do Until .BOF
        lngRcdCount = lngRcdCount + 1
        .MovePrevious
    Loop
End With
Debug.Print "Loop Record Count: " & lngRcdCount
Debug.Print "Recordset Count: " & rs.RecordCount                  '= 1

Note: There are many ways to get a record count. This can be achieved by using count() in a query, possibly cloning a recordset, looping through a recordset as I've suggested, possibly accessing the cache.

This also depends on how/where you set your textbox. You may just need to do a textbox requery. Is this done by code, if so show your code.. this shouldn't be a toughy

 

[ghudson]

My code belows is updating a label named "lRecordXofY".

Put this in the forms OnOpen event to get the correct X of Y count when the forms is first opened...

    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.RunCommand acCmdRecordsGoToFirst

Put this in the forms OnCurrent event to get the correct X of Y...

    If Me.NewRecord Then
        Me.lRecordXofY.Caption = "New Record (" & Format$(Me.RecordsetClone.RecordCount, "#,###") & " existing records)"
    Else
        Me.lRecordXofY.Caption = "Record " & Format$([CurrentRecord], "#,###") & " of " & Format$(Me.RecordsetClone.RecordCount, "#,###")
    End If

 

[Tim L]

modest, ghudson,

Thanks for the advice. Unfortunately the solution still evades me (error reports like "command not available" and "unable to go to the specified record").

I've been using the following code in the OnCurrent event:

    Me.txtRecordIndicator.Value = "Job " & Me.CurrentRecord & _
        " of " & Me.RecordsetClone.RecordCount

The text box is located in the Form header. This code works fine normally.

As for running up and down the records I've also tried:

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst

modest, I've been unable to use your code as I am not familiar with where it should be placed, I also doubt that it would solve the problem, see below.

The RecordSource is a query which includes a reference to a combo box on the form, when a value is selected in the combo box I requery. Is it, perhaps, because the query needs to run before the records are displayed and the OnCurrent event occurs before the query completes, that consequently Me.RecordsetClone.RecordCount returns the wrong info?

The problem also occurs when the query has rerun following a new selection of the combo box, I just need to figure out what code to put in the OnCurrent event... Any ideas? (Or an alternative method to achieve my aim...?)

Tim

 

[RoyVidar]

"One thing to try, is issuing a .movelast on the recordset from which you retrieve the recordcount."

Me.RecordsetClone.movelast
Me.txtRecordIndicator.Value = "Job " & Me.CurrentRecord & _
" of " & Me.RecordsetClone.RecordCount

you could stuff those lines just after the requery line too, if it doesn't trigger the on current, which I think it should.

 

[Tim L]

Roy-Vidar,

thank you very much, that works a treat! Just put the code in the OnCurrent event and hey-presto

Cheers,

Tim