Record ID # - recommendation is not to rely on this #.

[StargateFanFromWork]

That's understandable. But what if we need an autonumbering type of number
that corresponds to a tab in a binder - and the Access db is partly to make
up an index sheet of that and many other binders. It's gotten to be too
much to do in Excel. We need a better system and Access is so much easier
once it's been set up.

Let's say I have record #1 as that's the first one I input in that table.
The user then know that that data is under tab #1 in a particular binder.
If we're not to use, or shouldn't use, the record ID #, what can be used
instead of this? If I have 100 records and we delete one or two, if
autonumbering can't be relied upon anymore because of that type of thing,
how would autonumbering another field help? Or is there a numbering method
that wouldn't rely on this particular Access feature, but perhaps could be
generated as next-in-line no matter what records lost in a crash or deleted,
etc.

I hope people are understanding what I mean. In Excel, if we delete a row,
we would just replace it with another # and place the data in the
corresponding tab. Deleted rows are not a problem and having items out of
order are not either. We then sort by the data so that it is in
alphanumeric order. The user or others just go to the appropriate tab in
the binder.

 

[david epsom dot com dot au]

In Excel, you don't really delete rows: you just empty or fill them.

You can do the same in Access. Create a table, add some rows, and
remove the delete permission.

Then when you want to delete a binder, just change the data for
that row back to Null, Null, Null, whatever.

(david)

 

[StargateFan]

In Excel, you don't really delete rows: you just empty or fill them.

You can do the same in Access. Create a table, add some rows, and
remove the delete permission.

Then when you want to delete a binder, just change the data for
that row back to Null, Null, Null, whatever.

Oh, dear. Looks like I confused the issue.

I've read that we shouldn't use the autonumbering ID as good
reference. But we need that type of thing. What can we use _besides_
the autonumbering for the users to refer to. Understandably, if
records are deleted that throws the autonumbering off, which is no
good for our purposes, though fine for the db.

Thanks.

 

[John Vinson]

Or is there a numbering method
that wouldn't rely on this particular Access feature, but perhaps could be
generated as next-in-line no matter what records lost in a crash or deleted,
etc.

You can generate a "custom counter" if you ALWAYS do your data entry
using a Form. (Table datasheets don't have any usable events).

Use a Long Integer primary key (*not* an Autonumber; despite Access'
trying to push them, it is not essential to have autonumbers in any
table unless you want them. In this case you don't). Let's call it ID.

In the Form, have a control bound to this ID field - name it txtID
say.

In the Form's BeforeInsert event, click the ... icon and choose Code
Builder. Edit the code to

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[YourTableNameHere])) + 1
End Sub


This will look up the largest existing ID; add one, and store the
result.

John W. Vinson[MVP]

 

[StargateFan]

Or is there a numbering method
that wouldn't rely on this particular Access feature, but perhaps could be
generated as next-in-line no matter what records lost in a crash or deleted,
etc.

You can generate a "custom counter" if you ALWAYS do your data entry
using a Form. (Table datasheets don't have any usable events).

Use a Long Integer primary key (*not* an Autonumber; despite Access'
trying to push them, it is not essential to have autonumbers in any
table unless you want them. In this case you don't). Let's call it ID.

In the Form, have a control bound to this ID field - name it txtID
say.

In the Form's BeforeInsert event, click the ... icon and choose Code
Builder. Edit the code to

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[YourTableNameHere])) + 1
End Sub


This will look up the largest existing ID; add one, and store the
result.

We can definitely limit the input to a form (via switchboard), so this
is very do-able.

Going to start googling in archives for help re this. Thanks!

 

[John Vinson]

We can definitely limit the input to a form (via switchboard), so this
is very do-able.

Going to start googling in archives for help re this. Thanks!

um?

Just copy and paste the three lines. Edit the field and tablenames.
You're done.

John W. Vinson[MVP]

 

[StargateFanFromWork]

um?

Just copy and paste the three lines. Edit the field and tablenames.
You're done.

John W. Vinson[MVP]

<g> I'm getting to know you, Mr. Vinson. To you, everything is easy.

Copy and paste ... to where, exactly? T'ain't all that easy. I've been
googling past posts of yours and, to you, everything is easy <lol>. I've
already ran head-on into brick walls, so I'll keep that copy/paste in mind.
When I know where, will do. Thanks. D

 

[John Vinson]

<g> I'm getting to know you, Mr. Vinson. To you, everything is easy.

Copy and paste ... to where, exactly? T'ain't all that easy. I've been
googling past posts of yours and, to you, everything is easy <lol>. I've
already ran head-on into brick walls, so I'll keep that copy/paste in mind.
When I know where, will do. Thanks. D

Sorry for being obscure...

In the Form's BeforeInsert event, click the ... icon and choose Code
Builder. Edit the code to

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[YourTableNameHere])) + 1
End Sub

Open the Form in design view.
View its Properties by rightclicking the little square at the upper
left intersection of the rulers.
On the Event tab, one of the rows is labeled "Before Insert".
Click the ... icon next to this row.
You'll get a choice of Macro, Expression or Code Builder. Select Code
Builder.
Access will give you the Sub and the End Sub lines for free.
Copy and paste the one line above in between these.
Change txtID to the Name of the textbox, [ID] to the Name of the
primary key field, and YourTableNameHere to... well, you get the
idea... <g>

John W. Vinson[MVP]

 

[StargateFan]

<g> I'm getting to know you, Mr. Vinson. To you, everything is easy.

Copy and paste ... to where, exactly? T'ain't all that easy. I've been
googling past posts of yours and, to you, everything is easy <lol>. I've
already ran head-on into brick walls, so I'll keep that copy/paste in mind.
When I know where, will do. Thanks. D

Sorry for being obscure...

In the Form's BeforeInsert event, click the ... icon and choose Code
Builder. Edit the code to

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[YourTableNameHere])) + 1
End Sub

Open the Form in design view.
View its Properties by rightclicking the little square at the upper
left intersection of the rulers.
On the Event tab, one of the rows is labeled "Before Insert".
Click the ... icon next to this row.
You'll get a choice of Macro, Expression or Code Builder. Select Code
Builder.
Access will give you the Sub and the End Sub lines for free.
Copy and paste the one line above in between these.
Change txtID to the Name of the textbox, [ID] to the Name of the
primary key field, and YourTableNameHere to... well, you get the
idea... <g>

Wow, thank you! You are very kind. My head is whirling, must admit
but I'm making great strides and am coming along very quicly, but this
will save me so much study time for this task. Thank you, thank you!
D