Duplicate Primary Key and DCount problems

[MKammerer via AccessMonster.com]

I have been trying everything I can to get this to work and I'm still stumped.
I have a table which has numeric primary keey value which is set by a default
value expression but the last 2 digits of the default value expression are
always 01 and I would like to be able to increase that value to 02, 03, 04 ...
etc. as more records are entered. Using an autonumber field would not work
because the default value expression uses today's date to calculate an ID
number and then adds the 2 digits so that more than 1 record can be created
in a single day. Anyhow I have been using a form to add records to the field
and been using the BeforeUpdate event on the text field of the Primary Key to
test the table and see if the Default Expression is already present in the
table. If so then it simply adds 1 to the field. I went through a long task
of finally getting the syntax right on the DCount expression I use and now I
get no errors but the process of adding 1 doesn't seem to be happening
properly. Ieven added a new text box and told the function to update that
text box instead of the one containing the primary key just to test that the
function was working, and no dice, that new text box stays empty when I enter
a known duplicate value. The BeforeUpdate function I'm using is below:

Private Sub Article_ID_BeforeUpdate(Cancel As Integer)
Dim AID As Long
Dim stLinkCriteria As String

AID = Me.Article_ID
stLinkCriteria = "[Article ID] = " & AID

If DCount("[Article ID]", "Articles", stLinkCriteria) > 1 Then
Me.Text16 = AID + 1
End If
End Sub

Articles is the table containing the numeric primary key
[Article ID] is the name of the numeric primary key in Articles
Me.Article_ID is the text box on the form whose control source is [Article ID]
me.Text16 is a temporary text box I created to test the function

Not sure where to go from here. Any help would be appreciated. Thanks.

 

[bhicks11 via AccessMonster.com]

I don't know about the program but it certainly confused me to have
everything named ARTICLE_ID. Maybe you should vary the naming.

Bonnie
http://www.dataplus-svc.com

I have been trying everything I can to get this to work and I'm still stumped.
I have a table which has numeric primary keey value which is set by a default
value expression but the last 2 digits of the default value expression are
always 01 and I would like to be able to increase that value to 02, 03, 04 ...
etc. as more records are entered. Using an autonumber field would not work
because the default value expression uses today's date to calculate an ID
number and then adds the 2 digits so that more than 1 record can be created
in a single day. Anyhow I have been using a form to add records to the field
and been using the BeforeUpdate event on the text field of the Primary Key to
test the table and see if the Default Expression is already present in the
table. If so then it simply adds 1 to the field. I went through a long task
of finally getting the syntax right on the DCount expression I use and now I
get no errors but the process of adding 1 doesn't seem to be happening
properly. Ieven added a new text box and told the function to update that
text box instead of the one containing the primary key just to test that the
function was working, and no dice, that new text box stays empty when I enter
a known duplicate value. The BeforeUpdate function I'm using is below:

Private Sub Article_ID_BeforeUpdate(Cancel As Integer)
Dim AID As Long
Dim stLinkCriteria As String

AID = Me.Article_ID
stLinkCriteria = "[Article ID] = " & AID

If DCount("[Article ID]", "Articles", stLinkCriteria) > 1 Then
Me.Text16 = AID + 1
End If
End Sub

Articles is the table containing the numeric primary key
[Article ID] is the name of the numeric primary key in Articles
Me.Article_ID is the text box on the form whose control source is [Article ID]
me.Text16 is a temporary text box I created to test the function

Not sure where to go from here. Any help would be appreciated. Thanks.

 

[Allen Browne]

The logic is flawed here.

Say you 3 entries for article 999. If I understand, you use values 99901,
99902, and 99903. Someone deletes 99902. The DCount() will now return 2
matching articles, even though the 03 is being used.

 

[bhicks11 via AccessMonster.com]

Okay, I'll bite:

Why does this have [ARTICLE ID] =" & AID and not [ARTICLE ID] & AID?

and shouldn't the DCOUNT be

If DCount("[Article ID]", "Articles", stLinkCriteria > 1) Then?

Instead of creating the ID on the fly, why not have it already calculated in
a field as the record is created as a permanent ID and refer to it?


Bonnie
http://www.dataplus-svc.com

The logic is flawed here.

Say you 3 entries for article 999. If I understand, you use values 99901,
99902, and 99903. Someone deletes 99902. The DCount() will now return 2
matching articles, even though the 03 is being used.

I have been trying everything I can to get this to work and I'm still
stumped.

[quoted text clipped - 45 lines]

Not sure where to go from here. Any help would be appreciated. Thanks.

 

[MKammerer via AccessMonster.com]

The default value for the field is a julian date with 2 digits at the end.
The reason I did this is to refer to each entry with a date format and enable
the user to create more than 1 entry per day. With the type of data the
table stores it would be near impossible to create more than 100 entires per
day (and if that starts to happen then I will have to redesign the field).
If a record is deleted, chances are that someone will be entering a new
record at a later date and that will default to 01 so since the first 7
digits are different (because of the date change) it won't matter, however if
someone deletes a record from today and then tries to enter a new record on
the same date, I don't care if the new record gets the deleted record's old
PK value. I think this is what you are talking about but I'm not sure if
that is what you meant.
So essentially using your example if I have enteries for today (7/11/08) the
default entry for the PK is 200819301. And every record entered today will
have that PK value unless changed by the user to 200819302, 200819303, etc.
So lets assume I have 3 entries like you said with those PK values, and its
still 7/11/08 and someone deletes 200819302, and then tries to enter a new
record (still today) whose default PK will be 200819301. DCount should only
return 1 value because the new record being searched is 200819301 and only 1
of those exists. Then if the code works it should add one to the default PK
and set it to 200819302 which according to the table is not being used (even
though it was in use before). I also realize that its possible to create a
duplicate by only adding 1 one time without a loop to recheck until it finds
an empty spot but I would just like to get this part working before I add the
loop since I was having so much trouble with DCount before.

The logic is flawed here.

Say you 3 entries for article 999. If I understand, you use values 99901,
99902, and 99903. Someone deletes 99902. The DCount() will now return 2
matching articles, even though the 03 is being used.

I have been trying everything I can to get this to work and I'm still
stumped.

[quoted text clipped - 45 lines]

Not sure where to go from here. Any help would be appreciated. Thanks.

 

[bhicks11 via AccessMonster.com]

MKammerer,

I would not do it this way - as you said it isn't working as expected. When
your user creates a new record I would generate the ID in the background by
checking what the maximum last number was for that date and adding one. I
think it would be cleaner.

So you can do use a query to get the last number:

SELECT mid(Max(Table.Field),9,2) AS MaxOfField
FROM Table;


Make the default value for the ID field be: format(now(),"yyyymmdd" &
(MaxOfField + 1)

I did it quick, you may have to play with the syntax.

Bonnie
http://www.dataplus-svc.com

The default value for the field is a julian date with 2 digits at the end.
The reason I did this is to refer to each entry with a date format and enable
the user to create more than 1 entry per day. With the type of data the
table stores it would be near impossible to create more than 100 entires per
day (and if that starts to happen then I will have to redesign the field).
If a record is deleted, chances are that someone will be entering a new
record at a later date and that will default to 01 so since the first 7
digits are different (because of the date change) it won't matter, however if
someone deletes a record from today and then tries to enter a new record on
the same date, I don't care if the new record gets the deleted record's old
PK value. I think this is what you are talking about but I'm not sure if
that is what you meant.
So essentially using your example if I have enteries for today (7/11/08) the
default entry for the PK is 200819301. And every record entered today will
have that PK value unless changed by the user to 200819302, 200819303, etc.
So lets assume I have 3 entries like you said with those PK values, and its
still 7/11/08 and someone deletes 200819302, and then tries to enter a new
record (still today) whose default PK will be 200819301. DCount should only
return 1 value because the new record being searched is 200819301 and only 1
of those exists. Then if the code works it should add one to the default PK
and set it to 200819302 which according to the table is not being used (even
though it was in use before). I also realize that its possible to create a
duplicate by only adding 1 one time without a loop to recheck until it finds
an empty spot but I would just like to get this part working before I add the
loop since I was having so much trouble with DCount before.

The logic is flawed here.

[quoted text clipped - 7 lines]

 

[Allen Browne]

An example where this approach won't work:
User enter 3 records for today, so 200819301, 200819302, 200819303, then
realizes the middle one was wrong and deletes it. You now have only
200819301 and 200819303.

Next new entry, your code counts 2 records, and tries to assign 200819303.
But that value is already present. Ergo, 'duplicate primary key' failure.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The default value for the field is a julian date with 2 digits at the end.
The reason I did this is to refer to each entry with a date format and
enable
the user to create more than 1 entry per day. With the type of data the
table stores it would be near impossible to create more than 100 entires
per
day (and if that starts to happen then I will have to redesign the field).
If a record is deleted, chances are that someone will be entering a new
record at a later date and that will default to 01 so since the first 7
digits are different (because of the date change) it won't matter, however
if
someone deletes a record from today and then tries to enter a new record
on
the same date, I don't care if the new record gets the deleted record's
old
PK value. I think this is what you are talking about but I'm not sure if
that is what you meant.
So essentially using your example if I have enteries for today (7/11/08)
the
default entry for the PK is 200819301. And every record entered today
will
have that PK value unless changed by the user to 200819302, 200819303,
etc.
So lets assume I have 3 entries like you said with those PK values, and
its
still 7/11/08 and someone deletes 200819302, and then tries to enter a new
record (still today) whose default PK will be 200819301. DCount should
only
return 1 value because the new record being searched is 200819301 and only
1
of those exists. Then if the code works it should add one to the default
PK
and set it to 200819302 which according to the table is not being used
(even
though it was in use before). I also realize that its possible to create
a
duplicate by only adding 1 one time without a loop to recheck until it
finds
an empty spot but I would just like to get this part working before I add
the
loop since I was having so much trouble with DCount before.

The logic is flawed here.

Say you 3 entries for article 999. If I understand, you use values 99901,
99902, and 99903. Someone deletes 99902. The DCount() will now return 2
matching articles, even though the 03 is being used.

I have been trying everything I can to get this to work and I'm still
stumped.

[quoted text clipped - 45 lines]

Not sure where to go from here. Any help would be appreciated. Thanks.

 

[MKammerer via AccessMonster.com]

I thought that was the whole point of the DCount function was to count how
many records exist with the searched for criteria, namely how many are
200819301, not how many are 2008193##. If I have the wrong idea about DCount
please let me know, but under my understanding of it, it would not do as you
said, unless I have coded it wrong that is.

An example where this approach won't work:
User enter 3 records for today, so 200819301, 200819302, 200819303, then
realizes the middle one was wrong and deletes it. You now have only
200819301 and 200819303.

Next new entry, your code counts 2 records, and tries to assign 200819303.
But that value is already present. Ergo, 'duplicate primary key' failure.

The default value for the field is a julian date with 2 digits at the end.
The reason I did this is to refer to each entry with a date format and

[quoted text clipped - 49 lines]

 

[Allen Browne]

I guess I have not understood your criteria:
stLinkCriteria = "[Article ID] = " & AID
I don't know what Article ID is, or the value of AID, so it looks like I
made wrong assumptions.

Perhaps bhicks11 gave a useful answer.

 

[MKammerer via AccessMonster.com]

After fooling around with the code a bit more, I figured out why I wasn't
getting it to work. If I set the DCount If condition to > 0 instead of > 1
it runs my addition code properly, however I now have a new problem and that
is that when the form opens to create a new record the default value is
automatically entered in the field and the focus starts on that field, but it
seems that unless the user physically types in a value, the beforeUpdate
function never runs. So if the default value isn't manually edited by the
user it never gets checked for duplicates. New problem, not sure how to fix
this one.

I thought that was the whole point of the DCount function was to count how
many records exist with the searched for criteria, namely how many are
200819301, not how many are 2008193##. If I have the wrong idea about DCount
please let me know, but under my understanding of it, it would not do as you
said, unless I have coded it wrong that is.

An example where this approach won't work:
User enter 3 records for today, so 200819301, 200819302, 200819303, then

[quoted text clipped - 9 lines]

 

[MKammerer via AccessMonster.com]

I have a table called Articles.
[Articles] has a numeric PK field called [Article ID]
I have a form to create a new entry in [Articles] called [Create New Article]
[Create New Article] has a textbox on it bound to [Article ID] in [Articles],
this textbox is also called [Article ID] or Me. Article_ID
AID is a copied value from the value entered into the textbox on the form.
And all of this coding is taking place under the [Create New Article] form.
I think this should clear up the confusion.

The whole reason I coded everything this way was to essentially create my own
autonumber field for the PK value in the table, but I didn't just want a
regular autonumber, I wanted an ID that made some sense. In my searches of
the best way to go about this I came across the DCount function for searching
the PK field value and displaying an error message. I just took that error
message code away and tried to automatically update the field when the value
exists. Maybe bhicks11 has a better way of doing it however, I finally did
get it to work with the code using > 0, but I would still like to know if
there is a better way or if there is a way to fix the new error I encountered
that posted previously.

Thanks for you time

I guess I have not understood your criteria:
stLinkCriteria = "[Article ID] = " & AID
I don't know what Article ID is, or the value of AID, so it looks like I
made wrong assumptions.

Perhaps bhicks11 gave a useful answer.

 

[Allen Browne]

So you want a custom 9-digit numeric counter, where there first 4 digits are
the year, the next 3 digits are the day of the year, and the last 2 digits
are the entry for the date.

I would be tempted to use the BeforeUpdate event procedure of the form.
Since this is the last possible moment before the record is committed, using
this event reduces the chance that 2 users could be given the same number.

Using DMax() rather than DCount() will give you the highest number assigned
for the date (regardless of deletions.)

Something like this:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim strWhere As String
Dim lngMinID as Long

lngMinID = 100000 * Year(Date) + 100 * CLng(Format(Date, "y"))
strWhere = "[Article ID] Between " & lngMinID & " & And " & lngMinID +
99
If Me.NewRecord Then
Me.[Article ID] = Nz(DMax("Article ID", "Table1", strWhere),
lngMinID) + 1
End If
End Sub

HTH.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a table called Articles.
[Articles] has a numeric PK field called [Article ID]
I have a form to create a new entry in [Articles] called [Create New
Article]
[Create New Article] has a textbox on it bound to [Article ID] in
[Articles],
this textbox is also called [Article ID] or Me. Article_ID
AID is a copied value from the value entered into the textbox on the form.
And all of this coding is taking place under the [Create New Article]
form.
I think this should clear up the confusion.

The whole reason I coded everything this way was to essentially create my
own
autonumber field for the PK value in the table, but I didn't just want a
regular autonumber, I wanted an ID that made some sense. In my searches
of
the best way to go about this I came across the DCount function for
searching
the PK field value and displaying an error message. I just took that
error
message code away and tried to automatically update the field when the
value
exists. Maybe bhicks11 has a better way of doing it however, I finally
did
get it to work with the code using > 0, but I would still like to know if
there is a better way or if there is a way to fix the new error I
encountered
that posted previously.

Thanks for you time

I guess I have not understood your criteria:
stLinkCriteria = "[Article ID] = " & AID
I don't know what Article ID is, or the value of AID, so it looks like I
made wrong assumptions.

Perhaps bhicks11 gave a useful answer.