Problem with shared database record IDs

[DABS]

I have a projects database that automatically assigns project numbers based
on project prefix name + current date + ID Number (consecutive for that day).


A field concanates all three into one ID (Project ID) which is automatically
assigned when a new record is created via a form. The project prefix name
is selected from a dropdown box, which is the first field inthe form. The
Project ID field is then automatically created. For example if I selected
"ABC123" as the prefix and two "ABC123" records already existed for today,
the ID would automatically be ABC12310240603.

The problem is when two users open the front end DB form and select the same
prefix, they are assigned the same automatic ID since that ID number has not
been saved yet.

I need a way to prevent duplicates in the project table. Since one user
will be saving or moving to a new record before the other, is there a way to
prompt the other user when they save/go to a new record that this ID is a
duplicate and the next consecutive/available ID will be assigned?

Any other ideas?

I am not a true developer so please if you have an idea explain it to me as
detailed as possible.

Thanks in advance!!

 

[Zac Woodall [MSFT]]

Did you try multi-selecting all three fields int he table designer and
making them into the primary key (yes, a multi-field primary key)?

Zac Woodall | Program Manager | Microsoft Access

 

[Van T. Dinh]

It sounds to me that your code assigns the ID number as soon as the user
selects the prefix. Thus, if another user starts the data entry with same
prefix before the first user saves the record, the second user will get the
same ID.

There are a number of different ways of collision avoidance and collision
resolution. I tend to use collision avoidance more but I think the
collision resolution you asked is to use the Form_BeforeUpdate Event to
check whether this ID (same prefix) has been updated into the Table in the
mean time (since the ID was "temporarily" allocated). If it hasn't, let the
update go through. If it has been used (by someone's else), obtain a new ID
and give the user the message about change of ID / ProjectID.

BTW, it sounds to me that you actually store the (combined / full) ProjectID
"ABC12310240603" in the Table. This is actually a calculated value and it
may be more efficient to store the "ProjectID" as separate values in 3
different Fields: Prefix, DateCreated and SeqNo. This way, your data is
more atomic and you can use the values independently. When you want to
display / print the full ProjectID, it is a simply process of combining them
to give you the required format "ABC12310240603". I think Zac was thinking
of the same line in his advice.

 

[DABS]

It sounds to me that your code assigns the ID number as soon as the user
selects the prefix. Thus, if another user starts the data entry with same
prefix before the first user saves the record, the second user will get the
same ID.

There are a number of different ways of collision avoidance and collision
resolution. I tend to use collision avoidance more but I think the
collision resolution you asked is to use the Form_BeforeUpdate Event to
check whether this ID (same prefix) has been updated into the Table in the
mean time (since the ID was "temporarily" allocated). If it hasn't, let the
update go through. If it has been used (by someone's else), obtain a new ID
and give the user the message about change of ID / ProjectID.

BTW, it sounds to me that you actually store the (combined / full) ProjectID
"ABC12310240603" in the Table. This is actually a calculated value and it
may be more efficient to store the "ProjectID" as separate values in 3
different Fields: Prefix, DateCreated and SeqNo. This way, your data is
more atomic and you can use the values independently. When you want to
display / print the full ProjectID, it is a simply process of combining them
to give you the required format "ABC12310240603". I think Zac was thinking
of the same line in his advice.

No actually I am storing the data in three different fields and combining
them on the form only. You said to use the Form_BeforeUpdate Event to
check whether this ID (same prefix) has been updated into the Table in the
mean time (since the ID was "temporarily" allocated). If it hasn't, let the
update go through. If it has been used (by someone's else), obtain a new ID
and give the user the message about change of ID / ProjectID. I need
someone to explain to me how to do this.

THANKS!!!!

 

[DABS]

Did you try multi-selecting all three fields int he table designer and
making them into the primary key (yes, a multi-field primary key)?

Zac Woodall | Program Manager | Microsoft Access

Yes we tried this and it caused problems with the combined field on the
form.

 

[Van T. Dinh]

Do you know how to create code stubs for the Form_BeforeUpdate Event?

Asuuming that you have controls on the Form:

* cboPrefix bound to Field Prefix (Text data type)
* txtDateCreated bound to Field DateCreated (Date datatype)
* txtIDSeqNumber bound to IDSeqNumber (Numeric data type)

, the code should be something like (***untested***):
********
Dim intCount As Integer

intCount = DCount("*", "([YourTable]", "[Prefix] = '" & Me.Prefix & "') And
" & _
"([DateCreated] = " & Format(Me.DateCreated, "\#mm\/dd\/yyyy\#") & ")
And " & _
"([IDSeqNumber] = " & Me.IDSeqNumber & ")")

If intCount > 0 Then
' ID has been used, allocate new ID
Else
' OK. Do nothing to let the Update through
End If
********

There are other methods to get the count also, e.g. using Recordset ...

 

[DABS]

Was able to get it to work using this Form_BeforeUpdate Event:


Dim intCount As Integer
Dim bNotifyOfChange As Boolean

'intCount = DCount("*", "[tblProjects]", "([Project_Code] = '" & Me.PCode &
"') And " & _
' "([Project_ID] = " & Me.ProjID & ")")
If bDetermineProjID Then
intCount = 1
bNotifyOfChange = False

Do While intCount > 0
intCount = DCount("*", "[tblProjects]", "([Project_Code] = '" &
Me.PCode & "') And " & _
"(Format([Entry_Date], ""mm/dd/yy"")= '" & Format(Me.EDate,
"mm/dd/yy") & "') And " & _
"([Project_ID] = " & Me.ProjID & ")")

If intCount <> 0 Then
Me.ProjID = Me.ProjID + 1
bNotifyOfChange = True
End If

Loop

If bNotifyOfChange Then
MsgBox "Project ID Changed to: " & Me.ProjID, vbOKOnly, "Information"
End If
End If


Thanks for your help!

 

[Van T. Dinh]

I am not sure about your code ("bDetermineProjID" was not declared or its
value set but used in the code you posted) but I don't see your whole
database.

However, if it works for you using the BeforeUpdate Event ... that's the
main thing ...

 

[aaron.kempf]

you should use a real autonumber instead of your complex keys

performance, scalability, concurrency

-Aaron