Sequential or Auto numbering in table

[Kelly]

I have a table and need to add another column, I would like to just add the
first number at the top and the each number follow

Example
field 1
123
124
125
I would like the sequential numbers come up automatically. You can do this
in Access 03, but its not working in 07

 

[Gina Whipp]

Kelly,

You are one of the very few who actually liked that feature. Thankfully,
oh... I mean that feature was removed in 2007. You can use the
DMax("YourField","YourTable")+1 on the Before_Update of your form to acheive
the same results. However, if you are entering data directly into the
table, a big no-no, then in order to accomplish your results you would need
to use a form which you can make to look like a table.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[clare mc]

Sorry Gina but I really valued it also!! I work in tables showing all records
as I'm entering archaeological pottery by context so it was very very helpful
to manually enter a value for my context in one field and then just enter the
first two numbers of a sequence and press the buitton to fill up 1-100 or
1-25 or whatever. I've just upgraded computer so lost Access 2003 and that
tool and miss it terribly. I can always enter up to 100 or so but currently I
have one site with nearly 5000 items so it's a real pain to enter every
number manually.

I have read your very helpful advice to Kelly but I'm still lost as to how
to find where to adjust--I had tried AutoNumber but I can see the problem
with that as it doesn't reset for the next context. Would very much
appreciate you help.
Clare

 

[clare mc]

Ken
Very many thanks for your help.
I accept the words of wisdom that it is not appropriate to enter directly
into a table but I like it and I'm used to it!!
I'm very tempted to re-install Access 2003 as I could just fly through
entries that way. I had assigned values to most fields so 'e' would give me
the excavation number, 'l' would bring up Leinster Cooking Ware and 'b' for
body in their respective fields.
I will attempt to modify my master table by passing your advice to the
resident computer support person but my impression is that I must then come
out of table. Perhaps Access 2003 is the best way?

Clearly Irish medieval pottery!! Currently working on Stafford-type
pre-Norman pottery imported into Dublin!!

Clare

Clare:

Gina's absolutely right (as usual); raw datasheet view of a table is no place
for data entry, which should always be via forms. The best way to do this is
via a form and subform. The form will be based on the Contexts table and the
subform on the Items table. Assuming they are related on columns ContextID
or similar in each table these would be the LinkMasterFields and
LinkChildFields of the subform control. The subform can be in continuous
form or datasheet view, but the parent form must be in single form view.

Having entered a record into the parent form for the context in question rows
can then be inserted into the subform. The ContextID value will be
automatically entered into the subform's underlying Items table via the
linking mechanism. To enter a serial sequence of item numbers into the Items
table you then have two choices:

1. Automatically compute the ItemNumber value as each row is inserted into
the subform. This can be done with an expression of the type Gina posted but
in your case, as you presumably want the sequence to start at 1 for each set
of items from a given context you would need to restrict the rows examined by
the DMax function to those for the current context. You'd do this with code
in the subform's BeforeInsert event procedure. This executes as soon as you
begin to enter data fro a new row into the subform. The code would along
thse lines:

Me.[ItemNumber] = _
Nz(DMax("[ItemNumber]","[Items]","[ContextID] = " & _
Me.[ContextID]), 0)+1

This looks up the highest existing ItemNumber value for the current ContextID
and adds 1. If there are as yet no existing items for the context in
question the DMax function returns a Null, so the Nz function is used to
convert this to a zero, to which 1 is added to give 1 as the first number in
the sequence.

2. The Other approach would be to insert the required number of rows into
the Items table in one operation by means of code, incrementing the
ItemNumber value by 1 in each case, and then requery the subform to show the
new rows. For this you could include two unbound text boxes, txtStartNumber
and txtEndNumber in the parent form, to define the range of numbers to be
inserted, along with a command button, with code along the following lines in
its Click event procedure to insert the rows into the table:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n as Integer

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

'insert required number of rows into Items table
For n = Me.txtStartNumber To Me.txtEndNumber
strSQL = "INSERT INTO [Items] " & _
"([ContextID], [ItemNunber]) " & _
"VALUES(" & Me.[ContextID] & "," & n & ")"

cmd.CommandText = strSQL
cmd.Execute
Next n

' requery subform
Me.sfcItems.Requery

Where sfcItems is the name of the subform control, i.e. the control in the
parent form which houses the subform.

I'd recommend the first option as the latter means you have to be able to
insert multiple rows into the Items table without inserting values into other
columns than the ContextID and ItemNumber columns, i.e. the other columns
have to either allow Nulls or have default values which are inserted
automatically. Your ability to design this table with appropriate
constraints is consequently compromised.

Incidentally, prior to retirement I was the Principal Archaeological Officer
for Staffordshire, so your post has a special affinity for me.

Ken Sheridan
Stafford, England

Sorry Gina but I really valued it also!! I work in tables showing all records
as I'm entering archaeological pottery by context so it was very very helpful
to manually enter a value for my context in one field and then just enter the
first two numbers of a sequence and press the buitton to fill up 1-100 or
1-25 or whatever. I've just upgraded computer so lost Access 2003 and that
tool and miss it terribly. I can always enter up to 100 or so but currently I
have one site with nearly 5000 items so it's a real pain to enter every
number manually.

I have read your very helpful advice to Kelly but I'm still lost as to how
to find where to adjust--I had tried AutoNumber but I can see the problem
with that as it doesn't reset for the next context. Would very much
appreciate you help.
Clare

Kelly,

[quoted text clipped - 18 lines]

.

--



.