Show a record number?

[Phreeneazie]

Hi all,

I've just about finished my first ever database! I think it looks ok, I guess most would say it's a little raw. Anyway, what I'd like to be able to do, is when I scroll left and right thru my records, is have a little count in the bottom right hand corner saying, "Record ## of ###" - I've scoured the net trying to find out how to do it, I've exhausted myself looking thru the "help" topics and I just can't figure it out!

Can anyone help me out?

Please bear in mind, I have no experience with VB or anything whatsoever, so I'd be really grateful for a real step-by-step how-to!

Thanks to anyone and everyone.

 

[fneily]

Create a label somewhere on your form. Make it about an inch and a half long. Call it recnum.

On the form's Declarations section put:

Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim Totalrecords

On the AfterInsert event put:

Private Sub Form_AfterInsert()
Records.MoveLast
Totalrecords = Records.RecordCount
End Sub

On the BeforeInsert event put:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me![recnum].Caption = Totalrecords + 1 & " pending..."
End Sub

On the OnCurrent event put:

Private Sub Form_Current()
If Not Me.NewRecord Then
Records.Bookmark = Me.Bookmark
Me![recnum].Caption = "Record " & _
Records.AbsolutePosition + 1 & " of " & _
Totalrecords
Else
Me![recnum].Caption = "New Record"
End If
End Sub

On the OnLoad event put:

Private Sub Form_Load()
Set Records = Me.RecordsetClone
Records.MoveLast
Totalrecords = Records.RecordCount
End Sub

Give it a try.

Neil

 

[fneily]

Sorry. I just reread your note.
Go to design view of your form. Create a label. I'll assume you know how to do this. Click on the upper left hand square of the form. A black square should appear. This means the form is selected. Bring up the form's property sheet (button at the top with a hand on it). Click on the Event tab. Click on the box next to OnLoad. Click on the button with three dots (called Build Button). Select Code Builder. Click OK. Type in the above code for Load (Form_load). You'll see at the top of the VBA window Option Compare Database. Click underneath those words and type in the statements for the Declaration section from my last post. Don't repeat Option Compare Database.
On the top right you will see a dropdown arrow. Click on it and select the next event to add code to. eg. BeforeInsert. Copy the code in my last post. Do this for all the events I have coded.
Close the VBA window. Return to your normal form and as you scroll left, right, new record, the label will show the record of statement.

Neil

 

[Phreeneazie]

Neil,

thanks very much for that - it works really well! However, if I do a sort on ascending (or descending), I get a:

"Run-time error '91':

Object variable or With block variable not set"

when I click on debug, the highlighted line is:

"Records.Bookmark = Me.Bookmark" in Private Sub Form_Current().

Also, when I first load the form, I get a:

"Run-time error '3021':

No current record"........For some reason (I don't know if this is normal or not) when I first open the form, no records are shown - I have to click on sort asc or des before any records are loaded..???

Many thanks!

 

[fneily]

Look at something first. Go into design view of your form. Do ALT+F11. Click on Tools, then click on References. Look for a library Microsoft 3.6 DAO Library. You may have a different version. Make sure there's a check next to it. Also, using the Up arrow position this library near the top of the list - like third or fourth place. Close VBA. Close your form and save it. Reopen the form and see if you still get the error.

Sorting may be a problem, you're changing the position of records which the term BOOKMARK refers to. You may have to go to the VBA forum, show them the code and see if the experts there can figure it out. I'll try but I can't promise you anything. Maybe there's a way to sort the records first then open the form. Like ask the user how do they want the data sorted, have them click an option. I'm going to check on this.

Meantime check that lib.

Neil

 

[Phreeneazie]

Hi Neil (cool name by the way!),

checked that library - it's third in the list and has the tick next to it. It's "Microsoft DOA 3.6 Object Library".

As for the sorting of data - I have no customers to ask - it's me that does everything with the database, no-one else touches it. I know the field I'd be happy to sort on (membership number).

Thanks again,

Neil.

 

[fneily]

I don't know why when you open your form you're getting an error. Mine isn't. Make sure in the VBA,
Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim Totalrecords
are at the very top above a line and in the Declarations section.
Also check the Order By option on the form's property sheet and see if you see the word DESC. Get rid of it.

But I was right about the sort process. It's messing with the recordset. I get the same errors when sorting. So I just have to figure out a way to reset the recordsetclone.
Might take me a day.

 

[Phreeneazie]

Yep, have checked as you said and right at the very top, I have:

Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim Totalrecords

and then the line.

Where would I find the "Order By" option? I go to design view, right click on my form, select Properties and "Order By" isn't on the "ALL" tab at all..?

Really appreciate you looking into all this for me

 

[fneily]

You have to select the form. To do this click on the square in the upper left hand corner. A black square should appear. The property sheet's title bar should only say Form. Click the All tab and if you look down the options you'll see Order By.

Still trying to figure out how to reset the recordset after sorting.

 

[Phreeneazie]

Thanks Neil!

Just had a look and the Order By says, "Members.Address" - is that ok?

Thanks for still looking for me!

 

[fneily]

Ok. Let's start over. Create two labels, I called my recnum and lbltotal. Place the following code on the OnCurrent event of the form:

Private Sub Form_Current()
On Error Resume Next
Dim rs As DAO.Recordset, intCount As Integer, lngPosition As Long, lngTotal As Long, frm As Form
Set frm = Forms!recordindicators
Set rs = Me.RecordsetClone
rs.FindFirst "[nameid] =" & Me![Nameid]
frm![recnum].Caption = "Record " & rs.AbsolutePosition + 1
lngPosition = rs.AbsolutePosition + 1
rs.MoveLast
frm!lbltotal.Caption = rs.AbsolutePosition + 1
lngTotal = rs.AbsolutePosition + 1
End Sub

In rx.FindFirst "[TableFieldName] =" & Me![ControlName]

Now when you sort, it works.
Neil

 

[ReluctantDataGuy]

This works for me...

RecDat is a textbox...

Me.RecDat = IIf(Me.NewRecord, "New Record", "Record " & _
Me.CurrentRecord & " of " & Me.Recordset.RecordCount)