OK, so, here's what I am trying to do (Access2007).

I have a form based off of a table (DraftTracker_H). I have a form
built off that table. On the form, I want to display a count of
records (from the column WinninBid)that are greater than a certain
number. So, I created a query (qry11_drafttracker_H) and used the
totals to get that count.

Should I be able to created a field using [qry11_drafttracker_H].
[countofWinningBid] and display that number as it grows on my form?

Did you consider using the DCount domain aggregate function? It does
exactly what you wish to do. You'll find it described in the Help for the
Visual Basic Editor (VBE).

I'm not sure what you're asking... if you are asking whether you can set a
Record Source to use for a single Control, you can set one and only one
RecordSource for a Form. But you can use a Function such as DCount as an
expression in a Control's ControlSource.

Larry Linson
Microsoft Office Access MVP

"Commish" <(E-Mail Removed)> wrote in message
news:28dac8ad-980a-4545-bcdf-(E-Mail Removed)...
> OK, so, here's what I am trying to do (Access2007).
>
> I have a form based off of a table (DraftTracker_H). I have a form
> built off that table. On the form, I want to display a count of
> records (from the column WinninBid)that are greater than a certain
> number. So, I created a query (qry11_drafttracker_H) and used the
> totals to get that count.
>
> Should I be able to created a field using [qry11_drafttracker_H].
> [countofWinningBid] and display that number as it grows on my form?

On Mar 20, 11:26*pm, "Access Developer" <accde...@gmail.com> wrote:
> Did you consider using the DCount domain aggregate function? *It does
> exactly what you wish to do. You'll find it described in the Help for the
> Visual Basic Editor (VBE).
>
> I'm not sure what you're asking... if you are asking whether you can set a
> Record Source to use for a single Control, you can set one and only one
> RecordSource for a Form. But you can use a Function such as DCount as an
> expression in a Control's ControlSource.
>
> *Larry Linson
> *Microsoft Office Access MVP
>
> "Commish" <mark.wol...@gmail.com> wrote in message
>
> news:28dac8ad-980a-4545-bcdf-(E-Mail Removed)...
>
> > OK, so, here's what I am trying to do (Access2007).
>
> > I have a form based off of a table (DraftTracker_H). I have a form
> > built off that table. On the form, I want to display a count of
> > records (from the column WinninBid)that are greater than a certain
> > number. So, I created a query (qry11_drafttracker_H) and used the
> > totals to get that count.
>
> > Should I be able to created a field using [qry11_drafttracker_H].
> > [countofWinningBid] and display that number as it grows on my form?

I have a query that generates a value - [qry11_drafttracker_H].
[countofWinningBid] - I want to be able to display that value on a
form.

Are you saying that I need to create a VBE function to display a value
on a form?

A text box who's data control souce =[CurrentRecord] will show where your
bound form is in it's recordset


A text box who's data control souce =Count([Name]) (where name is a field)
will show the record count




"Commish" <(E-Mail Removed)> wrote in message
news:be63cb79-bbce-4b2a-b844-(E-Mail Removed)...
On Mar 20, 11:26 pm, "Access Developer" <accde...@gmail.com> wrote:
> Did you consider using the DCount domain aggregate function? It does
> exactly what you wish to do. You'll find it described in the Help for the
> Visual Basic Editor (VBE).
>
> I'm not sure what you're asking... if you are asking whether you can set a
> Record Source to use for a single Control, you can set one and only one
> RecordSource for a Form. But you can use a Function such as DCount as an
> expression in a Control's ControlSource.
>
> Larry Linson
> Microsoft Office Access MVP
>
> "Commish" <mark.wol...@gmail.com> wrote in message
>
> news:28dac8ad-980a-4545-bcdf-(E-Mail Removed)...
>
> > OK, so, here's what I am trying to do (Access2007).
>
> > I have a form based off of a table (DraftTracker_H). I have a form
> > built off that table. On the form, I want to display a count of
> > records (from the column WinninBid)that are greater than a certain
> > number. So, I created a query (qry11_drafttracker_H) and used the
> > totals to get that count.
>
> > Should I be able to created a field using [qry11_drafttracker_H].
> > [countofWinningBid] and display that number as it grows on my form?

I have a query that generates a value - [qry11_drafttracker_H].
[countofWinningBid] - I want to be able to display that value on a
form.

Are you saying that I need to create a VBE function to display a value
on a form?

On Mar 21, 1:25*pm, "barret bondon" <supp...@networks-cc.com> wrote:
> A text box who's data control souce *=[CurrentRecord] will show whereyour
> bound form is in it's recordset
>
> A text box who's data control souce =Count([Name]) (where name is a field)
> will show the record count
>
> "Commish" <mark.wol...@gmail.com> wrote in message
>
> news:be63cb79-bbce-4b2a-b844-(E-Mail Removed)....
> On Mar 20, 11:26 pm, "Access Developer" <accde...@gmail.com> wrote:
>
>
>
> > Did you consider using the DCount domain aggregate function? It does
> > exactly what you wish to do. You'll find it described in the Help for the
> > Visual Basic Editor (VBE).
>
> > I'm not sure what you're asking... if you are asking whether you can set a
> > Record Source to use for a single Control, you can set one and only one
> > RecordSource for a Form. But you can use a Function such as DCount as an
> > expression in a Control's ControlSource.
>
> > Larry Linson
> > Microsoft Office Access MVP
>
> > "Commish" <mark.wol...@gmail.com> wrote in message
>
> >news:28dac8ad-980a-4545-bcdf-(E-Mail Removed)....
>
> > > OK, so, here's what I am trying to do (Access2007).
>
> > > I have a form based off of a table (DraftTracker_H). I have a form
> > > built off that table. On the form, I want to display a count of
> > > records (from the column WinninBid)that are greater than a certain
> > > number. So, I created a query (qry11_drafttracker_H) and used the
> > > totals to get that count.
>
> > > Should I be able to created a field using [qry11_drafttracker_H].
> > > [countofWinningBid] and display that number as it grows on my form?
>
> I have a query that generates a value - [qry11_drafttracker_H].
> [countofWinningBid] - I want to be able to display that value on a
> form.
>
> Are you saying that I need to create a VBE function to display a value
> on a form?

If I do Count([Name]) - I will get the total count of all records in
the table - I need to count a subset, which is why I set the value in
a query. Does access support a countif statement?

It works with a filter thrown over the bound form.


Me.Combo88.SetFocus
w = Me.Combo88.Text
Me.Filter = "fd = """ & w & """"
Me.FilterOn = True



"Commish" <(E-Mail Removed)> wrote in message
news:b67f9d04-3faf-4090-8631-(E-Mail Removed)...
On Mar 21, 1:25 pm, "barret bondon" <supp...@networks-cc.com> wrote:
> A text box who's data control souce =[CurrentRecord] will show where your
> bound form is in it's recordset
>
> A text box who's data control souce =Count([Name]) (where name is a field)
> will show the record count
>
> "Commish" <mark.wol...@gmail.com> wrote in message
>
> news:be63cb79-bbce-4b2a-b844-(E-Mail Removed)...
> On Mar 20, 11:26 pm, "Access Developer" <accde...@gmail.com> wrote:
>
>
>
> > Did you consider using the DCount domain aggregate function? It does
> > exactly what you wish to do. You'll find it described in the Help for
> > the
> > Visual Basic Editor (VBE).
>
> > I'm not sure what you're asking... if you are asking whether you can set
> > a
> > Record Source to use for a single Control, you can set one and only one
> > RecordSource for a Form. But you can use a Function such as DCount as an
> > expression in a Control's ControlSource.
>
> > Larry Linson
> > Microsoft Office Access MVP
>
> > "Commish" <mark.wol...@gmail.com> wrote in message
>
> >news:28dac8ad-980a-4545-bcdf-(E-Mail Removed)...
>
> > > OK, so, here's what I am trying to do (Access2007).
>
> > > I have a form based off of a table (DraftTracker_H). I have a form
> > > built off that table. On the form, I want to display a count of
> > > records (from the column WinninBid)that are greater than a certain
> > > number. So, I created a query (qry11_drafttracker_H) and used the
> > > totals to get that count.
>
> > > Should I be able to created a field using [qry11_drafttracker_H].
> > > [countofWinningBid] and display that number as it grows on my form?
>
> I have a query that generates a value - [qry11_drafttracker_H].
> [countofWinningBid] - I want to be able to display that value on a
> form.
>
> Are you saying that I need to create a VBE function to display a value
> on a form?

If I do Count([Name]) - I will get the total count of all records in
the table - I need to count a subset, which is why I set the value in
a query. Does access support a countif statement?

"Commish" <(E-Mail Removed)> wrote:

> Are you saying that I need to create a
> VBE function to display a value
> on a form?

I'm rather certain I neither said nor implied that, only that the builtin
DCount domain aggregate function which can be used in a Control Source is
_explained_ in detail in VBA Help, which you access in the Visual Basic
Editor.

Other builtin functions, usable in Expressions, are also _explained_ in VBA
Help.

In early versions of Access, there was only one type of Help, so it was a
bit easier to find some things.

Larry Linson
Microsoft Office Access MVP

On Mar 21, 4:05*pm, "Access Developer" <accde...@gmail.com> wrote:
> "Commish" <mark.wol...@gmail.com> wrote:
>
> *> Are you saying that I need to create a
> *> VBE function to display a value
> *> on a form?
>
> I'm rather certain I neither said nor implied that, only that the builtin
> DCount domain aggregate function which can be used in a Control Source is
> _explained_ in detail in VBA Help, which you access in the Visual Basic
> Editor.
>
> Other builtin functions, usable in Expressions, are also _explained_ in VBA
> Help.
>
> In early versions of Access, there was only one type of Help, so it was a
> bit easier to find some things.
>
> *Larry Linson
> *Microsoft Office Access MVP

OK... let me ask this a different way.

I want to add a value to a table as I use my application, and populate
the a table called "DraftTracker_H" - most of the values will be typed
in, or selected from a pull down list. That part is working fine.

But, there's one value that I want to create as I populate the data,
and it needs to be a counter - I want to start at 1 and add 1 to the
column (let's call it PlayerCount - so, the calculation would be
something like =(1+max(PlayerCount))). What's is the easiest way to do
this? Does it require VBA?

"Commish" <(E-Mail Removed)> wrote

> OK... let me ask this a different way.

> I want to add a value to a table as I use
> my application, and populate the a table
> called "DraftTracker_H" - most of the values
> will be typed in, or selected from a pull
> down list. That part is working fine.

> But, there's one value that I want to create
> as I populate the data, and it needs to be a
> counter - I want to start at 1 and add 1 to the
> column (let's call it PlayerCount - so, the calcu-
> lation would be something like
> =(1+max(PlayerCount))). What's is the easiest
> way to do this? Does it require VBA?

Help me understand. You _add records to the table_ ("populate the table" is
not awfully precise, because some might use that to mean either adding new
records or updating existing ones) and you want each new record to have a
monotonically increasing "record number"?

If that is so, then my question is, "What is the purpose of this record
number? How do you intend to use it?" I ask because, unlike many other
databases, Access purposely omitted the 'record number' since it can be
misleading. Access provides an Autonumber which is usable for joining
records in related tables, but assumes that you have a natural way to order
your records, not just a "record number" indicating "the order in which they
are entered". That is not only "the Access way" but "the relational database
way". The Autonumber is intended to be unique, but not monotonically
increasing (and sometimes is not... in fact, there is even 'randomized'
option called a Globally Unique ID, or GUID, that can be used).

If you explain, then there's the possibility that someone here can suggest a
different, possibly easier or better approach to accomplish your purpose.

You seem quite focused on "must I use VBA?". What's your concern?

Access does, perhaps, try to "protect us from ourselves", by not making it
as easy as it might be to create a "record number", but if, as you indicate,
you are going to enter all data from an Access Form, here's a simple demo
you can do for yourself.

Create a table, named "tblNumbered", with its first field being a Text field
named "Anything", and another field, this one Number, Long Integer. Save the
Table. Use the AutoForm button to create a Form, and switch to Design View,
and first delete the Label and Text Box for RecNo. (It will work with or
without that Text Box, but we are demonstrating, among other things that you
don't need it.)

Right click the upper leftmost little square, and choose Properties to
display the Form's Property Sheet. Click the Data tab and on the Data Entry
line, choose "Yes", then click the "Events" tab, choose "Before Insert", and
click the box with three little dots, then choose "Code Builder". That will
open up a module window with the Sub and End Sub lines already filled in.
Add one line between them as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)

RecNo = DCount("*", "tblNumbered") + 1

End Sub

Go back to the Form, and click the Form View button on the toolbar. Now put
some text in the "Anything" box and press the tab key to move to the next
record... do this a few times, and open the Table View of tblNumbered. You
should see several records with your text and a monotonically increasing
Record Number.

Caution: if you add records from table view, or add records with a query, or
add records from code, RecNo will NOT be set. It's possible, I suppose, that
with enough time and energy, we might figure a way to do it so that you
didn't have to do it with a form.

That uses VBA, but one line of VBA is about as simple as you can get. Can
you do this with a macro? Maybe, but I don't use macros -- I can do anything
with VBA that I can do with a macro, but I can't do anything with a macro
that I can do with VBA.

On Mar 22, 6:53*pm, "Access Developer" <accde...@gmail.com> wrote:
> "Commish" <mark.wol...@gmail.com> wrote
>
> *> OK... let me ask this a different way.
>
> *> I want to add a value to a table as I use
> *> my application, and populate the a table
> *> called "DraftTracker_H" - most of the values
> *> will be typed in, or selected from a pull
> *> down list. That part is working fine.
>
> *> But, there's one value that I want to create
> *> as I populate the data, and it needs to be a
> *> counter - I want to start at 1 and add 1 to the
> *> column (let's call it PlayerCount - so, the calcu-
> *> lation would be something like
> *> =(1+max(PlayerCount))). What's is the easiest
> *> way to do this? Does it require VBA?
>
> Help me understand. You _add records to the table_ ("populate the table" is
> not awfully precise, because some might use that to mean either adding new
> records or updating existing ones) and you want each new record to have a
> monotonically increasing "record number"?
>
> If that is so, then my question is, "What is the purpose of this record
> number? How do you intend to use it?" *I ask because, unlike many other
> databases, Access purposely omitted the 'record number' since it can be
> misleading. *Access provides an Autonumber which is usable for joining
> records in related tables, but assumes that you have a natural way to order
> your records, not just a "record number" indicating "the order in which they
> are entered". That is not only "the Access way" but "the relational database
> way". The Autonumber is intended to be unique, but not monotonically
> increasing (and sometimes is not... in fact, there is even 'randomized'
> option called a Globally Unique ID, or GUID, that can be used).
>
> If you explain, then there's the possibility that someone here can suggest a
> different, possibly easier or better approach to accomplish your purpose.
>
> You seem quite focused on "must I use VBA?". *What's your concern?
>
> Access does, perhaps, try to "protect us from ourselves", by not making it
> as easy as it might be to create a "record number", but if, as you indicate,
> you are going to enter all data from an Access Form, here's a simple demo
> you can do for yourself.
>
> Create a table, named "tblNumbered", with its first field being a Text field
> named "Anything", and another field, this one Number, Long Integer. Save the
> Table. Use the AutoForm button to create a Form, and switch to Design View,
> and first delete the Label and Text Box for RecNo. (It will work with or
> without that Text Box, but we are demonstrating, among other things that you
> don't need it.)
>
> Right click the upper leftmost little square, and choose Properties to
> display the Form's Property Sheet. Click the Data tab and on the Data Entry
> line, choose "Yes", then click the "Events" tab, choose "Before Insert", and
> click the box with three little dots, then choose "Code Builder". That will
> open up a module window with the Sub and End Sub lines already filled in.
> Add one line between them as follows:
>
> Private Sub Form_BeforeInsert(Cancel As Integer)
>
> * * RecNo = DCount("*", "tblNumbered") + 1
>
> End Sub
>
> Go back to the Form, and click the Form View button on the toolbar. *Now put
> some text in the "Anything" box and press the tab key to move to the next
> record... do this a few times, and open the Table View of tblNumbered. *You
> should see several records with your text and a monotonically increasing
> Record Number.
>
> Caution: if you add records from table view, or add records with a query,or
> add records from code, RecNo will NOT be set. It's possible, I suppose, that
> with enough time and energy, we might figure a way to do it so that you
> didn't have to do it with a form.
>
> That uses VBA, but one line of VBA is about as simple as you can get. *Can
> you do this with a macro? Maybe, but I don't use macros -- I can do anything
> with VBA that I can do with a macro, but I can't do anything with a macro
> that I can do with VBA.

Thanks for responding - here's the requested detail. I have a table
with a set of records. I will not be adding records, but I will be
updating the missing data in the columns. Each records represents a
baseball player, right now, I know the population of players, but I do
not kow which teams they will end up on and what their salary and
position will be. That is the additional data that I am populating.

The counter that I want to add would be used to track the order in
which the records are populated. It's important to me to know which
player was selected first, 2nd, etc. So, I know the total number of
records, but I need to track a subset of those records. I know how to
generate the number, but I am having trouble generating it onto an
existing form.

My comment about VBA came up because I can calculate the counts and
sums in a query. But if I put the field from the query on the form, it
doesn't display the value - I see a #Name error. And the responses all
indicated that using VBA would handle that feature. It just seems that
dropping a calculation on a form should work....