RENUMBERING KEYS

[amywolfie]

This is an interesting one.....

I have a field on FORM!FORMDATA (no, I did not name this) called KEY.

There can be multiple KEYS per record (equates to FIELDS on a given
DOCUMENT).

The users want to Add a Key and manually assign the number, say 2.
They do this to retain the order in which fields will print on a
document.

If Keys 2 --- 10 already exist, then Old Key 2 becomes 3, Old Key 3
becomes 4, etc.

Adding a new key is triggered by a button click event. Can anyone
supply code to do this?

Thanks!
amy
===

[Larry Linson]

From your description, I don't even understand what you _have_, much less
know how to tell you how to change it. You describe the Form, but not the
underlying Table. You speak of one KEY field, but say there may be multiple
KEYs per record. Where are the other KEYs stored in the record (not, I hope,
as multiple values in the same field... that is a nightmare to try to use)?
The implication here is that you want to add Fields to a Record at
runtime -- which does not make sense. In a relational table, every record
has the same _definition_, but may have different values.

Larry Linson
Microsoft Access MVP

"amywolfie" <am*******@verizon.net> wrote in message
news:33**************************@posting.google.c om...

This is an interesting one.....

I have a field on FORM!FORMDATA (no, I did not name this) called KEY.

There can be multiple KEYS per record (equates to FIELDS on a given
DOCUMENT).

The users want to Add a Key and manually assign the number, say 2.
They do this to retain the order in which fields will print on a
document.

If Keys 2 --- 10 already exist, then Old Key 2 becomes 3, Old Key 3
becomes 4, etc.

Adding a new key is triggered by a button click event. Can anyone
supply code to do this?

Thanks!
amy
===

[Pieter Linden]

am*******@verizon.net (amywolfie) wrote in message news:<33**************************@posting.google. com>...

This is an interesting one.....

I have a field on FORM!FORMDATA (no, I did not name this) called KEY.

There can be multiple KEYS per record (equates to FIELDS on a given
DOCUMENT).

The users want to Add a Key and manually assign the number, say 2.
They do this to retain the order in which fields will print on a
document.

If Keys 2 --- 10 already exist, then Old Key 2 becomes 3, Old Key 3
becomes 4, etc.

Adding a new key is triggered by a button click event. Can anyone
supply code to do this?

Thanks!
amy
===

Open an updateable recordset of the keys in the order you want them
and then update them in code.

dim rs as dao.recordset

set rs = db.openrecordset("SELECT...",dbopenDynamic)
rs.movefirst
intCounter=intCounter+1
do
rs.edit
rs.fields("Key").Value=intCounter
intCounter=intCounter+1
rs.update
rs.movenext
loop until rs.eof

rs.close
set rs=nothing
set db=nothing

.... or something like that...

[Salad]

amywolfie wrote:

This is an interesting one.....

I have a field on FORM!FORMDATA (no, I did not name this) called KEY.

There can be multiple KEYS per record (equates to FIELDS on a given
DOCUMENT).

The users want to Add a Key and manually assign the number, say 2.
They do this to retain the order in which fields will print on a
document.

If Keys 2 --- 10 already exist, then Old Key 2 becomes 3, Old Key 3
becomes 4, etc.

Adding a new key is triggered by a button click event. Can anyone
supply code to do this?

Thanks!
amy
===

You might want to describe your problem again and provide a bit more
detail about the field Key.

Are you wanting to add a new field to the table? IOW, if you have 10
keys in the table, do you want to add another field to the table so you
now have 11?