Commish said:
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.
