New sequentiall Number to new record

[Bob Vance]

This code opens my new record and I want it to add a sequential number to
"HorseID" (Number Field) but only if it is a new record
----------------------------------------
On Error GoTo Err_Command98_Click

DoCmd.OpenForm "frmHorseInfo", , , , acFormAdd

Exit_Command98_Click:
Exit Sub

Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click
-----------------------------------------------
Can I use this code somewhere:
--------------------------------------
Function NextHorseNo() As Long
NextHorseNo = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Function
-- ----------------------------------------
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3

 

[Jack Cannon]

Bob,

From the information contained in your post, it would appear that "HorseID"
should be an autonumber field (as well as a primary key) instead of a number
field. An autonumber field will increment automatically as I interpret your
objective.

Jack Cannon

 

[Bob Vance]

Thanks Jack, So many people have told me that I should not use a autonumber
field as a HorseID, As these numbers are actually used as printed IDs on a
report, so as if I dont save 10 entries by making mistakes or deleting the
number will jump from say 50 to 60
I created a new Table using HorseID as a numberfield and made ID my
Autonumber (New Values- Incremant)
Regards Bob

 

[Jack Cannon]

Bob,

You are correct that deleting records or aborting a data entry operation
will cause gaps in the number. However, one needs to question whether that
is actually a problem. If so, you can use a separate field other than your
primary key for this function (as you indicated). This will eliminate the
gaps caused by aborting a data entry operation but you will still get gaps if
you delete any records other than the very last one.

As far as the information showing up on a printed report, that should not be
the case unless you specifically design the report to do this.

Jack Cannon

 

[Bob Vance]

Thanks Jack, so getting back to my original post where would I put this code
for only my new record in increminate HorseID..............Thanks Bob

 

[pietlinden]

Thanks Jack, so getting back to my original post where would I put this code
for only my new record in increminate HorseID..............Thanks Bob

If Me.NewRecord=True Then
Me.txtHorseID=NextHorseID
End If

 

[Jack Cannon]

Dave,

This is probably the easiest way to accomplish your objective.

Open the properties window of the form and enter the following line of text
into the "Default Value" of the HorseID property window.

=Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1

There are other ways too. The important part is to load the Default Value
of HorseID property window with whatever value you want. For example you
could on the Form_Open event, execute the following command.

Private Sub Form_Open(Cancel As Integer)
Me![HorseID].DefaultValue = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Sub


Jack Cannon

 

[Bob Vance]

Brillant Jack that did it used the default in my text Box..Regards Bob

Dave,

This is probably the easiest way to accomplish your objective.

Open the properties window of the form and enter the following line of
text
into the "Default Value" of the HorseID property window.

=Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1

There are other ways too. The important part is to load the Default Value
of HorseID property window with whatever value you want. For example you
could on the Form_Open event, execute the following command.

Private Sub Form_Open(Cancel As Integer)
Me![HorseID].DefaultValue = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Sub


Jack Cannon

Thanks Jack, so getting back to my original post where would I put this
code
for only my new record in increminate HorseID..............Thanks Bob

 

[Bob Vance]

I made a new table , the primary key field had been my HorseID , so I made
HorseID a number field and made my primary key something else so all my
functions in my database will still reconize HorseID, wont they???....Thanks
Bob

Dave,

This is probably the easiest way to accomplish your objective.

Open the properties window of the form and enter the following line of
text
into the "Default Value" of the HorseID property window.

=Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1

There are other ways too. The important part is to load the Default Value
of HorseID property window with whatever value you want. For example you
could on the Form_Open event, execute the following command.

Private Sub Form_Open(Cancel As Integer)
Me![HorseID].DefaultValue = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Sub


Jack Cannon

Thanks Jack, so getting back to my original post where would I put this
code
for only my new record in increminate HorseID..............Thanks Bob

 

[Bob Vance]

I must be dumb Just relised I could have added a new field and made in the
primary key instead of creating a new one... Bob

I made a new table , the primary key field had been my HorseID , so I made
HorseID a number field and made my primary key something else so all my
functions in my database will still reconize HorseID, wont
they???....Thanks Bob

Dave,

This is probably the easiest way to accomplish your objective.

Open the properties window of the form and enter the following line of
text
into the "Default Value" of the HorseID property window.

=Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1

There are other ways too. The important part is to load the Default
Value
of HorseID property window with whatever value you want. For example you
could on the Form_Open event, execute the following command.

Private Sub Form_Open(Cancel As Integer)
Me![HorseID].DefaultValue = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Sub


Jack Cannon

Thanks Jack, so getting back to my original post where would I put this
code
for only my new record in increminate HorseID..............Thanks Bob


Bob,

You are correct that deleting records or aborting a data entry
operation
will cause gaps in the number. However, one needs to question whether
that
is actually a problem. If so, you can use a separate field other than
your
primary key for this function (as you indicated). This will eliminate
the
gaps caused by aborting a data entry operation but you will still get
gaps
if
you delete any records other than the very last one.

As far as the information showing up on a printed report, that should
not
be
the case unless you specifically design the report to do this.

Jack Cannon


:

Thanks Jack, So many people have told me that I should not use a
autonumber
field as a HorseID, As these numbers are actually used as printed IDs
on
a
report, so as if I dont save 10 entries by making mistakes or
deleting
the
number will jump from say 50 to 60
I created a new Table using HorseID as a numberfield and made ID my
Autonumber (New Values- Incremant)
Regards Bob

Bob,

From the information contained in your post, it would appear that
"HorseID"
should be an autonumber field (as well as a primary key) instead of
a
number
field. An autonumber field will increment automatically as I
interpret
your
objective.

Jack Cannon


:

This code opens my new record and I want it to add a sequential
number
to
"HorseID" (Number Field) but only if it is a new record
----------------------------------------
On Error GoTo Err_Command98_Click

DoCmd.OpenForm "frmHorseInfo", , , , acFormAdd

Exit_Command98_Click:
Exit Sub

Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click
-----------------------------------------------
Can I use this code somewhere:
--------------------------------------
Function NextHorseNo() As Long
NextHorseNo = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Function
-- ----------------------------------------
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3

 

[Jack Cannon]

Yes Bob, they will. You should have no problem.

Jack Cannon

 

[Richard]

Hi Bob, check this post about this subject. Where Ken talks about dupes

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I must be dumb Just relised I could have added a new field and made in the
primary key instead of creating a new one... Bob

I made a new table , the primary key field had been my HorseID , so I made
HorseID a number field and made my primary key something else so all my
functions in my database will still reconize HorseID, wont
they???....Thanks Bob

Dave,

This is probably the easiest way to accomplish your objective.

Open the properties window of the form and enter the following line of
text
into the "Default Value" of the HorseID property window.

=Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1

There are other ways too. The important part is to load the Default
Value
of HorseID property window with whatever value you want. For example you
could on the Form_Open event, execute the following command.

Private Sub Form_Open(Cancel As Integer)
Me![HorseID].DefaultValue = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Sub


Jack Cannon


:

Thanks Jack, so getting back to my original post where would I put this
code
for only my new record in increminate HorseID..............Thanks Bob


Bob,

You are correct that deleting records or aborting a data entry
operation
will cause gaps in the number. However, one needs to question whether
that
is actually a problem. If so, you can use a separate field other than
your
primary key for this function (as you indicated). This will eliminate
the
gaps caused by aborting a data entry operation but you will still get
gaps
if
you delete any records other than the very last one.

As far as the information showing up on a printed report, that should
not
be
the case unless you specifically design the report to do this.

Jack Cannon


:

Thanks Jack, So many people have told me that I should not use a
autonumber
field as a HorseID, As these numbers are actually used as printed IDs
on
a
report, so as if I dont save 10 entries by making mistakes or
deleting
the
number will jump from say 50 to 60
I created a new Table using HorseID as a numberfield and made ID my
Autonumber (New Values- Incremant)
Regards Bob

Bob,

From the information contained in your post, it would appear that
"HorseID"
should be an autonumber field (as well as a primary key) instead of
a
number
field. An autonumber field will increment automatically as I
interpret
your
objective.

Jack Cannon


:

This code opens my new record and I want it to add a sequential
number
to
"HorseID" (Number Field) but only if it is a new record
----------------------------------------
On Error GoTo Err_Command98_Click

DoCmd.OpenForm "frmHorseInfo", , , , acFormAdd

Exit_Command98_Click:
Exit Sub

Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click
-----------------------------------------------
Can I use this code somewhere:
--------------------------------------
Function NextHorseNo() As Long
NextHorseNo = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Function
-- ----------------------------------------
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3

 

[Richard]

its near the bottom called "Increment a string"

Hi Bob, check this post about this subject. Where Ken talks about dupes

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

I must be dumb Just relised I could have added a new field and made in the
primary key instead of creating a new one... Bob

I made a new table , the primary key field had been my HorseID , so I made
HorseID a number field and made my primary key something else so all my
functions in my database will still reconize HorseID, wont
they???....Thanks Bob

Dave,

This is probably the easiest way to accomplish your objective.

Open the properties window of the form and enter the following line of
text
into the "Default Value" of the HorseID property window.

=Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1

There are other ways too. The important part is to load the Default
Value
of HorseID property window with whatever value you want. For example you
could on the Form_Open event, execute the following command.

Private Sub Form_Open(Cancel As Integer)
Me![HorseID].DefaultValue = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Sub


Jack Cannon


:

Thanks Jack, so getting back to my original post where would I put this
code
for only my new record in increminate HorseID..............Thanks Bob


Bob,

You are correct that deleting records or aborting a data entry
operation
will cause gaps in the number. However, one needs to question whether
that
is actually a problem. If so, you can use a separate field other than
your
primary key for this function (as you indicated). This will eliminate
the
gaps caused by aborting a data entry operation but you will still get
gaps
if
you delete any records other than the very last one.

As far as the information showing up on a printed report, that should
not
be
the case unless you specifically design the report to do this.

Jack Cannon


:

Thanks Jack, So many people have told me that I should not use a
autonumber
field as a HorseID, As these numbers are actually used as printed IDs
on
a
report, so as if I dont save 10 entries by making mistakes or
deleting
the
number will jump from say 50 to 60
I created a new Table using HorseID as a numberfield and made ID my
Autonumber (New Values- Incremant)
Regards Bob

Bob,

From the information contained in your post, it would appear that
"HorseID"
should be an autonumber field (as well as a primary key) instead of
a
number
field. An autonumber field will increment automatically as I
interpret
your
objective.

Jack Cannon


:

This code opens my new record and I want it to add a sequential
number
to
"HorseID" (Number Field) but only if it is a new record
----------------------------------------
On Error GoTo Err_Command98_Click

DoCmd.OpenForm "frmHorseInfo", , , , acFormAdd

Exit_Command98_Click:
Exit Sub

Err_Command98_Click:
MsgBox Err.Description
Resume Exit_Command98_Click
-----------------------------------------------
Can I use this code somewhere:
--------------------------------------
Function NextHorseNo() As Long
NextHorseNo = Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1
End Function
-- ----------------------------------------
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3

 

[a a r o n . k e m p f]

those people are stupid
there's nothing wrong with having gaps in numbers-- that's the root of
the problem

 

[a a r o n . k e m p f]

wow, that might work ok-- execpt for when you have multiple users

Dave,

This is probably the easiest way to accomplish your objective.

Open the properties window of the form and enter the following line of text
into the "Default Value" of the HorseID property window.

=Nz(DMax("HorseID", "tblHorseInfo"), 0) + 1

There are other ways too.  The important part is to load the Default Value
of HorseID property window with whatever value you want.  For example you
could on the Form_Open event, execute the following command.

Private Sub Form_Open(Cancel As Integer)
    Me![HorseID].DefaultValue = Nz(DMax("HorseID", "tblHorseInfo"),0) + 1
End Sub

Jack Cannon

Thanks Jack, so getting back to my original post where would I put thiscode
for only my new record in increminate HorseID..............Thanks Bob

 

[Jack Cannon]

Aaron,

Please back off. You are not being helpful by calling people stupid.

There was a discussion early in this thread regarding the fact that there is
nothing wrong with gaps in an autonumber field. Bob still feels that he
needs to eliminate the gaps. And Yes, of course, he will have to ensure that
he is the only user adding the record, which can be done by restricting
everyone but himself from adding a record. That is a price he will have to
pay for the function.

One example of why Bob might be sensitive to the issue is that auditors
sometimes look for such gaps in the records and question why those gaps exist
and whether a record has been wrongly deleted. There is no need to inform
the rest of us that SQL Server has a mechanism that handles that particular
problem. We all already know that.

Jack Cannon

 

[Bob Vance]

Im using these numbers as Horse IDS like when I make a payments instead of
writing horse names or breeding I simply write 35,56
Also keeps me informed of how many horses I have had through my stable
Also for naming horse covers very convenant to just paint 35 on it
As its a stand alone database/ not split this should not be any problem
Regards Bob

 

[Aaron Kempf knows nothing]

wow, I have found an apple in my shorts

 

[Aaron Kempf knows nothing]

I am stupid
I having gaps in my brain cells.

 

[aaron.kempf]

why should I back off?

because the 'gang mentality' is correct?

**** you -- for using jet-- and **** newbies that make the wrong
decision-- to learn jet- instead of a database with a future