Record Counts

I built a simple form using the wizard (it's easier...) Its record source is 
a table (tblBoM). I use a combo box in the header to filter the form. I need 
to display in a combo box the number of records in the form. Me.Count is 
giving me some number which may or may not be the UnFiltered record count, 
but in any case, it's wrong.

What can I use? I've bludgeoned it to death with a dCount() reference as the 
control source of the text box, but I just can't imagine that's the best way 
to go....
-- 
Jim
0
Utf
11/13/2009 5:33:06 PM
access.formscoding 7493 articles. 0 followers. Follow

8 Replies
381 Views

Similar Articles

[PageSpeed] 52

On Fri, 13 Nov 2009 09:33:06 -0800, JimS wrote:

> I built a simple form using the wizard (it's easier...) Its record source is 
> a table (tblBoM). I use a combo box in the header to filter the form. I need 
> to display in a combo box the number of records in the form. Me.Count is 
> giving me some number which may or may not be the UnFiltered record count, 
> but in any case, it's wrong.
> 
> What can I use? I've bludgeoned it to death with a dCount() reference as the 
> control source of the text box, but I just can't imagine that's the best way 
> to go....

=[RecordsetClone].[RecordCount]
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
11/13/2009 5:55:08 PM
After a form is loaded, you need to move to the last record then back to the
first record before using the code for the record count. This insures that
all records have actually been loaded before giving you the count.

You can do this with an open recordset using 

.MoveLast 
.MoveFirst 

or you can simply use 

DoCmd.GoToRecord , , acLast
DoCmd.GoToRecord , , acFirst

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200911/1

0
Linq
11/13/2009 6:19:08 PM
JimS wrote:

>I built a simple form using the wizard (it's easier...) Its record source is 
>a table (tblBoM). I use a combo box in the header to filter the form. I need 
>to display in a combo box the number of records in the form. Me.Count is 
>giving me some number which may or may not be the UnFiltered record count, 
>but in any case, it's wrong.
>
>What can I use? I've bludgeoned it to death with a dCount() reference as the 
>control source of the text box, but I just can't imagine that's the best way 
>to go....


Me.Count is the number of controls on the form so that's not
relevant to your problem.

Use some code in the combo box to set the record count in a
text box:
	
With Me.RecordsetClone
	.MoveLast
	Me.thetextbox = .RecordCount
End With

-- 
Marsh
MVP [MS Access]
0
Marshall
11/13/2009 6:29:33 PM
Jim -

Put this in a textbox in your form header or footer:   
=Count([primarykeyfield])
but using your primary key field name.   The count also appears in the 
record navigation bar if that is displayed on your form.
-- 
Daryl S


"JimS" wrote:

> I built a simple form using the wizard (it's easier...) Its record source is 
> a table (tblBoM). I use a combo box in the header to filter the form. I need 
> to display in a combo box the number of records in the form. Me.Count is 
> giving me some number which may or may not be the UnFiltered record count, 
> but in any case, it's wrong.
> 
> What can I use? I've bludgeoned it to death with a dCount() reference as the 
> control source of the text box, but I just can't imagine that's the best way 
> to go....
> -- 
> Jim
0
Utf
11/13/2009 6:33:01 PM
=[RecordsetClone].[RecordCount] gets me a #Name? error.

btw...A2007
-- 
Jim


"fredg" wrote:

> On Fri, 13 Nov 2009 09:33:06 -0800, JimS wrote:
> 
> > I built a simple form using the wizard (it's easier...) Its record source is 
> > a table (tblBoM). I use a combo box in the header to filter the form. I need 
> > to display in a combo box the number of records in the form. Me.Count is 
> > giving me some number which may or may not be the UnFiltered record count, 
> > but in any case, it's wrong.
> > 
> > What can I use? I've bludgeoned it to death with a dCount() reference as the 
> > control source of the text box, but I just can't imagine that's the best way 
> > to go....
> 
> =[RecordsetClone].[RecordCount]
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
> .
> 
0
Utf
11/13/2009 8:42:06 PM
On Fri, 13 Nov 2009 12:42:06 -0800, JimS <JimS@discussions.microsoft.com>
wrote:

>=[RecordsetClone].[RecordCount] gets me a #Name? error.
>
>btw...A2007

Try 

Me.RecordsetClone.RecordCount
-- 

             John W. Vinson [MVP]
0
John
11/13/2009 10:50:16 PM
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:skorf51oosjonnet6f9u18e1esf9on2p00@4ax.com...
> On Fri, 13 Nov 2009 12:42:06 -0800, JimS <JimS@discussions.microsoft.com>
> wrote:
>
>>=[RecordsetClone].[RecordCount] gets me a #Name? error.
>>
>>btw...A2007
>
> Try
>
> Me.RecordsetClone.RecordCount
> -- 
>
>             John W. Vinson [MVP]

You can't use the Me keyword in a property expression. You must use [Form] 
instead (if you type Form, Access will surround it with square brackets)


0
Stuart
11/14/2009 1:20:19 AM
On Sat, 14 Nov 2009 01:20:19 -0000, "Stuart McCall" <smccall@myunrealbox.com>
wrote:

>"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
>news:skorf51oosjonnet6f9u18e1esf9on2p00@4ax.com...
>> On Fri, 13 Nov 2009 12:42:06 -0800, JimS <JimS@discussions.microsoft.com>
>> wrote:
>>
>>>=[RecordsetClone].[RecordCount] gets me a #Name? error.
>>>
>>>btw...A2007
>>
>> Try
>>
>> Me.RecordsetClone.RecordCount
>> -- 
>>
>>             John W. Vinson [MVP]
>
>You can't use the Me keyword in a property expression. You must use [Form] 
>instead (if you type Form, Access will surround it with square brackets)
>

thanks Stuart, you're right of course!
-- 

             John W. Vinson [MVP]
0
John
11/14/2009 1:44:17 AM
Reply:

Similar Artilces:

Count File Names
Hi All, I having an issue with version control on Buisness cases. I currently have a spreadsheet that looks into a folder and picks up details from a number of files. However, they require version control numbers so there will eventually be multiple files say 2010-01 v1.0, 2010-01 v1.1. Is there a way I can: 1) Get a macro to count the number of 2010-01 files there are in the folder, and 2) Get my version control sheet to look at the latest version of the file (in this example: 2010-01 v1.1) Cheers, Chris See if this code works. I assume that 2010-01 is year and month. ...

Record Macro Relative does not work?
Can someone help me with this macro. I want to go to any cell of my choice and press f2 to edit, then go to the fifth character of that cell, then select the next 6 characters and change them to a smaller font size (8), then "enter". I tried to make this macro using the record macro function, but it did not work how I want it to. It ends up pasting the contents of the cell which I used to record the macro initially. Can this kind of macro be done? To get a relative recorded macro, make sure the stopr recording toolbar is visible, it has 2 icons, one is to s...

Error when no records meet criteria
Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<>""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select ...

Auto Filling Fields in Current Record from Previous Record
Hello, I have looked through posts related to Auto-Filling a field from a previous record but am still a little unsure of how to do things. What I would like to do is auto fill a field in the current record with one from a different field in a previous record. What I have is a form that records "engine hours" and "tonnes moved" for a piece of equipment. The operator will record the staring hours and tonnes (at the start of a day) and the hours and tonnes at the end of the day. So in the database, I would like the "Starting Hours" and "Starting Tonnes"...

Count ifs
Hi I have a list of data shown below, in 2 cloumns. I want to do a count if to show the total people in each department and location i.e. COUNT the number of occurances of people in HEAD OFFICE and PRODUCTION and then the number of occurances of people in HEAD OFFICE and FINANCE and so on.... Many thanks Location Department Head Office Production Birmingham Sales London Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Finance Head Office Sales Head Office Sales Head Office...

Not saving records till "save" command button clicked
If you are in a form and adding information, then close out or go to a new for it just saves the record as long as all applicable fields are filled out. Is there a way to only have a record save if a command button is clicked? -- Message posted via http://www.accessmonster.com If you simply hide the form instead of closing it, I think that will prevent the save. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "szag via AccessMonster.com" <u2885@uwe> wrote in message news:a75249f4b37bb@uwe... > If you are in a form and adding informat...

More than 100 records in Look Up Records dialog window
Does anybody know, how to get more than 100 records in Look Up Records dialog window? For example in Opportunities - I want to add an existing contact to the opportunity, but I can see only 100 records. I dont want to specify any character, I need to choose from all contacts. (It is not the same problem as paging, PagingLimit in SQL can not help) -- thanks Ivana ...

How to sometimes open form to new record?
I have a parent form which is then linked to a child form. I want to have two buttons on the parent form, one to open the child form and display all the records with the corresponding foreign key, and one button to open the child form to a new record with the corresponding foreign key already inserted. I have figured out how to open the child form to a new record, but I cannot figure out how to insert the parent ID into the child foreign key. Help! Thanks. Megan, You don't have to enter the ParentID in the Child subfrom records... Establish a one many relationship between the Pa...

Count number of records after filtering
Hello, Probably a question that's easy to answer but I have been searching for hours now & it's driving me rather crazy: I have applied a filter to a form and now I want to know how many records I have got as a result, or better, I want to know if there are any records left - how do I do that? Tried so far: If Form.Recordset.Count = 0 Then ... If Iserror(Form.RecordSet.Count) Then ... If EOF(Recordset) Then ... but that all does not work. Thanks, Gerwin Gerwin: An easy way is to add a hidden text box, txtCount say, to the form, with a ControlSourc...

Select Records that fall in an external set of ranges (subquery)
HiI have not done subqueries before so I am a bit confused. I have twotables1 - Phone Numbers - (converted to be numeric) (with other fields forlater) and2 - Exchange codes - multiple entries for code with 3 fields -Exchange name (duplication) - Start Range and End Range.I want to return a list of phone numbers that fall into the range fora certain exchange.For example - MRSPOO has 4 entries in the Exchange Table with 4distinct ranges. 200-299, 307-788, 997-1102 and 2036-6698. I would like to be able to return all the phone numbers for a singleexchange code that I would select (using a query...

copy record between tables
Hi, I have two tables with same structure. I need to copy a record from oneto other using vba codes. I'd like avoid to copy field to field.Thank you. Without knowing anything about your table structures, something similar to the following:Dim strSQL As StringstrSQL = "INSERT INTO tblCopy SELECT tblOriginal.* " _ & "FROM tblOriginal WHERE tblOriginal.KeyValue = 4"CurrentProject.Connection.Execute strSQL"Alberto" <saveas[at]iol.it> wrote in message news:uk2oUMBYHHA.3656@TK2MSFTNGP05.phx.gbl...> Hi,> I have two tables with same structure....

Highlight a selected record
I am trying to highlight a selected record of a continuous form. Conditional Formatting is not working because it highlights individual fields. If Conditional Formatting would highlight the entire row, it would be perfect. I created a txtBackground text box to size of all my fields, sent it the back—behind the fields. Use the txtBackground as the control, but when it has focus it covers the data. Below is the code I used: Sub HighlightControl(ctl As Control) On Error Resume Next ctl.BackColor = 65535 End Sub Sub UnhighlightControl(ctl As...

Record data on two lines
Sorry about repeating the post but I think I messed up the first one yeaterday! I am receiving a spreadsheet from an agency with record information on two lines. Example: Record 1: A1, B1, C1, D1, A2, B2, C2 Line 3 blank Record 2: A4, B4, C4, D4, A5, B5, C5 There are over 100 records like this. They claim they can't fix it... It originates from a Crystal Report. How can I change the data so each record is on one line: Record 1: A1, B1, C1, D1, E1, F1, G1 Record 2: A2, B2, .......................G2 Thanks for any help! Dan A simple way is to 1) select all of the data fields. 2) Cli...

Show related records for custom entity
I have a custom entity. When I go to History I can not see the related records from contacts, opportunities, etc. They are not rolling up and there is no pick list to show related records or not. Is there a way to change this? Thanks, -Rick M On Fri, 23 Feb 2007 10:33:49 -0800, Rick M <RickM@discussions.microsoft.com> wrote: >I have a custom entity. When I go to History I can not see the related >records from contacts, opportunities, etc. They are not rolling up and there >is no pick list to show related records or not. Is there a way to change >this? > As...

Can MSCRM 4 handle millions of records (Product)?
Hi, We are in the middle of evaluating MSCRM 4 for a hypermarket. The objective is to load all the warehouse inventory into the Product entity, which are having approximately 6~7 millions of products (including sellable parts and etc.). This information will be available to the VIP card holders (Contact), as well as building royalty progammes and campaign management. Some selected Products will be available for ordering via a Portal (custom build), and the information will be updated to MSCRM as well. So, we need to know that whether MSCRM is a feasible solution for this kind of requi...

ListView insert
I have a ListView that I am using to edit and insert rows into a database table. I am displaying records for a specific person and existing records edit and update fine. Also, if a person already has records in that table, the ListView DropDownList population and insert works fine. The problem is that when a person has no records in the table (ListView is empty) the DropDownLists are not getting populated. I assume maybe because the DDL populating is being done in the ItemDataBound event? If so, where is the best place to populate the dropdowns for insert? Below is some of ...

Not recorded unless tab opened
I created a form with four tabs and (registration, progress,delivery of baby, and after delivery care. When i creat a new patient record in registration by putting name some details in the registration tab, if i close it at that point, the record cannot be recovered back into the form. However its in the table when i look for it. The surprising issue is if i open the delivery of baby tab and i only put in one single information, then i can recover that information on the form . The patient record will only be recovered if i fill the delivery of baby tab and no other tab allows th...

Adding a Record
I have 2 Subforms on my Main form. Each Subform contains a combo box with drop-down list. Their tables are on the many side of the one-to-many relationship with the Main form. When I make a selection on Subform 1, Subform 2 automatically populates with a correlated item from its drop-down list. PROBLEM: when I make a SECOND selection on Subform 1, the item that was listed on Subform 2 gets replaced with the item correlating with the SECOND selection. How can I adjust my code so that the second item is ADDED to the list on Subform 2 instead of replacing the first item? A sample of...

Creating a warning prior to deleting a record
I'm creating a membership database for an organisation. A problem I've encountered is the young lady that will be using the completed database is occasionally using control/z to undo an input error too frequently and deletes the record that she is inputting. As the membership number is an auto-number field this loses that particular membership number which I need to stop from happening. Is there anyway I can generate a warning prior to that final deletion to stop this from occurring? TIA BigAl Autonumber is not recommended for the use you describe, for exactly the reason you...

4.0 Workflow
I am trying to setup a workflow to send an email when an Account record gets deleted. I want to include the name of the Company in the email and who deleted it. The workflow fires, but always has a Status of Waiting because it says "The requested record was not found or you do not have sufficient permissions to view it". The reason this happens, I am assuming, is that the record has already been deleted so data from the record cannot be included in the email. Is there a way I can accomplish what I want? Workflow plugins are asynchronous so by the time you get to the databas...

Counting number of worksheets in a Excel 2007
Dear Sir, I am using MS office 2007, and want to know what is command/formula to count the number of worksheets in a Excel. I deals with some taxation matter wherein I need to have hundreds of sheets in a file. Thank in advance. From within Excel code, Worksheets.Count will return the number of worksheets in the workbook. -- Rick (MVP - Excel) "Y D" <Y D@discussions.microsoft.com> wrote in message news:B2C3A4B9-22F4-4643-8850-BD340C4659C9@microsoft.com... > Dear Sir, > I am using MS office 2007, and want to know what is command/formula to >...

Need to keep record of telephone conversions as in sales
I work from home and in sales i need to keep a record of my telephone conversations, i have outlook which i use but want a excell spreadsheet which i can see all my calls at once. In Excel, create a list of calls. In row 1, enter the headings, e.g. Data, Start, End, Name, etc. To enter the date, type: Ctrl + ; In another column, enter the Start time, by typing: Ctrl + : When the call ends, enter the end time in a third column, by typing: Ctrl + : In additional columns, enter other data about the call, e.g. Person called, purpose, outcome. tracy wrote: > I work from home and...

Importing Records in to CRM 3.0
Hello, On a monthly basis, I import a CSV into the Leads area of CRM. This worked well in 1.2 where I was able to select the "Target" field(s). When I tried this same technique in 3.0, I noticed the "Target" field contained the target field info, but was disabled thus not allowing me to change if I want to. Is this new in 3.0 and if so, is there a work-around Hi Kathy, I guess the target fields which are disabled must be mandatory fields for the lead. In V3 it was enforced that mandatory field has to be mapped and so this restriction. If right source field is ...

Using VBA coding to count color Occurrences
Hi: I am using Office (Word, Excel, and Powerpoint) 2007, and Windows 7, and below is what I'm trying to accomplish, hopefully, with VBA coding: In Columns L & M I have dates that identify a beginning and end date for a class (L is "beginning" date and M is "end date") that a student has signed up for. If the student has not paid for their class, the class dates in Columns L & M are in "bold red font". Once payment has been made, the font changes from "bold red" to "unbold black" thanks to conditional formatting. ...

Filter records not meeting criteria
Hi all, I have a table of training records, and I want to run a query to filter out a list of staff, from a staff table, which indicates those who haven't completed a particular course. TblStaff and TblTraining both have [EmployeeNumber] field relationship. TblTraining has [CourseCode], CourseDesc] fields example 363, Fire Training I want a list of staff from TblStaff who haven't completed their fire training in the last 9 months... Hope that makes sense! thanks in anticipation and cheers DubboPete Use a subquery, Pete. Example in: Subquery Basics: Identifying what is NO...