create a sequential number for field

[sharontodd]

I am creating a registration form for participants in an event. I need a
sequential number assigned to each participant and displayed as they are
entered in the form so that number can be written on the paper registration
for possible reference later. This field will be linked to other forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[Evi]

*If the database is not Multi-user* then in your form, use the After Update
Event of one of the controls which you always have to fill in and put the
following *single* line


Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt") + 1


substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the form when
you create a new record, one of the first fields, if possible so that you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the line

If your db is on a network, then it will be more complex, especially for a
Primary Key control. cI don't know how to set that up

Evi

 

[sharontodd]

Great explaination!

I entered the code but when I ran it Got teh following Error message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was looking
for. Any ideas on the error.

Thanks for your help.

Sharon

*If the database is not Multi-user* then in your form, use the After Update
Event of one of the controls which you always have to fill in and put the
following *single* line


Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt") + 1


substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the form when
you create a new record, one of the first fields, if possible so that you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the line

If your db is on a network, then it will be more complex, especially for a
Primary Key control. cI don't know how to set that up

Evi

I am creating a registration form for participants in an event. I need a
sequential number assigned to each participant and displayed as they are
entered in the form so that number can be written on the paper registration
for possible reference later. This field will be linked to other forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[Arvin Meyer [MVP]]

Access does this for you with an Autonumber datatype. Just add a field to
your table and set its Data Type to Autonumber.

 

[sharontodd]

I had this as an autonumber before but i have read many, many threads putting
it down for this type of use as it can become random and not continuous and
sequential. For that reason, I have been trying to find an alternative.

 

[sharontodd]

The event procedure is in the activating field "Last Name". I am trying to
increaseing Reg_#. I added the if then statement you suggested - good idea -
but now no error but also no increase of Reg_#. I have the defult value of
Reg_# as 0. Here is the code I have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control which is
meant to activate it? ie it doesn't go in the field which is going to hold
the sequential number, you don't need to type in that at all, it goes in a
nearby control - one which you always fill in when you are entering a record

Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt") + 1
End If

Otherwise, when you come to correct that other field, it will reset the
sequential number code.

Evi

Great explaination!

I entered the code but when I ran it Got teh following Error message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was looking
for. Any ideas on the error.

Thanks for your help.

Sharon

*If the database is not Multi-user* then in your form, use the After Update
Event of one of the controls which you always have to fill in and put the
following *single* line


Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt") + 1


substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the form when
you create a new record, one of the first fields, if possible so that you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the line

If your db is on a network, then it will be more complex, especially for a
Primary Key control. cI don't know how to set that up

Evi




I am creating a registration form for participants in an event. I need a
sequential number assigned to each participant and displayed as they are
entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to other forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[sharontodd]

Yes the code is in the activating field, "Last Name". I added the IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the code I
have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control which is
meant to activate it? ie it doesn't go in the field which is going to hold
the sequential number, you don't need to type in that at all, it goes in a
nearby control - one which you always fill in when you are entering a record

Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt") + 1
End If

Otherwise, when you come to correct that other field, it will reset the
sequential number code.

Evi

Great explaination!

I entered the code but when I ran it Got teh following Error message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was looking
for. Any ideas on the error.

Thanks for your help.

Sharon

*If the database is not Multi-user* then in your form, use the After Update
Event of one of the controls which you always have to fill in and put the
following *single* line


Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt") + 1


substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the form when
you create a new record, one of the first fields, if possible so that you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the line

If your db is on a network, then it will be more complex, especially for a
Primary Key control. cI don't know how to set that up

Evi




I am creating a registration form for participants in an event. I need a
sequential number assigned to each participant and displayed as they are
entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to other forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[Evi]

Private Sub Last_Name_AfterUpdate()
If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Just stick a Me. in front of the first Reg_# as above.

My Bad - rushing again!

Evi

Yes the code is in the activating field, "Last Name". I added the IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the code I
have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control which is
meant to activate it? ie it doesn't go in the field which is going to hold
the sequential number, you don't need to type in that at all, it goes in a
nearby control - one which you always fill in when you are entering a record

Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt") + 1
End If

Otherwise, when you come to correct that other field, it will reset the
sequential number code.

Evi

Great explaination!

I entered the code but when I ran it Got teh following Error message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was looking
for. Any ideas on the error.

Thanks for your help.

Sharon

:

*If the database is not Multi-user* then in your form, use the After Update
Event of one of the controls which you always have to fill in and

put
the

following *single* line


Me.[YourNumberField] =

DMax("[YourNumberField]","TableWhichContainsIt")
+ 1

substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the

form
when

you create a new record, one of the first fields, if possible so

that
you

can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the line

If your db is on a network, then it will be more complex,

especially
for a

Primary Key control. cI don't know how to set that up

Evi




I am creating a registration form for participants in an event. I need a
sequential number assigned to each participant and displayed as

they
are

entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to other forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[Evi]

I'd better also add an NZ while I'm at it so that it doesn't throw a wobbly
the first time you use it.
Private Sub Last_Name_AfterUpdate()
If NZ(IsNull(Me.[Reg_#]),0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Evi

Private Sub Last_Name_AfterUpdate()
If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Just stick a Me. in front of the first Reg_# as above.

My Bad - rushing again!

Evi

Yes the code is in the activating field, "Last Name". I added the IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the code I
have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control which is
meant to activate it? ie it doesn't go in the field which is going to hold
the sequential number, you don't need to type in that at all, it goes

in

a

nearby control - one which you always fill in when you are entering a record

Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] =

DMax("[YourNumberField]","TableWhichContainsIt")
+ 1

End If

Otherwise, when you come to correct that other field, it will reset the
sequential number code.

Evi


Great explaination!

I entered the code but when I ran it Got teh following Error message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was
looking
for. Any ideas on the error.

Thanks for your help.

Sharon

:

*If the database is not Multi-user* then in your form, use the After
Update
Event of one of the controls which you always have to fill in and put
the
following *single* line


Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt")
+ 1


substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the form
when
you create a new record, one of the first fields, if possible so that
you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the line

If your db is on a network, then it will be more complex, especially
for a
Primary Key control. cI don't know how to set that up

Evi




I am creating a registration form for participants in an event. I
need a
sequential number assigned to each participant and displayed as they
are
entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to other
forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[sharontodd]

I tried to send this once. It didn't seem to go through.

I wish I knew the syntax for this language better. Still getting an error.

Error 2465. Can't find the "|" referred to in your experession.

It lights up the IF line. I tried it with "[Reg_#]" = syntax error for
routine; ["Reg_#"] = Error 2465; Reg_# = syntax error.

I've now got...

Private Sub Last_Name_AfterUpdate()
If Nz(IsNull(Me.[Reg_#]), 0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Sorry this is dragging on. I feel like I'm back in Comp Sci in college.

Sharontodd

I'd better also add an NZ while I'm at it so that it doesn't throw a wobbly
the first time you use it.
Private Sub Last_Name_AfterUpdate()
If NZ(IsNull(Me.[Reg_#]),0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Evi

Private Sub Last_Name_AfterUpdate()
If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Just stick a Me. in front of the first Reg_# as above.

My Bad - rushing again!

Evi

Yes the code is in the activating field, "Last Name". I added the IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the code I
have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

:

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control which is
meant to activate it? ie it doesn't go in the field which is going to hold
the sequential number, you don't need to type in that at all, it goes

in

a
nearby control - one which you always fill in when you are entering a record

Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] =

DMax("[YourNumberField]","TableWhichContainsIt")
+ 1

End If

Otherwise, when you come to correct that other field, it will reset the
sequential number code.

Evi


Great explaination!

I entered the code but when I ran it Got teh following Error message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was
looking
for. Any ideas on the error.

Thanks for your help.

Sharon

:

*If the database is not Multi-user* then in your form, use the After
Update
Event of one of the controls which you always have to fill in and put
the
following *single* line


Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt")
+ 1


substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the form
when
you create a new record, one of the first fields, if possible so that
you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the line

If your db is on a network, then it will be more complex, especially
for a
Primary Key control. cI don't know how to set that up

Evi




I am creating a registration form for participants in an event. I
need a
sequential number assigned to each participant and displayed as they
are
entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to other
forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[Evi]

Oh dear, I'm really not with it today Sharon. If you look at my post in
Formscoding about an EOF error I've got in a form you'll probably see why -
I'm banging my head about it!

If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = Nz(DMax("[Reg_#]", "Registrations") + 1, 0)
End If
End Sub

I tried to send this once. It didn't seem to go through.

I wish I knew the syntax for this language better. Still getting an error.

Error 2465. Can't find the "|" referred to in your experession.

It lights up the IF line. I tried it with "[Reg_#]" = syntax error for
routine; ["Reg_#"] = Error 2465; Reg_# = syntax error.

I've now got...

Private Sub Last_Name_AfterUpdate()
If Nz(IsNull(Me.[Reg_#]), 0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Sorry this is dragging on. I feel like I'm back in Comp Sci in college.

Sharontodd

I'd better also add an NZ while I'm at it so that it doesn't throw a wobbly
the first time you use it.
Private Sub Last_Name_AfterUpdate()
If NZ(IsNull(Me.[Reg_#]),0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Evi

Private Sub Last_Name_AfterUpdate()
If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Just stick a Me. in front of the first Reg_# as above.

My Bad - rushing again!

Evi
Yes the code is in the activating field, "Last Name". I added the
IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the

code
I

have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

:

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control

which
is

meant to activate it? ie it doesn't go in the field which is going to
hold
the sequential number, you don't need to type in that at all, it

goes
in

a
nearby control - one which you always fill in when you are entering a
record

Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] = DMax("[YourNumberField]","TableWhichContainsIt")
+ 1
End If

Otherwise, when you come to correct that other field, it will

reset
the

sequential number code.

Evi


Great explaination!

I entered the code but when I ran it Got teh following Error message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was
looking
for. Any ideas on the error.

Thanks for your help.

Sharon

:

*If the database is not Multi-user* then in your form, use the After
Update
Event of one of the controls which you always have to fill in and
put
the
following *single* line


Me.[YourNumberField] =
DMax("[YourNumberField]","TableWhichContainsIt")
+ 1


substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the
form
when
you create a new record, one of the first fields, if possible so
that
you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the
line

If your db is on a network, then it will be more complex,
especially
for a
Primary Key control. cI don't know how to set that up

Evi




I am creating a registration form for participants in an

event.
I

need a
sequential number assigned to each participant and displayed as
they
are
entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to other
forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[John Spencer]

I'm a bit concerned, but I believe the poster said that she had a
default value of zero on the field. IF that is true, you might want to
test to see if Reg# is zero. The control's value may be zero and not
null if some other field has already dirtied the record. You may need
to test for both.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Oh dear, I'm really not with it today Sharon. If you look at my post in
Formscoding about an EOF error I've got in a form you'll probably see why -
I'm banging my head about it!

If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = Nz(DMax("[Reg_#]", "Registrations") + 1, 0)
End If
End Sub

I tried to send this once. It didn't seem to go through.

I wish I knew the syntax for this language better. Still getting an error.
Error 2465. Can't find the "|" referred to in your experession.

It lights up the IF line. I tried it with "[Reg_#]" = syntax error for
routine; ["Reg_#"] = Error 2465; Reg_# = syntax error.

I've now got...

Private Sub Last_Name_AfterUpdate()
If Nz(IsNull(Me.[Reg_#]), 0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Sorry this is dragging on. I feel like I'm back in Comp Sci in college.

Sharontodd

I'd better also add an NZ while I'm at it so that it doesn't throw a wobbly
the first time you use it.
Private Sub Last_Name_AfterUpdate()
If NZ(IsNull(Me.[Reg_#]),0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Evi

Private Sub Last_Name_AfterUpdate()
If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Just stick a Me. in front of the first Reg_# as above.

My Bad - rushing again!

Evi
Yes the code is in the activating field, "Last Name". I added the
IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the code
I
have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

:

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control which
is
meant to activate it? ie it doesn't go in the field which is going to
hold
the sequential number, you don't need to type in that at all, it goes
in
a
nearby control - one which you always fill in when you are entering a
record
Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] =
DMax("[YourNumberField]","TableWhichContainsIt")
+ 1
End If

Otherwise, when you come to correct that other field, it will reset
the
sequential number code.

Evi


Great explaination!

I entered the code but when I ran it Got teh following Error
message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was
looking
for. Any ideas on the error.

Thanks for your help.

Sharon

:

*If the database is not Multi-user* then in your form, use the
After
Update
Event of one of the controls which you always have to fill in and
put
the
following *single* line


Me.[YourNumberField] =
DMax("[YourNumberField]","TableWhichContainsIt")
+ 1

substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the
form
when
you create a new record, one of the first fields, if possible so
that
you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the
line
If your db is on a network, then it will be more complex,
especially
for a
Primary Key control. cI don't know how to set that up

Evi




message
I am creating a registration form for participants in an event.
I
need a
sequential number assigned to each participant and displayed as
they
are
entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to
other
forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[Evi]

Quite right John, I reckon I'll quit answering questions until I get my
Questionnaire db to stop playing The Omen with me. (see formscoding 'Not
finding EOF)

Sharon

If IsNull(Me.[Reg_#]) Or Me.[Reg_#]= 0 Then
Me.[Reg_#]= Nz(DMax("[[Reg_#]]", "Registrations"), 0) + 1
End If

(and this time I've tested it)

Evi

I'm a bit concerned, but I believe the poster said that she had a
default value of zero on the field. IF that is true, you might want to
test to see if Reg# is zero. The control's value may be zero and not
null if some other field has already dirtied the record. You may need
to test for both.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Oh dear, I'm really not with it today Sharon. If you look at my post in
Formscoding about an EOF error I've got in a form you'll probably see why -
I'm banging my head about it!

If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = Nz(DMax("[Reg_#]", "Registrations") + 1, 0)
End If
End Sub

I tried to send this once. It didn't seem to go through.

I wish I knew the syntax for this language better. Still getting an error.
Error 2465. Can't find the "|" referred to in your experession.

It lights up the IF line. I tried it with "[Reg_#]" = syntax error for
routine; ["Reg_#"] = Error 2465; Reg_# = syntax error.

I've now got...

Private Sub Last_Name_AfterUpdate()
If Nz(IsNull(Me.[Reg_#]), 0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Sorry this is dragging on. I feel like I'm back in Comp Sci in college.

Sharontodd



:

I'd better also add an NZ while I'm at it so that it doesn't throw a wobbly
the first time you use it.
Private Sub Last_Name_AfterUpdate()
If NZ(IsNull(Me.[Reg_#]),0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Evi

Private Sub Last_Name_AfterUpdate()
If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Just stick a Me. in front of the first Reg_# as above.

My Bad - rushing again!

Evi
Yes the code is in the activating field, "Last Name". I added the
IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the code
I
have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

:

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control which
is
meant to activate it? ie it doesn't go in the field which is going to
hold
the sequential number, you don't need to type in that at all, it goes
in
a
nearby control - one which you always fill in when you are entering a
record
Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] =
DMax("[YourNumberField]","TableWhichContainsIt")
+ 1
End If

Otherwise, when you come to correct that other field, it will reset
the
sequential number code.

Evi


Great explaination!

I entered the code but when I ran it Got teh following Error
message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I was
looking
for. Any ideas on the error.

Thanks for your help.

Sharon

:

*If the database is not Multi-user* then in your form, use the
After
Update
Event of one of the controls which you always have to fill in and
put
the
following *single* line


Me.[YourNumberField] =
DMax("[YourNumberField]","TableWhichContainsIt")
+ 1

substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on the
form
when
you create a new record, one of the first fields, if possible so
that
you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up the
line
If your db is on a network, then it will be more complex,
especially
for a
Primary Key control. cI don't know how to set that up

Evi




message
I am creating a registration form for participants in an event.
I
need a
sequential number assigned to each participant and displayed as
they
are
entered in the form so that number can be written on the paper
registration
for possible reference later. This field will be linked to
other
forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !

 

[Arvin Meyer [MVP]]

I had this as an autonumber before but i have read many, many threads
putting
it down for this type of use as it can become random and not continuous
and
sequential. For that reason, I have been trying to find an alternative.

It can only become random if a user makes it random, one excellent reason
for NEVER allowing a user access to the tables. I've been an Access
developer for more than 15 years and I've never seen even a single case
where it can be proven that it happened all by itself.

That said, no matter what is done, you cannot renumber records in a
relational database without the skill of knowing exactly what records are
linked to what. So if a record is deleted, you will leave a gap in the
numbering sequence. This will be true no matter what method you use for
sequential numbering.

There is one advantage to using you own design for a sequential number, and
that is that if you delete, or leave the last record worked on unfinished,
you will not use the last number and can reuse that number. The disadvantage
that accompanies that is that in a multi-user environment it is possible for
2 people to get the exact same number. There are steps that can be taken to
minimize that possibility.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[sharontodd]

With some slight modification all is working great!!! Thanks for your help
and persistance.

For anyone looking for the same answer, we ended up with


If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = 1
Else
If Me.[Reg_#] = 0 Then
Me.[Reg_#] = Nz(DMax("[Reg_#]", "Registrations"), 0) + 1
End If
End If
Me.Refresh


Thanks again for all your help.

Sharontodd

Quite right John, I reckon I'll quit answering questions until I get my
Questionnaire db to stop playing The Omen with me. (see formscoding 'Not
finding EOF)

Sharon

If IsNull(Me.[Reg_#]) Or Me.[Reg_#]= 0 Then
Me.[Reg_#]= Nz(DMax("[[Reg_#]]", "Registrations"), 0) + 1
End If

(and this time I've tested it)

Evi

I'm a bit concerned, but I believe the poster said that she had a
default value of zero on the field. IF that is true, you might want to
test to see if Reg# is zero. The control's value may be zero and not
null if some other field has already dirtied the record. You may need
to test for both.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Oh dear, I'm really not with it today Sharon. If you look at my post in
Formscoding about an EOF error I've got in a form you'll probably see why -
I'm banging my head about it!

If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = Nz(DMax("[Reg_#]", "Registrations") + 1, 0)
End If
End Sub

I tried to send this once. It didn't seem to go through.

I wish I knew the syntax for this language better. Still getting an
error.
Error 2465. Can't find the "|" referred to in your experession.

It lights up the IF line. I tried it with "[Reg_#]" = syntax error for
routine; ["Reg_#"] = Error 2465; Reg_# = syntax error.

I've now got...

Private Sub Last_Name_AfterUpdate()
If Nz(IsNull(Me.[Reg_#]), 0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Sorry this is dragging on. I feel like I'm back in Comp Sci in college.

Sharontodd



:

I'd better also add an NZ while I'm at it so that it doesn't throw a
wobbly
the first time you use it.
Private Sub Last_Name_AfterUpdate()
If NZ(IsNull(Me.[Reg_#]),0) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Evi

Private Sub Last_Name_AfterUpdate()
If IsNull(Me.[Reg_#]) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Just stick a Me. in front of the first Reg_# as above.

My Bad - rushing again!

Evi
Yes the code is in the activating field, "Last Name". I added the
IF..THEN
(good idea) and now no error but no increase of Reg_#. Here is the
code
I
have.

Private Sub Last_Name_AfterUpdate()
If IsNull(Reg_#) Then
Me.[Reg_#] = DMax("[Reg_#]", "Registrations") + 1
End If
End Sub

Thanks so much for your help.

sharontodd

:

What other code do you have in the form? Something is kicking in.

Did you put the code in the *After* Update Event of the control
which
is
meant to activate it? ie it doesn't go in the field which is going
to
hold
the sequential number, you don't need to type in that at all, it
goes
in
a
nearby control - one which you always fill in when you are
entering a
record
Also, add the following to the code, so that it now says

If IsNull(YourNumberField) Then
Me.[YourNumberField] =
DMax("[YourNumberField]","TableWhichContainsIt")
+ 1
End If

Otherwise, when you come to correct that other field, it will
reset
the
sequential number code.

Evi


message
Great explaination!

I entered the code but when I ran it Got teh following Error
message.
"Run-time error '2001'. You cancelled the previous operation.

It seems like a simple, straight-forward solution. Just what I
was
looking
for. Any ideas on the error.

Thanks for your help.

Sharon

:

*If the database is not Multi-user* then in your form, use the
After
Update
Event of one of the controls which you always have to fill in
and
put
the
following *single* line


Me.[YourNumberField] =
DMax("[YourNumberField]","TableWhichContainsIt")
+ 1

substitute the real name of your table and number field.
To do this, open the form in Design View

Right click on the control (field) which you always fill in on
the
form
when
you create a new record, one of the first fields, if possible
so
that
you
can check that all is well.
Choose Properties to open the Properties box.
Click on the Events tab
Click just right of After Update and choose Event Procedure
Click just right of that row and open the code page.
Type the DMax code just above the bit that says
End Sub

If any of the code turns red then the email probably broke up
the
line
If your db is on a network, then it will be more complex,
especially
for a
Primary Key control. cI don't know how to set that up

Evi




message
I am creating a registration form for participants in an
event.
I
need a
sequential number assigned to each participant and displayed
as
they
are
entered in the form so that number can be written on the
paper
registration
for possible reference later. This field will be linked to
other
forms
later.

I am pretty new to Access so KIS for S.

THANK YOU !