Equivalent of "Refresh All"

[Sa'El]

Hi All,



When a splitform (or maybe datasheet form) is initially opened and no action is done, at the bottom in the navigation section where you can see the number of records, it's initially blank. If you click in a field then you see the number of records. Additionally, if you go to the Ribbon and click "Refresh All", you also see the number of records.

Essentially, I would like the equivalent of "Refresh All" to be programmed, meaning, when a form loads I would like the number of records to be displayed at the bottom without the user having to click or anything. In my case the lack of clicking is significant since the "fields" the user could click are all hyperlinks (and besides, the user shouldn't have to do anything for the number of records to be generated). Me.Requery and it's siblings hasn't worked. I've checked the internet and this same question has been asked, and Me.Requery has been stated as a solution, but just like me, the originator of the question said it didn't work.

Does anyone know how to program the equivalent? Thanks.

[June7]

I don't use split form (looked at it when Access 2007 was new, didn't like it).

I don't see this issue with stand alone form. I do see it sometimes with subform. In those instances, clicking into the subform seems to be only way to trigger the record count.

Just did some testing with 2 different dbs. In one the subform had to be clicked, in the other did not. The first is synchronized to main form, the second is not.

[Sa'El]

Hi June,

So it's likely a side-effect of the split form...I can live with. It's unfortunate that you can have an icon that does the trick but not have a VBA equivalent for the icon.

Thank you June.

[June7]

Review: http://www.techonthenet.com/access/f...fresh_form.php

I just tested the code with Access 2010 db (the first one that had to click the subform) and it worked.

[Sa'El]

Thank you for the extra effort. I inputted the code first as a Load event it didn't work. When I created a button and tried inserted the same code, that didn't work too. The name of the button was correct (did another type of command and it worked). When it worked for you did you use a split form? Regardless, it's not that big a deal, though I'll keep that link for my records.

Anyway, I do have another question entirely. Is there any code that captures when a record changes. Therefore, in a regular form and you move to the next record, is there an "On Next Record" equivalent or something?

Reason is that initially I had single forms that weren't going to have multiple entries. For these forms I added code that After Update certain fields would be enabled/disabled. Now that I have multiple entries for a couple of these forms, I noticed that when you go to the next record, whatever code triggered for the first record is saved. Logically, I'm thinking that in these instances, once a new record is activated, the code for that form resets to the initial On_Load event.

What do you think?

[June7]

I tested the command in the VBA immediate window. Not a split form.

No "On Next Record" event.

Try the OnCurrent event for code to set controls enabled/disabled. Or use Conditional Formatting. CF will allow enable/disable for each record independently of the others, whereas VBA applies same setting to all records.

[Sa'El]

As the After Update depends on the values of the fields and not simply just being active, it doesn't look like the On Current would work. I wasn't aware that CF also effects enable/disable, and it would fit perfectly!

I'm so grateful for your help June...I would propose on one knee, but since I fear rejection, I think I'll pass.

[Missinglinq]

Can't test this with a Split Form, right now, but a problem with Access, vis-à-vis Record Counts, is that Access doesn't necessarily load an entire RecordSet when a Form first loads...and this quirk may be particularly problematic when loading the new Split Form. To facilitate custom Record Counts, in other types of Forms, the following can be used, and may be helpful, in your situation; it simply forces the Access Gnomes to load all of the RecordSet, on opening the Form, by moving to the Last Record then returning to the First Record:

Private Sub Form_Load()
  DoCmd.GoToRecord , , acLast
  DoCmd.GoToRecord , , acFirst
End Sub

Linq ;0)>

[Sa'El]

Sorry for the delay. I was able to test out your suggestion Linq, and it worked on the Split Form! This was the kind of manipulation I was looking for to that would make the form do a harmless action programmatically to trigger the recordset to display.

Thank you both for the help!

[Missinglinq]

Glad we could help!

Good luck with your project!

Linq ;0)>