RE-NUMBER KEYS WHEN NEW KEY ADDED

[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 running this code:

Private Sub butAddNew_Click()

On Error GoTo Err_butAddNew_Click

Dim DOCNUMBER As String
Dim SegmentNumber As String
Dim NextKey As Long, Dummy
DOCNUMBER = Form_Number
'DoCmd DoMenuItem 0, 0, 4, 0, A_MENU_VER20 ' Tries to Save
Record
NextKey = GetNextKey(Val(DOCNUMBER))
DoCmd.GoToRecord , , A_NEWREC
Form_Number = DOCNUMBER
'KEY value now on Subform -- aww 8/14/04
Forms![sfrmKeys_AW]![KEY] = NextKey
Forms![sfrmKeys_AW]![KEY].SetFocus

Exit_butAddNew_Click:
Exit Sub

DoCmd.SetWarnings (WarningsOn)

Err_butAddNew_Click:
MsgBox Error$
Resume Exit_butAddNew_Click

End Sub
===

Any idea on how to renumber Keys as described above?

Thanks!

amy
==

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe it would be better to just set the new key to the last old
key, plus one. E.g.:

key = dmax("key","table_name") + 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQR1lrIechKqOuFEgEQLXfQCgoUa6MYeIbNlEi08KGtUIy6 PJwj8An033
fJEcja0dRSnyTC7MpMwG9WjN
=Xfnl
-----END PGP SIGNATURE-----
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 running this code:

Private Sub butAddNew_Click()

On Error GoTo Err_butAddNew_Click

Dim DOCNUMBER As String
Dim SegmentNumber As String
Dim NextKey As Long, Dummy
DOCNUMBER = Form_Number
'DoCmd DoMenuItem 0, 0, 4, 0, A_MENU_VER20 ' Tries to Save
Record
NextKey = GetNextKey(Val(DOCNUMBER))
DoCmd.GoToRecord , , A_NEWREC
Form_Number = DOCNUMBER
'KEY value now on Subform -- aww 8/14/04
Forms![sfrmKeys_AW]![KEY] = NextKey
Forms![sfrmKeys_AW]![KEY].SetFocus

Exit_butAddNew_Click:
Exit Sub

DoCmd.SetWarnings (WarningsOn)

Err_butAddNew_Click:
MsgBox Error$
Resume Exit_butAddNew_Click

End Sub
===

Any idea on how to renumber Keys as described above?

Thanks!

amy
==

[amywolfie]

Unfortunately, that won't work, based on the user requirements.

amy
===

MGFoster <[e-mail]> wrote in message news:<PA*****************@newsread1.news.pas.earth link.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I believe it would be better to just set the new key to the last old
key, plus one. E.g.:

key = dmax("key","table_name") + 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQR1lrIechKqOuFEgEQLXfQCgoUa6MYeIbNlEi08KGtUIy6 PJwj8An033
fJEcja0dRSnyTC7MpMwG9WjN
=Xfnl
-----END PGP SIGNATURE-----
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 running this code:

Private Sub butAddNew_Click()

On Error GoTo Err_butAddNew_Click

Dim DOCNUMBER As String
Dim SegmentNumber As String
Dim NextKey As Long, Dummy
DOCNUMBER = Form_Number
'DoCmd DoMenuItem 0, 0, 4, 0, A_MENU_VER20 ' Tries to Save
Record
NextKey = GetNextKey(Val(DOCNUMBER))
DoCmd.GoToRecord , , A_NEWREC
Form_Number = DOCNUMBER
'KEY value now on Subform -- aww 8/14/04
Forms![sfrmKeys_AW]![KEY] = NextKey
Forms![sfrmKeys_AW]![KEY].SetFocus

Exit_butAddNew_Click:
Exit Sub

DoCmd.SetWarnings (WarningsOn)

Err_butAddNew_Click:
MsgBox Error$
Resume Exit_butAddNew_Click

End Sub
===

Any idea on how to renumber Keys as described above?

Thanks!

amy
==