Record number displayed in form

[GregP]

I have a single row datasheet form, which, when in 'Open'
view, displays several hundred records. I want to be able to have these records numbered 1-500 or whatever, in exactly the same way as the navigation bar displays which record number you're up to in the form. Essentially I just want to take the data control which displays this information in the navigation bar, and place it in the body of the form. How do I do this? Or achieve the same end result via some other means?

 

[Oldsoftboss]

I have an ubound text box on a form and put the code under the 'current' event of my form: Me!TextBoxName = CurrentRecord & " of " & Me.RecordsetClone.RecordCount

HTH
Dave

 

[GregP]

That looks like the sort of thing I'm trying to do, but can you please step me through it slowly, haven't had lots of experience with all this code stuff! The first part looks like what I'm after (don't need the x of y bit), what's the deal with the Me! part? I know that would seem to indicate the record source (table name?) but why Me? Does that refer to the current form or something? Whenever I tried to use that, it said it couldn't find the macro or something referring to Me.

Where do I acutally put this code? I tried pasting it into the onCurrent property, and it says it can't find the macro with that name. I tried creating a macro, and storing that code in the function name of the RunCode object, but that gave me the error "The object doesn't contain the automation object 'Me'. When I left it as just 'TextBoxName = CurrentRecord' (where the textbox in question is called TextBoxName), it didn't report any errors, but neither did it display anything in the text box.

BTW, I presume the Me.RecordsetClone... should be Me!RecordsetClone?

Hope you can help!

 

[Oldsoftboss]

Lets start from scratch.
Open your form in design mode and add a text box. (The ab button with the vertical line beside it) Delete the attached label.
Then double click on the new text box to open the properties window and give it a meaningfull name (In my previous post the "TextboxName"). It will probably have something like text5 in the name but you can change it to something a bit easier to remember. Maybe - Mycounter - for example (No spaces)
Next
On your form (in design mode) you will notice a small box in the top LH corner. When you click in the square a small black dot will appear. Double click on the black dot and the form properties window should open.
Click on the event tab and you should see a line with On Current.
Type [Event Procedure] in here.(Once you start to type it should come up) then press the little square on the RHS of that line, the one with 3 dots in it.
This will take you to the code window and create an area to type your code.
Should have private sub Form_Current()

that is where you type the code from the previous post (Plus the extra bit: !!

Me!MyCounter =CurrentRecord & " of " & Me.RecordsetClone.RecordCount

Me refers to the form the MyCounter field is found on, CurrentRecord reflects the position in the record count in the navigation bar at the bottom that you refered to, & " of " & is how you join the two fields together and the Me.RecordsetClone.RecordCount is a background count of the no of records in the table.

Hope that helps

Dave

PS If anyone else can explain it a little easier please feel free too.

 

[GregP]

Thanks for that, I got it to work in the manner you described. Unfortunately it still hasn't exactly solved my problem. Since I have a datasheet form, I am able to view many records simultaneously. I therefore need each row to have a different number in it, i.e. the record number it is within the form. The method you supplied did generate a record number, but every field was given the current record number, instead of each record having its own number. I.e. I would want record #10 to have a number '10' in the field regardless of which record in the form I had selected.

Guess this changes the nature of the whole problem, any more ideas? There's a autonumber thread started by Anarch which has a possible solution, creating an event to number each field when the form is opened, but that hasn't worked thus far for me. Is there some other method you can think of?

Thanks for your help thus far, it's actually been useful for one of my other forms. Just have to get this one solved now!

 

[Oldsoftboss]

Talking of autonumbers, does your table have an ID autonumber field. The question is usually asked when you first save the table. If not, you should have, for searching and referencing, if nothing else. Maybe you could use this No as your record no.??

 

[GregP]

Thought of that. Yes I do have an autonumber field as the primary key, but I'm always changing the order of the records in the form, but still want these sequentially numbered regardless of the sort order.