By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,079 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

Record Numbers (Navigation) on Forms - Access 2010

P: 61
Does anyone know how to create record numbers on a form for the purpose of navigation?

Thanks,
Mike
Oct 21 '12 #1

✓ answered by TheSmileyCoder

Is this something you want to show on the form, and only in form view? Or is this something you want to have show up in print?
Assuming you only need it in formview, you could have a textbox tb_RecordIndex and tb_TotalRecords to store the information in, you could use the OnCurrent event of the form to update the fields. It would look something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.NewRecord Then
  3.    Me.tb_RecordIndex = "New"
  4.    Else
  5.    Me.tb_RecordIndex = Me.Recordset.AbsolutePosition + 1
  6.   End If
  7.   Me.tb_TotalRecords = CountRecords()
  8. End Sub
  9.  
  10. Public Function CountRecords() As Long
  11.   Dim rsC As DAO.Recordset
  12.   Set rsC = Me.RecordsetClone
  13.   If Not rsC.EOF Then rsC.MoveLast
  14.  
  15.   CountRecords = rsC.RecordCount
  16.   Set rsC = Nothing
  17. End Function

Share this Question
Share on Google+
22 Replies


zmbd
Expert Mod 5K+
P: 5,397
Forms (usually no pages in a form)
or
Report?
In either case: http://office.microsoft.com/en-gb/ac...010096281.aspx Works in V2010 too
Oct 21 '12 #2

P: 61
Thanks but Access 2010 doesn't have this feature; i believe it was taken out. I created navigation buttons for my forms and I want to remove the standard Access navigation buttons. So if I have 80 records and I'm on record 4, I want to show Record 4 of 80.

Thanks
Oct 21 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
It is available in the reports design. You can see that in action in the DB I uploaded to: http://bytes.com/topic/access/answer...esign-question post# 26.

I'll have to figure out what's going in forms as I have one at work that shows the Page # of ## when printed; however, it is from an older version of access.

IN the meantime for your forms you could use the on current event to do a "record... of... " by opening a record set in the event, get the current record, move first/last to get the count and then use that information as the value in a textbox.
Oct 21 '12 #4

zmbd
Expert Mod 5K+
P: 5,397
Follow up.
Opened the DB I posted the link to....
Opened the main form in design mode.
Showed the page-header/footer section...
Opened one of the reports in design mode and copied the page#of## control.
Copied and pasted the control into the open form:
\
When placed in the page footer/head section of the main form and sent to print (actually preview) the page numbers showed as expected; however, not in form view only in print.

When placed in anyother section of the form or subform then received the expected type error as a form has no pages until sent the printer. I suspect this is also the case with the form at work... seems to me that's the only time I notice the page count. I'll double check the Monday.
Oct 21 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Z's comment in Post #2 is correct. Reports have pages, Forms have records. Recommendation in Post #4 is the easiest way I know how to show "Record X of Y records" on a form.
Oct 22 '12 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Is this something you want to show on the form, and only in form view? Or is this something you want to have show up in print?
Assuming you only need it in formview, you could have a textbox tb_RecordIndex and tb_TotalRecords to store the information in, you could use the OnCurrent event of the form to update the fields. It would look something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.NewRecord Then
  3.    Me.tb_RecordIndex = "New"
  4.    Else
  5.    Me.tb_RecordIndex = Me.Recordset.AbsolutePosition + 1
  6.   End If
  7.   Me.tb_TotalRecords = CountRecords()
  8. End Sub
  9.  
  10. Public Function CountRecords() As Long
  11.   Dim rsC As DAO.Recordset
  12.   Set rsC = Me.RecordsetClone
  13.   If Not rsC.EOF Then rsC.MoveLast
  14.  
  15.   CountRecords = rsC.RecordCount
  16.   Set rsC = Nothing
  17. End Function
Oct 22 '12 #7

P: 61
Sorry I have to be more specific. Yes I want to see the record number I'm on and the total records in the formview. This will allow me to hide the Access navigation buttons, so users will only use the my navigation controls.

@Z thanks, let me go through what you sent and figure it out.

Thanks to all
Oct 22 '12 #8

P: 61
@Smiley, I get the following error message:
"The expression On Current you entered as the event property setting produced the following error: Ambiguous name detected: Form_Current"

I created two text boxes in the Form Footer. One is named tb_RecordIndex and the other is named tb_TotalRecords and I put your code in the On Current event on the form. I'm not sure what happened, have any ideas?

Thanks
Oct 22 '12 #9

zmbd
Expert Mod 5K+
P: 5,397
mburch2000
Most certainly... you have one or more controls in scope of the form that have the same name. Change the name of the newly created controls in some manner and the error should clear.
Oct 22 '12 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
If you open your form, select the form properties and navigate to the Events tab, you need to look at the Event for "On Current".

Select the dropdown and select "Event Procedure". This will create a small bit of code like so:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. End Sub
Then copy/paste the code posted previously.
Oct 22 '12 #11

P: 61
Alright, I think I get the issue now. In the On Current event, I also have Error Handling code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo HandleError:
The code I entered for the records start as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. If Me.NewRecord Then
Does this mean that I need to change one of these to another name, such as Private Sub Form2_Current()?

Sorry for the stupid questions, but I'm still trying to figure this out. Thanks very much
Oct 22 '12 #12

P: 61
Well I renamed the Smiley's code On Current event to Private Sub Form2_Current() and the error message went away. But I still don't get my record numbers. :-(
Oct 22 '12 #13

zmbd
Expert Mod 5K+
P: 5,397
I'd start over from scratch with that event.
Open the VBA editor
CLOSE every open program window.
Open the program window for just the form in question.
Delete the "Form_Current" codes you have.... (maybe cut and past to notepad if there's something you made there.)

Go back to the form in the Access window... it should already be in design mode; however, if not, go into design mode.
Show the properties for the form
Goto the events tab for the form
Find the on current event and click the [...] button and select "code" as you do normally.
Go back to the VBA editor window and you should have a nice clean Private Sub Form_Current()...end sub into which paste TheSmileyCoder's code.
Oct 22 '12 #14

P: 61
@Z, Wow it works great.

@Smiley, your code is great! Now I need to put my other code back and see what happens. I really appreciate your help!!!
Oct 22 '12 #15

TheSmileyCoder
Expert Mod 100+
P: 2,321
Glad to hear you got it working.

Coding is access is mostly "Event Driven".
Most objects in access has events, and you can tie code into these events. The Form_Current event fires each time you navigate to a record. By using VBA you can then decide what should happen when that event occurs. I have forms that lockdown certain controls, depending on whether or not the status of the record is "Submitted"(A field in my table) or not.

I use the Form_BeforeUpdate to run bits of code to check if the form is filled out correctly. You can also do some of this at the table level, by using input masks, and setting the field to required, however that is not always sufficient. You could have cases where a field only needs to be filled out if certain criteria are true. Example: A Issue record might only need a Supervisor selected if the Issue is determined to be Critical. In that case you could use something like:
(Pseudocode)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as integer)
  2.  If Me.IssueImportance="Critical" AND IsNull(Me.SuperVisor) then
  3.   Msgbox "A supervisor must be selected for critical issues",vbOkOnly+vbExclamation
  4.   'Cancel Update
  5.   Cancel=True
  6.  End If
  7. End Sub
This also means that some function/sub names are reserved for the events. I can't just make my own function and call it Form_BeforeUpdate, but I can create a function such as FormValid and call that like the example below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as integer)
  2.   Cancel=Not FormValid()
  3. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Function FormValid()
  2.  If Me.IssueImportance="Critical" AND IsNull(Me.SuperVisor) then
  3.   Msgbox "A supervisor must be selected for critical issues",vbOkOnly+vbExclamation
  4.   'Form is not valid
  5.   FormValid=False
  6.  else
  7.   FormValid=True
  8.  End If
  9. End Sub
Oct 22 '12 #16

P: 61
Smiley, I appreciate your post. You are definitely a pro. I am working my way there, very slowly. I still have a lot to learn, but understand the VBA concepts, even if I don't have a lot of experience. I'm going to continue working hard, so I can get there. Thanks very much!
Oct 22 '12 #17

TheSmileyCoder
Expert Mod 100+
P: 2,321
You are quite welcome. I hope you will enjoy your VBA path, I know I have enjoyed (and still do) mine. I have found it quite exilerating discovering the power of VBA and access.
Oct 23 '12 #18

P: 4
To get a Page X or Page X of Y on your PRINTED form:
1. Right click on Form you want and open in Design View
2. Right click the ribbon and open "customize the ribbon"
3. On the RIGHT side of the screen under "customize the ribbon" there is a drop down list - choose "Tool Tabs"
4. Under Form Design Tools, click + next to Design
5. Right click Design and Add New Group - I named my group Page Print Design
6. On LEFT of screen under the drop down list "Choose commands from", click All Commands
7. Scroll down to Page Number and add it to your new custom group. I also added Page Setup to the new custom group.
8. You will now have an icon to click to add page number to form designs just like 2007 including Page X of Y.
9. This a global change and all your forms will get the benefit.
Sep 18 '14 #19

NeoPa
Expert Mod 15k+
P: 31,494
In order to avoid further confusion I have renamed the thread and edited the OP (Original Post) to reflect the actual question to be dealt with. This should save many people time and effort trying to deal with form pages.

@Mike.
Please pay a lot more attention when posting questions in future. This one's in the past now, but it would be nice to avoid any such issues for new questions.

MBurch:
Smiley, I appreciate your post. You are definitely a pro.
I can confirm that one - Anders Ebro (TheSmileyCoder).
Sep 18 '14 #20

P: 4
Since you changed the thread, how do people get the information on how to put page numbers in a form? An early reply was excellent unless you hat no page number icon in your ribbon.

It took me a great deal of time to figure out how to get the icon for inserting the page number even into my ribbon since when the program loaded, it was not there to begin with.
Sep 18 '14 #21

zmbd
Expert Mod 5K+
P: 5,397
ReneeDaphne:
This is why we urge posters to completely read a thread. If you had done so, you should have realized that your post really does not relate to what mburch2000 intended to ask... which took a few posts into the thread to ferret out.

We've left your post in line, even though it's not really on topic, because it did answer the original title and it may prove helpful to someone looking to print from a from with page numbers (even though that's not what forms are intended for - which is indeed why that icon is not readily available for forms and yet is easily available or reports. Honestly though, many long time users bypass the icon in the report editor and simply insert the control by hand (^_^) ).

It is always advisable to completely read a thread, especially old threads, before making a post.
Sep 19 '14 #22

NeoPa
Expert Mod 15k+
P: 31,494
Renee:
Since you changed the thread, how do people get the information ...
I changed the title to reflect the question in the thread accurately (Not more accurately because the original title wasn't accurate at all). You were an unfortunate victim of the title being misleading in the first place. Leaving it as misleading would do no good to anyone. It may even cause others to misinterpret what the question really was and post off-topic replies - which is not what anyone wants.

If you believe that there is important information that you would like to share then I can suggest you post your own question on the subject and answer it yourself. That way it would at least be in a place that people can find it when looking for that particular issue. Having it in here is a waste of time because no-one is likely to be looking in this thread for an answer to a question that has not been asked in this thread. It's clearly not enough of a topic to be the subject of an article.

You see, it all makes sense really. Nothing complicated - just common sense.
Sep 21 '14 #23

Post your reply

Sign in to post your reply or Sign up for a free account.