How to insert records in a form

[reeve13]

Is there a way I could insert a record in a continuous for
and have the numbering field updates itself? I currently have th
CounterID set to(number) instead of auto number. (e.g.) If I want t
insert data in row # 200, I want the original data in row 200 updat
to row 201 (just like in excel).

Can this be done?

Thanks

 

[Rob Parker]

Yes.

I'm sure it could be done, if you were bloody-minded enough!

BUT

I can't envision any sensible database where you would actually want/need to
do this. If you do really want/need to do so, why don't you export your
data to Excel and do it there. Then, if you are really determined to have
your data in an Access database, re-import the data and replace your
previous table.

I suggest you get a better basic understanding of what a relational database
is, and does.

I certainly wouldn't bother trying to figure out exactly how to do what you
asked

Rob

 

[John Vinson]

Is there a way I could insert a record in a continuous form
and have the numbering field updates itself? I currently have the
CounterID set to(number) instead of auto number. (e.g.) If I want to
insert data in row # 200, I want the original data in row 200 update
to row 201 (just like in excel).

Can this be done?

It's actually pretty easy.

In the Form's BeforeInsert event, click the ... icon, invoke the Code
Builder, and put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!CounterID = NZ(DMax("[CounterID]", "[your-table-name]")) + 1
End Sub


John W. Vinson[MVP]

 

[Rob Parker]

John,

I think that one of us has missed the point of the OP's question. Your
reply is fine if he was asking how to avoid using autonumbering, and apply a
consecutive number as the CounterID for a new record. Maybe that's what he
really wants, but what he asked was how to insert a new record with a
specific CounterID number, and have existing records renumbered so that the
new record fits into the sequence as the chosen insertion point. This would
involve updating the CounterID number for all records after the number he
choses to insert (and, if CounterID is the primary key, this must be done
avoiding any temporary duplication along the way).

I think you'd agree that that is actually not pretty easy ;-)

Rob

Is there a way I could insert a record in a continuous form
and have the numbering field updates itself? I currently have the
CounterID set to(number) instead of auto number. (e.g.) If I want to
insert data in row # 200, I want the original data in row 200 update
to row 201 (just like in excel).

Can this be done?

It's actually pretty easy.

In the Form's BeforeInsert event, click the ... icon, invoke the Code
Builder, and put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!CounterID = NZ(DMax("[CounterID]", "[your-table-name]")) + 1
End Sub


John W. Vinson[MVP]

 

[John Vinson]

Is there a way I could insert a record in a continuous form
and have the numbering field updates itself? I currently have the
CounterID set to(number) instead of auto number. (e.g.) If I want to
insert data in row # 200, I want the original data in row 200 update
to row 201 (just like in excel).

I was mistaken, and Rob's quite right. You do NOT want to do this
ordinarily! Bear in mind that these numbers can take on a life outside
of this table. If you have 31228 rows in your table, and want to
insert a new Row# 5, do you *really* want to renumber 31222 records?
And do you want to change the value of this field in all the tables
that this table is linked to, on all the printouts where they are
written out, on all the memos and Post-It notes and human memories
where folks are all excited about item 30146 (which has now become
30147)?

John W. Vinson[MVP]

 

[John Vinson]

I think that one of us has missed the point of the OP's question.

yep... and it was me. You're quite right!

Sorry for reading too hastily!

John W. Vinson[MVP]