Before Insert

[Jim Meyer]

I created two fields in my table. I then used the
following example from applecore99.com which is great but
it has stopped working in my database. The point is to
create a order number. It works until the serialid field
gets to 10 but then continually enters 10 into the field
of a new record over and over. 1 - 9 works great.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SERIALID = Nz(DMax
("[SerialID]", "[Search]", "[YearEntry]=Year(Date())"), 0)
+ 1
Me!YEARENTRY = Year(DATE)
End Sub

Please Help.

Jim Meyer

 

[Dirk Goldgar]

I created two fields in my table. I then used the
following example from applecore99.com which is great but
it has stopped working in my database. The point is to
create a order number. It works until the serialid field
gets to 10 but then continually enters 10 into the field
of a new record over and over. 1 - 9 works great.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SERIALID = Nz(DMax
("[SerialID]", "[Search]", "[YearEntry]=Year(Date())"), 0)
+ 1
Me!YEARENTRY = Year(DATE)
End Sub

Please Help.

Jim Meyer

What type of field is SERIALID, and what is its Field Size?

 

[Jim Meyer]

Thanks for replying. The type of field is called SerialID
and the data type is text with the size at default 50.

Thanks,
Carl

-----Original Message-----

I created two fields in my table. I then used the
following example from applecore99.com which is great but
it has stopped working in my database. The point is to
create a order number. It works until the serialid field
gets to 10 but then continually enters 10 into the field
of a new record over and over. 1 - 9 works great.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SERIALID = Nz(DMax
("[SerialID]", "[Search]", "[YearEntry]=Year(Date())"), 0)
+ 1
Me!YEARENTRY = Year(DATE)
End Sub

Please Help.

Jim Meyer

What type of field is SERIALID, and what is its Field Size?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[Dirk Goldgar]

Thanks for replying. The type of field is called SerialID
and the data type is text with the size at default 50.

Thanks,
Carl

-----Original Message-----

I created two fields in my table. I then used the
following example from applecore99.com which is great but
it has stopped working in my database. The point is to
create a order number. It works until the serialid field
gets to 10 but then continually enters 10 into the field
of a new record over and over. 1 - 9 works great.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me!SERIALID = Nz(DMax
("[SerialID]", "[Search]", "[YearEntry]=Year(Date())"), 0)
+ 1
Me!YEARENTRY = Year(DATE)
End Sub

Please Help.

Jim Meyer

What type of field is SERIALID, and what is its Field Size?

If SerialID is a text field, then DMax is going to give you the maximum
*string* value, not the maximum *numeric* value. For example, the value
"9" is greater, considered as a string, than "10", because an alphabetic
comparison is used. There are (moderately complicated) ways around
this, but the real answer is not to use a text field when you're going
to be doing arithmetic -- adding 1, for example -- or numeric
comparisons with the field value. As anup posted, this should be a
number field, probably a Long Integer.

 

[Jim Meyer]

Thank you both for helping me. I changed the field to long
integer and it has been working much better. The only
problem I am now having is it will randomly repeat itself.
Example: I have two 110's and 118's. Any input would be
greatly appreciated.

Thanks,
Carl

 

[Dirk Goldgar]

Thank you both for helping me. I changed the field to long
integer and it has been working much better. The only
problem I am now having is it will randomly repeat itself.
Example: I have two 110's and 118's. Any input would be
greatly appreciated.

Do you mean it repeats itself for the same year? The only way I can see
that happening is two or more people adding records at the about the
same time, where one begins a new record (causing the next SerialID to
be generated) and then another begins a new record before the first has
saved the record with the new SerialID. Could that be happening?

I have the impression that Year and SerialID between them are supposed
to constitute a unique key. If so, you could keep duplicate-keyed
records from being stored by creating a unique index on the combination
of those two fields.

 

[Jim Meyer]

Thanks again for the help. It is the SerialID that
randomly repeats itself and stores a duplicate value which
defeats my purpose of a unique number. On my form it is
displayed as 2004-1 to 2004-(next number). I

 

[Dirk Goldgar]

Thanks again for the help. It is the SerialID that
randomly repeats itself and stores a duplicate value which
defeats my purpose of a unique number. On my form it is
displayed as 2004-1 to 2004-(next number). I

Your message appears to be incomplete. Did you look into the
possibility that I suggested? If only one user is editing the data,
using only one instance of the form, I can't see any way that that code
could give the same SerialID. In a multi-user situation it's another
story.