Before Insert

  • Thread starter Thread starter Jim Meyer
  • Start date Start date
J

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
 
Jim Meyer said:
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
Click to expand...


What type of field is SERIALID, and what is its Field Size?
 
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
Click to expand...


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)


.
Click to expand...
 
Jim Meyer said:
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-----
Jim Meyer said:
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
Click to expand...


What type of field is SERIALID, and what is its Field Size?
Click to expand...
Click to expand...

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.
 
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
 
Jim Meyer said:
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.
Click to expand...

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.
 
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
 
Jim Meyer said:
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
Click to expand...

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.
 
Back
Top