Autonumber? Sequencing?

[Kevin]

I have a database in which one-to-many relationships exist as follows:
Project Number
Under a Project Number can be many Item Numbers
Under each Item Number can be many Key Numbers

The issue: Key numbers start at "001" and can go up to "602", and cannot
duplicate on a project. Each project starts the key numbers at 001, but they
don't alwzys correspond with certain item numbers. So on a project we have
the following example:
Project #5432 / Item #01 / Key 001
Project #5432 / Item #01 / Key 002
Project #5432 / Item #01 / Key 003
Project #5432 / Item #02 / Key 004
Project #5432 / Item #03 / Key 005

Project #6868 / Item #01 / Key 001
Project #6868 / Item #02 / Key 002
Project #6868 / Item #02 / Key 003
Project #6868 / Item #02 / Key 004

Is there a way to sort of AutoNumber the Key field and allow it to start
back at 001 for each project number? Is there then a way to have new records
in the Key Table default to the next available key number? I don't want
users to have to search the records that relate to each item number to try
and find what the next key number should be.

I hope all this makes sense! Any input or suggestion is appreciated!

 

[tina]

well, you can assign the Key number programmatically, when a new Key record
is entered. one way would be to write a Select query the returns all the Key
number records for a specific project (regardless of the Item number). then
use that query in a DMax() function to find the highest key number for the
project, and add 1. something like

Me!KeyNumber = DMax("KeyNumber","MyQuery","ProjectNumber = " &
Me!ProjectNumber) + 1

(watch for line wrap on the above code, it should be a single line.)
if the ProjectNumber is not available in the form's RecordSource, or the
parent form's RecordSource (if the code is running in a subform), then
you'll have to write code to retrieve the correct project number to use in
the DMax() function's criteria.

programmatically assigning an incremental value in a multi-user environment
can be tricky. if two users are entering Key records for the same project at
the same time, you could end up with the same Key value being assigned to
both records. it may be hard to prevent that because presumably the Key
records don't include a field linked directly to the Project table, but only
indirectly linked through the Items table. if you find a way to successfully
prevent duplicate keys in the same project, then you have to also find a way
to handle the error that one user may get when the other user's record gets
saved "first".

also, what happens if you need to add a 603rd Key record to a specific
project? since you say the numbers can only go to 602, you'll have to write
code to handle records over 602.

btw, hopefully your "real" field names don't include a # sign. best practice
is to use only alpha characters (and underscore ( _ ), if necessary) in
anything you name in Access. you can use numeric characters in names, if you
must; recommend you avoid putting them in the first character space; begin
the name with an alpha character. "special" characters and spaces are a
definite no-no.

hth

 

[Kevin]

My field names do not include a # sign. I just did that to keep the post a
little shorter. Two other instances: 2 simultaeous users entering the same
record, and going beyond 602 key numbers will likely never occur.

Thank you for the helpful info. I'll give this a try and post another
question if I get stuck. Thanks again!

 

[tina]

you're welcome, Kevin, and good luck. one note of caution, though, re: "2
simultaeous users entering the same record, and going beyond 602 key numbers
will likely never occur"

"likely" is not good enough, when it comes to database development -
especially when the issue is multi-user data entry. if it's *at all*
possible that two (or more) simultaneous users could enter Key records on
the same Project (the Key records themselves don't have to be "the same",
only the Project) at the same time - then it will happen sooner or later,
and your code needs to be able to handle that situation.

 

[BruceM]

Remember that if a user starts a record, then is called away from the
computer, that record will not be saved until exiting the record unless it is
explicitly saved before then. The record could be saved immediately upon
assigning the number, but if they then decide not to complete the record then
the number is already assigned to a record that contains nothing else. If
the number could be assigned in the form's Before Update event it would
minimize the chance that there would be another user entering a record at the
exact same time. However, it may be that users need (or want) to see the
number when they start the record.
In a similar situation I came up with the following approach. I placed the
code for assigning the number into a General procedure called AssignNumber,
which wrote the number into a text box (txtSequence) bound to the key field.
The form's Current event was:

' Assign the number programatically, and write it to Me.txtSequenceNumber
Call AssignNumber

' If this is a new record, copy the assigned number to an unbound text box
' (txtTemp), and remove the number from the bound text box.
If Me.NewRecord Then
Me.txtTemp = Me.txtSequenceNumber
Me.txtSequenceNumber = Null
' txtTitle is an unbound text box
Me.txtTitle = "Report No. " & Me.txtTemp
Else
Me.txtTemp = ""
Me.txtTitle = "Report No. " & Me.txtSequenceNumber
End If

Then, in the form's Before Update event, I call AssignNumber again, and
compare the result to what is in Me.txtTemp. If they are the same, the
number is written to the bound field behind txtSequenceNumber. If they are
not, the user is informed that the number will change, and AssignNumber is
called again. Finally, just in case there is a collision because two people
create the record at exactly the same time, I use error handling for Error
3022 (duplicate primary key) to generate an error message and try
AssignNumber yet again.

You could also try going to www.rogersaccesslibrary.com and finding a sample
database called AutonumberProblem.mdb. For reason's that I cannot now recall
his approach didn't work in my situation, so I contrived the above.