Track_No in sub form

[ak]

Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will be
missing. This number missing not beautiful. I dont like this way. I need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your greate
favor in this regard.

 

[Al Campagna]

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[ak]

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will be
missing. This number missing not beautiful. I dont like this way. I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your greate
favor in this regard.

 

[Evi]

You've almost certainly got a Missing Reference, Ak.
Open a Module, Go to Tools, Reference and check what is there. If any are
marked as Missing, note their names and Untick them. Run Debug. If you still
have a problem, find the missing reference in the list and tick it.
Evi

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will be
missing. This number missing not beautiful. I dont like this way. I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your greate
favor in this regard.

 

[Al Campagna]

ak,
Whenever you have trouble with code, please cut and paste that code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will be
missing. This number missing not beautiful. I dont like this way. I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your greate
favor in this regard.

 

[ak]

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

ak,
Whenever you have trouble with code, please cut and paste that code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will be
missing. This number missing not beautiful. I dont like this way. I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your greate
favor in this regard.

 

[ak]

I did not got your instruction. Where is that Module to open?

You've almost certainly got a Missing Reference, Ak.
Open a Module, Go to Tools, Reference and check what is there. If any are
marked as Missing, note their names and Untick them. Run Debug. If you still
have a problem, find the missing reference in the list and tick it.
Evi

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will be
missing. This number missing not beautiful. I dont like this way. I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your greate
favor in this regard.

 

[Al Campagna]

ak,
Evi's response (in this thread) would be correct. You have a missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

ak,
Whenever you have trouble with code, please cut and paste that code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will
be
missing. This number missing not beautiful. I dont like this way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your
greate
favor in this regard.

 

[Evi]

In your main database window, click on the word Module and choose New so
that a new Module Page opens.
You will see Tools on the Menu Bar and under that, you will find References.

Evi

I did not got your instruction. Where is that Module to open?

You've almost certainly got a Missing Reference, Ak.
Open a Module, Go to Tools, Reference and check what is there. If any are
marked as Missing, note their names and Untick them. Run Debug. If you still
have a problem, find the missing reference in the list and tick it.
Evi

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will be
missing. This number missing not beautiful. I dont like this way. I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your greate
favor in this regard.

 

[Evi]

Which is the bit you don't understand, Ak. We'll try to say it another way.
We do understand that you are trying to understand the complications of
Access while using a language with which you are not completely familiar and
I'm sure that when you write 'I am not satisfied with your response' you
are not aware of how inappropriate it sounds. It will make others hesitate
to answer your posts. It is the sort of thing you would say to an employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me more.
The thing is, as I am aware about myself I am not expert in visual basic. But
please solve the problem from your side itself in easy way. I have read
Evi's reply and Doug Steele's write up too. I am in a big problem of solve
the problem. Please help me.

ak,
Evi's response (in this thread) would be correct. You have a missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste that code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will
be
missing. This number missing not beautiful. I dont like this way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your
greate
favor in this regard.

 

[ak]

Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

Which is the bit you don't understand, Ak. We'll try to say it another way.
We do understand that you are trying to understand the complications of
Access while using a language with which you are not completely familiar and
I'm sure that when you write 'I am not satisfied with your response' you
are not aware of how inappropriate it sounds. It will make others hesitate
to answer your posts. It is the sort of thing you would say to an employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me more.
The thing is, as I am aware about myself I am not expert in visual basic. But
please solve the problem from your side itself in easy way. I have read
Evi's reply and Doug Steele's write up too. I am in a big problem of solve
the problem. Please help me.

ak,
Evi's response (in this thread) would be correct. You have a missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste that code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will be
coming automatically. In case I delete this number one number will
be
missing. This number missing not beautiful. I dont like this way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming that
also waste. How to solve this problem? Would appreciate your
greate
favor in this regard.

 

[Evi]

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results as you
do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains the field
TrackNo

Evi

Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

Which is the bit you don't understand, Ak. We'll try to say it another way.
We do understand that you are trying to understand the complications of
Access while using a language with which you are not completely familiar and
I'm sure that when you write 'I am not satisfied with your response' you
are not aware of how inappropriate it sounds. It will make others hesitate
to answer your posts. It is the sort of thing you would say to an employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me more.
The thing is, as I am aware about myself I am not expert in visual

basic.
But

please solve the problem from your side itself in easy way. I have read
Evi's reply and Doug Steele's write up too. I am in a big problem of solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste that code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default

value
on

'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day

in
your

life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number

will
be

coming automatically. In case I delete this number one

number
will

be
missing. This number missing not beautiful. I dont like

this
way.

I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly

coming
that

also waste. How to solve this problem? Would appreciate your
greate
favor in this regard.

 

[ak]

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results as you
do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains the field
TrackNo

Evi

Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

Which is the bit you don't understand, Ak. We'll try to say it another way.
We do understand that you are trying to understand the complications of
Access while using a language with which you are not completely familiar and
I'm sure that when you write 'I am not satisfied with your response' you
are not aware of how inappropriate it sounds. It will make others hesitate
to answer your posts. It is the sort of thing you would say to an employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me
more.
The thing is, as I am aware about myself I am not expert in visual basic.
But
please solve the problem from your side itself in easy way. I have read
Evi's reply and Doug Steele's write up too. I am in a big problem of
solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste that
code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any
record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next number will
be
coming automatically. In case I delete this number one number
will
be
missing. This number missing not beautiful. I dont like this
way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming
that
also waste. How to solve this problem? Would appreciate your
greate
favor in this regard.

 

[Evi]

I've been looking at the very start of this thread and I think we need to
approach this differently
I think that what you need is a) keep your Autonumber field - you never have
to look at it if you don't want to but they are so easy to maintain.

Add a Number field to your table - lets call it SortNum (or whatever you
like)

To ensure that the next consecutive number is always added, in your subform
use the AfterUpdate Event of one the controls which you always use when you
want to add a new record

The code you will use will say

If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No])) + 1
End If

Replace table and field names with the real ones.


Evi

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results as you
do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains the field
TrackNo

Evi

Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

:

Which is the bit you don't understand, Ak. We'll try to say it

another
way.

We do understand that you are trying to understand the complications of
Access while using a language with which you are not completely

familiar
and

I'm sure that when you write 'I am not satisfied with your

response'
you

are not aware of how inappropriate it sounds. It will make others hesitate
to answer your posts. It is the sort of thing you would say to an employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me
more.
The thing is, as I am aware about myself I am not expert in visual basic.
But
please solve the problem from your side itself in easy way. I

have
read

Evi's reply and Doug Steele's write up too. I am in a big problem of
solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste that
code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day

in
your

life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be

the
next

larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any
record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a

day
in

your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each TRACK_NO.
TRACK_NO is auto number. If I press enter key next

number
will

be
coming automatically. In case I delete this number one number
will
be
missing. This number missing not beautiful. I dont

like
this

way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly coming
that
also waste. How to solve this problem? Would

appreciate
your

greate
favor in this regard.

 

[ak]

Evi,
I am worried about I could not do this. You are saying in right way. But
once I try to do this I could not make it practicable. The reason is my lack
of knowlege, I know. But if I get info more simlified way I believe I can do
that. Can you help me please? I created control, I went to properties etc..
etc...

I've been looking at the very start of this thread and I think we need to
approach this differently
I think that what you need is a) keep your Autonumber field - you never have
to look at it if you don't want to but they are so easy to maintain.

Add a Number field to your table - lets call it SortNum (or whatever you
like)

To ensure that the next consecutive number is always added, in your subform
use the AfterUpdate Event of one the controls which you always use when you
want to add a new record

The code you will use will say

If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No])) + 1
End If

Replace table and field names with the real ones.


Evi

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results as you
do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains the field
TrackNo

Evi


Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

:

Which is the bit you don't understand, Ak. We'll try to say it another
way.
We do understand that you are trying to understand the complications of
Access while using a language with which you are not completely familiar
and
I'm sure that when you write 'I am not satisfied with your response'
you
are not aware of how inappropriate it sounds. It will make others
hesitate
to answer your posts. It is the sort of thing you would say to an
employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain
which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me
more.
The thing is, as I am aware about myself I am not expert in visual
basic.
But
please solve the problem from your side itself in easy way. I have
read
Evi's reply and Doug Steele's write up too. I am in a big problem of
solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a
missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste that
code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default
value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the
next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any
record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day
in
your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each
TRACK_NO.
TRACK_NO is auto number. If I press enter key next number
will
be
coming automatically. In case I delete this number one
number
will
be
missing. This number missing not beautiful. I dont like
this
way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly
coming
that
also waste. How to solve this problem? Would appreciate
your
greate
favor in this regard.

 

[ak]

Evi,
I have typed in my database form properties of control as you told. But
there is a certain mistake which I made or something else such as I dont know
what is Me.SortNum something like that. But I believe you can help me more
to enable me to do that. I am sorry for this. Please help. I have more and
more worry about disturbing you. I know this is free service.

I've been looking at the very start of this thread and I think we need to
approach this differently
I think that what you need is a) keep your Autonumber field - you never have
to look at it if you don't want to but they are so easy to maintain.

Add a Number field to your table - lets call it SortNum (or whatever you
like)

To ensure that the next consecutive number is always added, in your subform
use the AfterUpdate Event of one the controls which you always use when you
want to add a new record

The code you will use will say

If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No])) + 1
End If

Replace table and field names with the real ones.


Evi

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results as you
do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains the field
TrackNo

Evi


Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

:

Which is the bit you don't understand, Ak. We'll try to say it another
way.
We do understand that you are trying to understand the complications of
Access while using a language with which you are not completely familiar
and
I'm sure that when you write 'I am not satisfied with your response'
you
are not aware of how inappropriate it sounds. It will make others
hesitate
to answer your posts. It is the sort of thing you would say to an
employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain
which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me
more.
The thing is, as I am aware about myself I am not expert in visual
basic.
But
please solve the problem from your side itself in easy way. I have
read
Evi's reply and Doug Steele's write up too. I am in a big problem of
solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a
missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste that
code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default
value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be the
next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow any
record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day
in
your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each
TRACK_NO.
TRACK_NO is auto number. If I press enter key next number
will
be
coming automatically. In case I delete this number one
number
will
be
missing. This number missing not beautiful. I dont like
this
way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly
coming
that
also waste. How to solve this problem? Would appreciate
your
greate
favor in this regard.

 

[Evi]

Ak, give us
the name of the table which contains the field SortNum
The name of the first control in your form which you always fill in when you
are creating a new record.
Evi

Evi,
I am worried about I could not do this. You are saying in right way. But
once I try to do this I could not make it practicable. The reason is my lack
of knowlege, I know. But if I get info more simlified way I believe I can do
that. Can you help me please? I created control, I went to properties etc..
etc...

I've been looking at the very start of this thread and I think we need to
approach this differently
I think that what you need is a) keep your Autonumber field - you never have
to look at it if you don't want to but they are so easy to maintain.

Add a Number field to your table - lets call it SortNum (or whatever you
like)

To ensure that the next consecutive number is always added, in your subform
use the AfterUpdate Event of one the controls which you always use when you
want to add a new record

The code you will use will say

If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No])) + 1
End If

Replace table and field names with the real ones.


Evi

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

:

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results

as
you

do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains

the
field

TrackNo

Evi


Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

:

Which is the bit you don't understand, Ak. We'll try to say it another
way.
We do understand that you are trying to understand the

complications
of

Access while using a language with which you are not completely familiar
and
I'm sure that when you write 'I am not satisfied with your response'
you
are not aware of how inappropriate it sounds. It will make others
hesitate
to answer your posts. It is the sort of thing you would say to an
employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain
which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me
more.
The thing is, as I am aware about myself I am not expert in visual
basic.
But
please solve the problem from your side itself in easy way. I have
read
Evi's reply and Doug Steele's write up too. I am in a big

problem
of

solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a
missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode

from
the

menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day

in
your

life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and

paste
that

code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a

day
in

your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default
value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will

be
the

next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't

allow
any

record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work

a
day

in
your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each
TRACK_NO.
TRACK_NO is auto number. If I press enter key next number
will
be
coming automatically. In case I delete this number one
number
will
be
missing. This number missing not beautiful. I dont like
this
way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly
coming
that
also waste. How to solve this problem? Would appreciate
your
greate
favor in this regard.

 

[Evi]

Hi ak, sorry about the delay. I fell down the stairs and hurt my arm and
haven't been able to write for a couple of days.
Me.SortNum
In code pages, when you want to write about a control in the form or report
which is open, you write
Me
a dot
and the name of the control.
Evi

Evi,
I have typed in my database form properties of control as you told. But
there is a certain mistake which I made or something else such as I dont know
what is Me.SortNum something like that. But I believe you can help me more
to enable me to do that. I am sorry for this. Please help. I have more and
more worry about disturbing you. I know this is free service.

I've been looking at the very start of this thread and I think we need to
approach this differently
I think that what you need is a) keep your Autonumber field - you never have
to look at it if you don't want to but they are so easy to maintain.

Add a Number field to your table - lets call it SortNum (or whatever you
like)

To ensure that the next consecutive number is always added, in your subform
use the AfterUpdate Event of one the controls which you always use when you
want to add a new record

The code you will use will say

If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No])) + 1
End If

Replace table and field names with the real ones.


Evi

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

:

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results

as
you

do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains

the
field

TrackNo

Evi


Evi,
I am so sorry about my language problem and I know you are doing a free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

:

Which is the bit you don't understand, Ak. We'll try to say it another
way.
We do understand that you are trying to understand the

complications
of

Access while using a language with which you are not completely familiar
and
I'm sure that when you write 'I am not satisfied with your response'
you
are not aware of how inappropriate it sounds. It will make others
hesitate
to answer your posts. It is the sort of thing you would say to an
employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain
which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please help me
more.
The thing is, as I am aware about myself I am not expert in visual
basic.
But
please solve the problem from your side itself in easy way. I have
read
Evi's reply and Doug Steele's write up too. I am in a big

problem
of

solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a
missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode

from
the

menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day

in
your

life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and

paste
that

code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a

day
in

your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default
value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will

be
the

next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't

allow
any

record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work

a
day

in
your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each
TRACK_NO.
TRACK_NO is auto number. If I press enter key next number
will
be
coming automatically. In case I delete this number one
number
will
be
missing. This number missing not beautiful. I dont like
this
way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly
coming
that
also waste. How to solve this problem? Would appreciate
your
greate
favor in this regard.

 

[ak]

I could not do that.

Hi ak, sorry about the delay. I fell down the stairs and hurt my arm and
haven't been able to write for a couple of days.
Me.SortNum
In code pages, when you want to write about a control in the form or report
which is open, you write
Me
a dot
and the name of the control.
Evi

Evi,
I have typed in my database form properties of control as you told. But
there is a certain mistake which I made or something else such as I dont know
what is Me.SortNum something like that. But I believe you can help me more
to enable me to do that. I am sorry for this. Please help. I have more and
more worry about disturbing you. I know this is free service.

I've been looking at the very start of this thread and I think we need to
approach this differently
I think that what you need is a) keep your Autonumber field - you never have
to look at it if you don't want to but they are so easy to maintain.

Add a Number field to your table - lets call it SortNum (or whatever you
like)

To ensure that the next consecutive number is always added, in your subform
use the AfterUpdate Event of one the controls which you always use when you
want to add a new record

The code you will use will say

If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" & [Track_No])) + 1
End If

Replace table and field names with the real ones.


Evi

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

:

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same results as
you
do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab, type the
formula next to where it says Default Value

Replace table2 with the correct name for your table which contains the
field
TrackNo

Evi


Evi,
I am so sorry about my language problem and I know you are doing a
free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What is NZ?
Sorry for disturbance.

:

Which is the bit you don't understand, Ak. We'll try to say it
another
way.
We do understand that you are trying to understand the complications
of
Access while using a language with which you are not completely
familiar
and
I'm sure that when you write 'I am not satisfied with your
response'
you
are not aware of how inappropriate it sounds. It will make others
hesitate
to answer your posts. It is the sort of thing you would say to an
employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain
which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please
help me
more.
The thing is, as I am aware about myself I am not expert in visual
basic.
But
please solve the problem from your side itself in easy way. I
have
read
Evi's reply and Doug Steele's write up too. I am in a big problem
of
solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct. You have a
missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select View/ViewCode from
the
menubar. That will place yoiu in the code module... follow the
instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut and paste
that
code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day
in
your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default
value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger
field.
On your subform, set the DefaultValue of TrackNo to...
=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo will be
the
next
larger
value than is stored in the table.
Also, to help insure no sequence gaps... don't allow
any
record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a
day
in
your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each
TRACK_NO.
TRACK_NO is auto number. If I press enter key next
number
will
be
coming automatically. In case I delete this number one
number
will
be
missing. This number missing not beautiful. I dont
like
this
way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly
coming
that
also waste. How to solve this problem? Would
appreciate
your
greate
favor in this regard.

 

[Evi]

Ak, what is the name of a control in your form, not the Autonumber field,
but the first one which you fill in when you are adding a new record?
Evi

I could not do that.

Hi ak, sorry about the delay. I fell down the stairs and hurt my arm and
haven't been able to write for a couple of days.
Me.SortNum
In code pages, when you want to write about a control in the form or report
which is open, you write
Me
a dot
and the name of the control.
Evi

Evi,
I have typed in my database form properties of control as you told. But
there is a certain mistake which I made or something else such as I

dont
know

what is Me.SortNum something like that. But I believe you can help me more
to enable me to do that. I am sorry for this. Please help. I have

more
and

more worry about disturbing you. I know this is free service.

:

I've been looking at the very start of this thread and I think we

need
to

approach this differently
I think that what you need is a) keep your Autonumber field - you

never
have

to look at it if you don't want to but they are so easy to maintain.

Add a Number field to your table - lets call it SortNum (or whatever you
like)

To ensure that the next consecutive number is always added, in your subform
use the AfterUpdate Event of one the controls which you always use

when
you

want to add a new record

The code you will use will say

If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" &

[Track_No]))
+ 1

End If

Replace table and field names with the real ones.


Evi

Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

:

Hi Ak
I've just realized that you are probably using this in your *Table*'s
Default Value. I've just tried this on a database table with no missing
references for the DMax and NZ functions and I get the same

results
as

you
do.

What Al said is that you have to use the function in your Subform

To do this, open your Subform by itself in Design View, click on your
Control. Click on the Properties button, and on the Data Tab,

type
the

formula next to where it says Default Value

Replace table2 with the correct name for your table which

contains
the

field
TrackNo

Evi


Evi,
I am so sorry about my language problem and I know you are doing a
free
service.
I need to know why not working once I type the below :
NZ(DMax("[TrackNo]", "table2")) +1
Anyway I need to see that properly working. Please help me.
Where is the problem? Is it problem of wrong table name? What

is
NZ?

Sorry for disturbance.

:

Which is the bit you don't understand, Ak. We'll try to say it
another
way.
We do understand that you are trying to understand the complications
of
Access while using a language with which you are not completely
familiar
and
I'm sure that when you write 'I am not satisfied with your
response'
you
are not aware of how inappropriate it sounds. It will make others
hesitate
to answer your posts. It is the sort of thing you would say

to
an

employee
to whom you are paying a salary.

In fact, Al's explanation sounds very clear to me. So please explain
which
bit isn't clear to you.
Evi

Dear Expert,
I am not satisfied with your response this way. Please Please
help me
more.
The thing is, as I am aware about myself I am not expert

in
visual

basic.
But
please solve the problem from your side itself in easy way. I
have
read
Evi's reply and Doug Steele's write up too. I am in a big problem
of
solve
the problem. Please help me.

:

ak,
Evi's response (in this thread) would be correct.

You
have a

missing
Reference, so Access doesn't know what an NZ function is.
Read Evi's reply, and in addition, you can try Doug Steele's
excellent
write up on the subject.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

While in Design mode for your form, select

View/ViewCode
from

the
menubar. That will place yoiu in the code module...

follow
the

instructuions from Evi or Doug to locate the missing reference.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a

day
in

your
life."

This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

:

ak,
Whenever you have trouble with code, please cut

and
paste

that
code
"exactly" into your reply.
We need to see your code just as you have it.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never

work a
day

in
your
life."

I have typed as per you mentioned. But in vein.
I get a remark as :
Unknown function 'NZ' in validation expression or default
value
on
'Table2.SN'.
Please help.

:

ak,
Instead of using an autonumber, use a LongInteger
field.
On your subform, set the DefaultValue of

TrackNo
to...

=NZ(DMax("[TrackNo]", "table2")) +1
Every time you create a new record, TrackNo

will
be

the
next
larger
value than is stored in the table.
Also, to help insure no sequence gaps...

don't
allow

any
record
deletions in that table.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never

work
a

day
in
your
life."


Dear Experts,

I have created table1 as main form :
SN, NAME
Then created table2 as sub-form :
TRACK_NO, DOCUMENT_NAME, RECEIVED_DATE

The problem is I enter document information for each
TRACK_NO.
TRACK_NO is auto number. If I press enter key next
number
will
be
coming automatically. In case I delete this

number
one

number
will
be
missing. This number missing not beautiful. I dont
like
this
way.
I
need
all numbers (TRACK_NO). In case 2 numbers automaticaly
coming
that
also waste. How to solve this problem? Would
appreciate
your
greate
favor in this regard.