If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need calculated control to work
I have a subform with fields: first name, last name, phone, expiration date,
membership status. I want a calculated control to display the total number of records that appear in the subform. How do I do this? -- jj |
#2
|
|||
|
|||
Need calculated control to work
The simplest way to do this is to set the subform's NavigationButtons
property to Yes. The left end of the horizontal scrollbar will then display something like: 4 of 199 records If you don't want to do that, life is not so simple. If the form is in Form view or Continuous View, you could add a text box to its Form Footer section, and set the Control Source to: =[Form].[Recordset].[RecordCount] However, several things can go wrong with that: a) It may initially show 1 for a while when the form first loads. This is because it takes Access some time to load the form's records, and some further time before it must update this text box. b) This fails in Access 2007, where you must write a function to get the count. c) In older versions (before Access 2000) you might need to use: =[Form].[RecordsetClone].[RecordCount] -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JJ" wrote in message ... I have a subform with fields: first name, last name, phone, expiration date, membership status. I want a calculated control to display the total number of records that appear in the subform. How do I do this? -- jj |
#3
|
|||
|
|||
Need calculated control to work
Allen,
Have not started using 2007 yet. Do I understand correctly that in 2007 you actually have to write a function to get the record count and cannot referr to the Forms recordsetclone.recordcount property? "Allen Browne" wrote in message ... The simplest way to do this is to set the subform's NavigationButtons property to Yes. The left end of the horizontal scrollbar will then display something like: 4 of 199 records If you don't want to do that, life is not so simple. If the form is in Form view or Continuous View, you could add a text box to its Form Footer section, and set the Control Source to: =[Form].[Recordset].[RecordCount] However, several things can go wrong with that: a) It may initially show 1 for a while when the form first loads. This is because it takes Access some time to load the form's records, and some further time before it must update this text box. b) This fails in Access 2007, where you must write a function to get the count. c) In older versions (before Access 2000) you might need to use: =[Form].[RecordsetClone].[RecordCount] -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "JJ" wrote in message ... I have a subform with fields: first name, last name, phone, expiration date, membership status. I want a calculated control to display the total number of records that appear in the subform. How do I do this? -- jj |
#4
|
|||
|
|||
Need calculated control to work
A VBA function is the only way I know to get around Access 2007's inability
to handle: =[Form].[Recordset].[RecordCount] in the Control Source of a text box. It's trivial. Without error handling: Function RecordsInForm(frm As Form) As Long If frm.RecordSource vbNullString Then With frm.RecordsetClone If .RecordCount 0 Then .MoveLast RecordsInForm = .RecordCount End If End With End If End Function Set the Control Source of a text box to literally this: =RecordsInForm([Form]) (i.e. you do not replace the Form part.) If you only need to know whethere there any records or not, you can improve performance hugely by omitting the MoveLast, so that's what I prefer to do. It's the FormHasData() function on this page: http://allenbrowne.com/RecordCountError.html (I realize this is probably obvious to you, Dale, but others may be researching this too.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Dale Fye" wrote in message ... Allen, Have not started using 2007 yet. Do I understand correctly that in 2007 you actually have to write a function to get the record count and cannot referr to the Forms recordsetclone.recordcount property? |
#5
|
|||
|
|||
Need calculated control to work
Thanks, Allen.
Am getting ready to venture into the 2007 realm, and am starting to read all of the 2007 threads to identify "features" in advance. "Allen Browne" wrote in message ... A VBA function is the only way I know to get around Access 2007's inability to handle: =[Form].[Recordset].[RecordCount] in the Control Source of a text box. It's trivial. Without error handling: Function RecordsInForm(frm As Form) As Long If frm.RecordSource vbNullString Then With frm.RecordsetClone If .RecordCount 0 Then .MoveLast RecordsInForm = .RecordCount End If End With End If End Function Set the Control Source of a text box to literally this: =RecordsInForm([Form]) (i.e. you do not replace the Form part.) If you only need to know whethere there any records or not, you can improve performance hugely by omitting the MoveLast, so that's what I prefer to do. It's the FormHasData() function on this page: http://allenbrowne.com/RecordCountError.html (I realize this is probably obvious to you, Dale, but others may be researching this too.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Dale Fye" wrote in message ... Allen, Have not started using 2007 yet. Do I understand correctly that in 2007 you actually have to write a function to get the record count and cannot referr to the Forms recordsetclone.recordcount property? |
Thread Tools | |
Display Modes | |
|
|