Form

[Jenn Civello]

I have a form, that has an autonumber in it and when I go to the next record
the autonumber field does not automatically updated.

 

[John Vinson]

I have a form, that has an autonumber in it and when I go to the next record
the autonumber field does not automatically updated.

Why would you expect it to?

An autonumber has one purpose, and one purpose only: to provide a
unique key value. It's not a record counter, it certainly will not
change if you just "go to the next record". Inserting a NEW record (by
typing data into some field other than the autonumber on the new
record on a Form) will create a new record, with a new autonumber. Are
you not seeing this?

John W. Vinson[MVP]

 

[Jenn Civello]

Yes I am, but this is what I am trying to do.

I have a query with 4 fields. The first field has "ITCC", the second field
has "07" and the third field is an autonumber field and the fourth field is
Change Control Number: [Field1] & [Field2] & "-" & [Field3], this is suppose
to bring everything together so it looks like, ITCC07-21. It shows up like
this if I enter the information into the table first, but I don't want to
enter it into the table, I want to have a form that users can use to enter
in the information. When I enter information into on of the other fields the
number just shows up like "-21", but I want the "ITCC" in front of the
number. Is there any way that I can make this happen?

 

[John Vinson]

Yes I am, but this is what I am trying to do.

I have a query with 4 fields. The first field has "ITCC", the second field
has "07" and the third field is an autonumber field and the fourth field is
Change Control Number: [Field1] & [Field2] & "-" & [Field3], this is suppose
to bring everything together so it looks like, ITCC07-21. It shows up like
this if I enter the information into the table first, but I don't want to
enter it into the table, I want to have a form that users can use to enter
in the information. When I enter information into on of the other fields the
number just shows up like "-21", but I want the "ITCC" in front of the
number. Is there any way that I can make this happen?

This is a bad idea for two or three reasons.

For one, you're misusing autonumber. Autonumbers will NOT start over
in 2008 (if the 07 is in fact referring to 2007); you're creating an
"intelligent key" - not a compliment, it means that you're trying to
store information in a key; it's redundnant; the field is non-atomic.

The only justification for this composite field is for compatibility
with a long-established paper system. If you do need to do that, then
I'd store it in two fields - a date or year field (the 07 component)
and an Integer or Long Integer field (*not* an Autonumber). You would
use code to increment the number, starting over on January 1 each year
if that's what you want to do; and combine the fields *at the time you
need to display them*, rather than storing this composite redundant
field.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Yes I am, but this is what I am trying to do.

I have a query with 4 fields. The first field has "ITCC", the second
field
has "07" and the third field is an autonumber field and the fourth field
is
Change Control Number: [Field1] & [Field2] & "-" & [Field3], this is
suppose
to bring everything together so it looks like, ITCC07-21. It shows up
like
this if I enter the information into the table first, but I don't want to
enter it into the table, I want to have a form that users can use to enter
in the information. When I enter information into on of the other fields
the
number just shows up like "-21", but I want the "ITCC" in front of the
number. Is there any way that I can make this happen?

This is a bad idea for two or three reasons.

For one, you're misusing autonumber. Autonumbers will NOT start over
in 2008 (if the 07 is in fact referring to 2007); you're creating an
"intelligent key" - not a compliment, it means that you're trying to
store information in a key; it's redundnant; the field is non-atomic.

The only justification for this composite field is for compatibility
with a long-established paper system. If you do need to do that, then
I'd store it in two fields - a date or year field (the 07 component)
and an Integer or Long Integer field (*not* an Autonumber). You would
use code to increment the number, starting over on January 1 each year
if that's what you want to do; and combine the fields *at the time you
need to display them*, rather than storing this composite redundant
field.

In fairness to Jenn, John, she's storing three separate fields in her table,
and using a query to concatenate them for display purposes.

Jeff Boyce & I have been involved with her on this issue previously, but
we've had to bow out. However, we've already warned her that the AutoNumber
won't reset this year, and she's okay with that.

 

[Jenn Civello]

Is code hard to do? I am not a very advanced user, that's why I was trying
to get around it by using an auto number field.
How would I go about using code to increment the number?

Yes I am, but this is what I am trying to do.

I have a query with 4 fields. The first field has "ITCC", the second
field
has "07" and the third field is an autonumber field and the fourth field
is
Change Control Number: [Field1] & [Field2] & "-" & [Field3], this is
suppose
to bring everything together so it looks like, ITCC07-21. It shows up
like
this if I enter the information into the table first, but I don't want to
enter it into the table, I want to have a form that users can use to enter
in the information. When I enter information into on of the other fields
the
number just shows up like "-21", but I want the "ITCC" in front of the
number. Is there any way that I can make this happen?

This is a bad idea for two or three reasons.

For one, you're misusing autonumber. Autonumbers will NOT start over
in 2008 (if the 07 is in fact referring to 2007); you're creating an
"intelligent key" - not a compliment, it means that you're trying to
store information in a key; it's redundnant; the field is non-atomic.

The only justification for this composite field is for compatibility
with a long-established paper system. If you do need to do that, then
I'd store it in two fields - a date or year field (the 07 component)
and an Integer or Long Integer field (*not* an Autonumber). You would
use code to increment the number, starting over on January 1 each year
if that's what you want to do; and combine the fields *at the time you
need to display them*, rather than storing this composite redundant
field.

John W. Vinson[MVP]

 

[John Vinson]

Is code hard to do? I am not a very advanced user, that's why I was trying
to get around it by using an auto number field.
How would I go about using code to increment the number?

It's not very difficult; in fact there are several ways to do it. But
WHICH way you should use depends on the circumstances! Is this a
multiuser (split, if you're wise) database, or single-user? If it's
multiuser, will many users be updating and adding new records at the
same time, or will this be an infrequent event?

John W. Vinson[MVP]

 

[Jenn Civello]

John,

Yes this is a mutil user database, and there might be several users updating
and adding new records to the database at one time.

 

[John W. Vinson]

John,

Yes this is a mutil user database, and there might be several users updating
and adding new records to the database at one time.

In that case you'll probably want to use the technique from the Access
Developer's Handbook. It's copyrighted code so I'm reluctant to post
it here, but you can do a Google Groups search:

http://groups.google.com/advanced_search

Select microsoft.public.access.* and search for "Custom Counter" and
you'll find boatloads of examples.

In a nutshell, you create a table with one field for each custom
counter that you need; the code opens this table exclusively, gets the
value, increments it, updates it in that table, and frees the table up
for the next user; it then returns the value to the form.

John W. Vinson [MVP]

 

[Jenn Civello]

What if I changed this database to be a single user database, could you give
me any suggestions?

 

[John W. Vinson]

What if I changed this database to be a single user database, could you give
me any suggestions?

Sure... but that would be a pretty drastic change! If it's a multiuser
database because you want multiple users to USE it, you certainly
wouldn't want to remove that capability just to save a bit of coding!

What's the real-world situation? Will there routinely be two or more
users attempting to create new records at the same time, or is that
just not something that will happen (because of staff assignments and
the actual work-room processes)?

The simplest way to increment the number for the "only one user will
be inserting records" scenario would use the Form's Beforeinsert
event, with code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtSeqNo = NZ(DMax("[SeqNo]", "[yourtablename]")) + 1
End Sub

This will find the maximum existing SeqNo value in the table named
yourtablename, increment it by one, and store it in the (bound)
textbox named txtSeqNo. The risk (in multiuser applications) is that
another user might get the same number if they enter a record while
the first user still has this record on the form, not committed to
disk.

John W. Vinson [MVP]

 

[Jenn Civello]

We were going to make the database a multi user database, but decided it
would be better to keep it as a single user database.

Where do I enter in this code?

What if I changed this database to be a single user database, could you
give
me any suggestions?

Sure... but that would be a pretty drastic change! If it's a multiuser
database because you want multiple users to USE it, you certainly
wouldn't want to remove that capability just to save a bit of coding!

What's the real-world situation? Will there routinely be two or more
users attempting to create new records at the same time, or is that
just not something that will happen (because of staff assignments and
the actual work-room processes)?

The simplest way to increment the number for the "only one user will
be inserting records" scenario would use the Form's Beforeinsert
event, with code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtSeqNo = NZ(DMax("[SeqNo]", "[yourtablename]")) + 1
End Sub

This will find the maximum existing SeqNo value in the table named
yourtablename, increment it by one, and store it in the (bound)
textbox named txtSeqNo. The risk (in multiuser applications) is that
another user might get the same number if they enter a record while
the first user still has this record on the form, not committed to
disk.

John W. Vinson [MVP]