Static IDs in autonumbered lookup tables

[Allie]

Hi Folks,

I have a database which uses a bunch of lookup tables. Many of these
lookup tables have auto-generated indicies, and can have records added
to them by the user. I would, however, like to prefill them with a
couple entries whose IDs are not in order (ie, one with ID 98 and one
with ID 99).

Basically, when I export the data, I need 98 and 99 to show up for
particular dropdown values. The rest I'd like to start with 1, 2, etc.
If I haven't confused everyone, is there any way to do this? Thanks a
bunch!

Best,
Allie

 

[John W. Vinson]

Hi Folks,

I have a database which uses a bunch of lookup tables. Many of these
lookup tables have auto-generated indicies, and can have records added
to them by the user. I would, however, like to prefill them with a
couple entries whose IDs are not in order (ie, one with ID 98 and one
with ID 99).

Basically, when I export the data, I need 98 and 99 to show up for
particular dropdown values. The rest I'd like to start with 1, 2, etc.
If I haven't confused everyone, is there any way to do this? Thanks a
bunch!

Best,
Allie

Allie, did you not see John Spencer's reply to the second of your four
postings of this subject?

 

[Allie]

Allie, did you not see John Spencer's reply to the second of your four
postings of this subject?

Sorry about that - I've been having issues with my newsreader, and I've
been offline for quite a while, so when the message finally sent, I was
afriad it would end up at the bottom of everyone's list, and hence not seen.

Yes, thanks John. It is the numbering starting with 100 that we're
trying to avoid. Any ideas how to do this without that issue? Perhaps
putting in our own auto-number event instead of using access'...

thanks,
allie

 

[John W. Vinson]

Yes, thanks John. It is the numbering starting with 100 that we're
trying to avoid. Any ideas how to do this without that issue? Perhaps
putting in our own auto-number event instead of using access'...

If the number means anything, then you certainly should NOT use an autonumber.
Use a Long Integer instead and populate it either manually or using a Form's
code to increment the number. If there will never be more than 100 entries,
you can use the form's BeforeInsert event with code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim NxtID As Integer
NxtID = NZ(DMax("ID", "Tablename", "ID < 98")) + 1
If NxtID = 98 Then
MsgBox "Go home, table full, no more ID's available", vbOKOnly
Cancel = True
Else
Me!ID = NxtID
End If
End Sub