Track_No in sub form

A

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.
 
A

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."
 
A

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.

Al Campagna said:
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 said:
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.
 
E

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
ak said:
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.

Al Campagna said:
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 said:
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.
 
A

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."

ak said:
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.

Al Campagna said:
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 said:
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.
 
A

ak

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

Al Campagna said:
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."

ak said:
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.

Al Campagna said:
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.
 
A

ak

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

Evi said:
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
ak said:
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.

Al Campagna said:
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.
 
A

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."

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

Al Campagna said:
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."

ak said:
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.
 
E

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

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

Evi said:
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
ak said:
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.
 
E

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

ak said:
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.

Al Campagna said:
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."

ak said:
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.
 
A

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.

Evi said:
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

ak said:
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.

Al Campagna said:
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.
 
E

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


ak said:
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.

Evi said:
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

ak said:
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.
 
A

ak

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

Evi said:
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


ak said:
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.

Evi said:
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.
 
E

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

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

Evi said:
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


ak said:
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.
 
A

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...

Evi said:
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

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

Evi said:
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.
 
A

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.

Evi said:
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

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

Evi said:
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.
 
E

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
ak said:
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...

Evi said:
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

ak said:
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.
 
E

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

ak said:
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.

Evi said:
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

ak said:
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.
 
A

ak

I could not do that.

Evi said:
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

ak said:
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.

Evi said:
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.
 
E

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
ak said:
I could not do that.

Evi said:
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

ak said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top