Simple Database Question (I hope)

  • Thread starter Thread starter Tom Hall
  • Start date Start date
T

Tom Hall

A while ago I created an Access database simply to contain the list of all
Stargate SG-1 episodes, ordered by season number and disk.

I thought I was being smart when I assigned the episode number field as an
autonumber field, only to discover that I missed an episode back in Season
5, but when I entered it, the autonumber naturally didn't match the episode
number. I changed the autonumber to a simple number field, and then
adjusted all the episode numbers manually.

The overlooked episode is still in last place in the table, but my original
query to produce a report simply sorts on season number (1-9) and disc
number (1-5), so the missed episode is in the right place in the report.

My question is this: now that I've corrected the mistake I'd like to revert
to using autonumber for the episode number field, but Access won't let me
change the field's type back to autonumber. Is there a way I can
essentially "clone" this database to a new one in which the episode number
is once again of autonumber type?


TIA,

Tom
 
As you've discovered, an autonumber field cannot be relied on to produce any
type of meaningful serial number system. It's sole purpose is to yield a
unique integer, useful as a primary key. There are work-arounds to "reset"
autonumbers, but what would you do when you skip the next episode, or delete
an inadvertent duplicate?
-Ed
 
Tom said:
A while ago I created an Access database simply to contain the list of all
Stargate SG-1 episodes, ordered by season number and disk.

I thought I was being smart when I assigned the episode number field as an
autonumber field, only to discover that I missed an episode back in Season
5, but when I entered it, the autonumber naturally didn't match the episode
number. I changed the autonumber to a simple number field, and then
adjusted all the episode numbers manually.

The overlooked episode is still in last place in the table, but my original
query to produce a report simply sorts on season number (1-9) and disc
number (1-5), so the missed episode is in the right place in the report.

My question is this: now that I've corrected the mistake I'd like to revert
to using autonumber for the episode number field, but Access won't let me
change the field's type back to autonumber. Is there a way I can
essentially "clone" this database to a new one in which the episode number
is once again of autonumber type?
Click to expand...

try using a max function.
 
Auto numbers aren't suitable for that purpose.
In the default value property for your number field enter
DMax([numberFieldName])+1
This will add one to the highest number for a new record. You can change
numbers at will afterwards and the function will always look up the largest
number and add 1.

John
 
Auto numbers aren't suitable for that purpose.
In the default value property for your number field enter
DMax([numberFieldName])+1
This will add one to the highest number for a new record. You can change
numbers at will afterwards and the function will always look up the largest
number and add 1.
Click to expand...

"The expression you entered has a function containing the wrong number of
arguments"

In the default value property, I put the following:

DMax(ID)+1

where ID is the name of the field in the table.

Access Help seems to indicate a totally different syntax from what you've
given me, which confuses me even more...

This is my first foray back into Access in nearly 6 years. Seems I've
forgotten more about Access than I thought I had :-(


Tom
 
"The expression you entered has a function containing the wrong number of
arguments"

In the default value property, I put the following:

DMax(ID)+1

where ID is the name of the field in the table.

Access Help seems to indicate a totally different syntax from what you've
given me, which confuses me even more...
Click to expand...

You're correct - John may have been thinking of the Max() operator in
queries.

Try instead using the Form's BeforeInsert event. Click the ... icon by
that event in the form's properties, and choose Code Builder; edit it
to something like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
End Sub


John W. Vinson[MVP]
 
John Vinson said:
"The expression you entered has a function containing the wrong number of
arguments"

In the default value property, I put the following:

DMax(ID)+1

where ID is the name of the field in the table.

Access Help seems to indicate a totally different syntax from what you've
given me, which confuses me even more...
Click to expand...

You're correct - John may have been thinking of the Max() operator in
queries.

Try instead using the Form's BeforeInsert event. Click the ... icon by
that event in the form's properties, and choose Code Builder; edit it
to something like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
End Sub


John W. Vinson[MVP]
Click to expand...


Sorry, I was just going from memory, I should have checked.
I have =DMax("[JobNumber]","[tblJobcard]")+1
Is there any problem just putting this in the default property, rather than
writing a procedure.
It has worked ok for me.

John
 
Try instead using the Form's BeforeInsert event.
Click to expand...

Form? I'm trying to re-order a table. What does the/a form have to do with
it? I totally don't understand this.
Click the ... icon by
that event in the form's properties, and choose Code Builder; edit it
to something like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
End Sub
Click to expand...

All of my mdb files say "Access 2000 format". Is there anyway to bring them
up to speed with Access 2003?


Tom
 
Sorry, I was just going from memory, I should have checked.
I have =DMax("[JobNumber]","[tblJobcard]")+1
Is there any problem just putting this in the default property, rather than
writing a procedure.
It has worked ok for me.
Click to expand...

Assuming that the values inside the []'s had to be changed to reflect my
database, I came up with the following:

=DMax("[ID]","
")+1

Where "ID" is the formerly autonumber field, and "table" is the name of the
table.

However, this time I get a different error message:

Unknown function 'DMax' in validation expression or default value on
'EpisodeList.ID'



Tom
 
Form? I'm trying to re-order a table. What does the/a form have to do with
it? I totally don't understand this.
Click to expand...

Tables HAVE NO ORDER.

A table is an unordered "bag" of data. There is no such thing as
"ordering a table". If you want to see records in a particular order
you must, no option, use a Query sorting by a field in the table.
Click the ... icon by
that event in the form's properties, and choose Code Builder; edit it
to something like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[tablename]")) + 1
End Sub
Click to expand...

All of my mdb files say "Access 2000 format". Is there anyway to bring them
up to speed with Access 2003?
Click to expand...

Access2002 and 2003 default to creating databases in 2000 format.
Unless you want to create a .MDE file I haven't seen any compelling
reason to change that default. If you want to do so, use Tools...
Database Utilities... Convert.

John W. Vinson[MVP]
 
Back
Top