'1 of x' records in a form

[vbaInet]

I've just noticed, wrong code. That is actually counting the number of controls you have on your form. Use this instead

Dim rs As Recordset
Set rs = Me.Recordset
txtBoxName = Me.CurrentRecord & " of " & rs.RecordCount

Place it inside the signatures.

 

[AndyCabbages]

That code is much better, thanks.

One small problem though. When the form is initially opened it appears as '1 of 1'. When the 'Next Record' button is pressed it then corrects itself to '2 of 17' and remains as 'x of 17' when the records are cycles through.

 

[vbaInet]

That's a strange behaviour. I thought I could convince you to learn some vba now

Paste this code in the Control Source property of the text box:

=[CurrentRecord] & " of " & Count(*)

 

[missinglinq]

Add this code to get rid of the problem of showing 1 of 1 when the form first opens.

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

 

[AndyCabbages]

Hmmm, when I add that piece of code to the Control Source property for the text box it gives me an error telling me I cant assign a value to that box. Then when I delete the code from the VBA it gets a bit buggy on the form page.

It comes up with '1 of 1' then when I change record it completely disappears

 

[vbaInet]

Delete the code from the vba editor window first. Then paste the last code in the Control Source property of your text box.

 

[AndyCabbages]

Same thing hapens as before.

It starts as showing '1 of ' then the dissapears when the record is changed :s

Missinglinq, I pasted that code into the VBA and it gave a few errors when I tried to open the form

 

[vbaInet]

Zip, upload and post your db. Things aren't set up right on there.

 

[AndyCabbages]

Sorry, I cant send you a copy of the database due to the agreement I have with the company. I tried to break it down and remove everything except the form but it is all interlinked and isnt really practical

 

[vbaInet]

Ah yes, I remembered you mentioned that earlier. Maybe it's time to pick up those books. You've been provided with relevant bits of code so it must be something to with your setup and without seeing your db it would be hard to diagnose.

I believe you now know where the form's On Load event is based on missinglinq's code? Put a message box in between to see how many records there are when it loads.

 

[missinglinq]

There is absolutely nothing in the code I gave you to pop an error, assuming this is a bound form. It simply moves to the last record then moves back to the first record, forcing Access to load all of the records, which makes the 1 of X Records work when the form first opens.

My guess is that you've left code somewhere in all of your attempts to solve this thing. You need to

1. Delete everything you've tried in code so far
2. Delete anything you've placed the Properties Pane trying to do this
3. Then paste this in this code in the code window, replacing TextBoxName with the actual name of your textbox.

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

Private Sub Form_Current()
  Me.TextBoxName = "Record  " & CurrentRecord & "  Of  " & _
  RecordsetClone.RecordCount & "  Records"
End Sub

 

[LPurvis]

As suggested by MissingLinq, using the form's RecordsetClone gives you the opportunity to determine record position and count without affecting the UI at all.
For a navigation implementation, perhaps have a look at the play and go navigation buttons here.
(Very similar to Stephen Lebans example too - recnavbuttons)

Cheers.

 

[ghudson]

Did you try searching the forum?

This sample should answer your question... Record X of Y

 

[AndyCabbages]

Thank you everyone for the help with this problem, it seems to be working nicely now

Special thanks to vbaInet in particular for the lessons in VBA and putting up with my complete lack of experience, lol

 

[AndyCabbages]

Also, missinglinq, the code you gave me to Form_load sub was causing an error because I didnt realise that there was already a Form_load sub defined in the code. I was creating a second Form_Load sub rather than adding to the one that was already there.

Also the reason I couldnt right click on the form and select the properties was because in the Form_Sub load it is coded to automatically select one of the text boxes already there. This has been fixed since it was annoying.

 

[vbaInet]

Haha! No problem. Glad to have made an input.