Track_No in sub form

[ak]

I use one form, created by wizard. This is form made by wizard from Table2.
In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on.

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,

 

[Evi]

See if you can perform the following steps: Tell us which steps you can't do
Add a number field called SortNum to the table on which the form is based.
It must be a Number field.
If your form is based on a query, Open the query in design view and add this
field to the query grid

In the Form's Design View, click the Field List button and add your new
SortNum field to the form by dragging it from the Field List onto the Form.
click on the Document_Name control
Click on Properties
Click on the Events tab
Click next to After Update so that you open a code page
Just above where it says End Sub, paste the following:
If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" &
[Track_No]))+1
End If
Replace the word table2 with the real name of the table which contains the
SortNum field.

Now, when you fill in your form, don't bother to fill in SortNum, as soon as
you have filled in Document_Name, the SortNum field will fill itself in
(hopefully!)
Evi

I use one form, created by wizard. This is form made by wizard from Table2.
In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on.

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,

 

[ak]

Evi,
I have done as you told me. After SortNum field next field is NAME. Instead
of Document_Name control
2nd field is NAME. So 1st field is SortNum. Once I enter name in NAME
filed I get an error message
Run-time error '3075':
Syntax-error (missing operator) in query expression '[Track_No]<'.

As you told me once I enter name in NAME filed the SortNum have to display
new field number.
Let me tell you what are the field in this form I created.
SortNum
NAME
TRACK_NO
DOCUMETN_NAME
PROCESS_FINISHED
PROCESS FOR
FORWARDED_TO
FORWARDED DATE
NOTES
Please comment.

See if you can perform the following steps: Tell us which steps you can't do
Add a number field called SortNum to the table on which the form is based.
It must be a Number field.
If your form is based on a query, Open the query in design view and add this
field to the query grid

In the Form's Design View, click the Field List button and add your new
SortNum field to the form by dragging it from the Field List onto the Form.
click on the Document_Name control
Click on Properties
Click on the Events tab
Click next to After Update so that you open a code page
Just above where it says End Sub, paste the following:
If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" &
[Track_No]))+1
End If
Replace the word table2 with the real name of the table which contains the
SortNum field.

Now, when you fill in your form, don't bother to fill in SortNum, as soon as
you have filled in Document_Name, the SortNum field will fill itself in
(hopefully!)
Evi

I use one form, created by wizard. This is form made by wizard from Table2.
In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on.

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.

http://home.comcast.net/~cccsolutions/index.html

 

[Evi]

Ak, you need to rename the Name field. Name is a reserved word in Access. At
some time, when you least expect it, it will cause an inexplicable problem.
I see that you have spelt Document_Name as Documetn_Name - is that just a
typo?

Paste the actual code which you have on your form's code page. It is
possible that the email broke up the message lines.


Evi

Evi,
I have done as you told me. After SortNum field next field is NAME. Instead
of Document_Name control
2nd field is NAME. So 1st field is SortNum. Once I enter name in NAME
filed I get an error message
Run-time error '3075':
Syntax-error (missing operator) in query expression '[Track_No]<'.

As you told me once I enter name in NAME filed the SortNum have to display
new field number.
Let me tell you what are the field in this form I created.
SortNum
NAME
TRACK_NO
DOCUMETN_NAME
PROCESS_FINISHED
PROCESS FOR
FORWARDED_TO
FORWARDED DATE
NOTES
Please comment.

See if you can perform the following steps: Tell us which steps you can't do
Add a number field called SortNum to the table on which the form is based.
It must be a Number field.
If your form is based on a query, Open the query in design view and add this
field to the query grid

In the Form's Design View, click the Field List button and add your new
SortNum field to the form by dragging it from the Field List onto the Form.
click on the Document_Name control
Click on Properties
Click on the Events tab
Click next to After Update so that you open a code page
Just above where it says End Sub, paste the following:
If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" &
[Track_No]))+1
End If
Replace the word table2 with the real name of the table which contains the
SortNum field.

Now, when you fill in your form, don't bother to fill in SortNum, as soon as
you have filled in Document_Name, the SortNum field will fill itself in
(hopefully!)
Evi

I use one form, created by wizard. This is form made by wizard from Table2.
In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on.
:

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.

http://home.comcast.net/~cccsolutions/index.html

 

[ak]

Once going on this way by lot of question and answer I am confused. This is
my problem. Now question arise this way. What will do with track number in
Sub form.
I guess the sort number is only for sorting records. If there is SortNum I
hope no need of Track_No
Actually I need a databse to maintain my office documents.
For example there are 100 people in a company. Each one have several issues
daily such as
Passport renewal
Driving license renewal
need medical card
need exit reentry
need family visa
so on....
Once an employee e.g. serial number 7 bring 5 issues one day this 5 issues I
will file as 5 issues physically in five pages. In each page I will write
down serial number without duplicate number. In my way this is Track_No.
Main form will be keeping
SN (serial number of employee)
NAME1 (employee name)
DESIGNATIONA
LOCATION (employee's office location)
CONTACT

SN will be primary key.
Once one employee ask me about his certain date's certain case I will search
in database. So I can see his SN and Track_No.
I can take that paper according to the Track_No from the physical file which
I filed serially.
Please help me.

Ak, you need to rename the Name field. Name is a reserved word in Access. At
some time, when you least expect it, it will cause an inexplicable problem.
I see that you have spelt Document_Name as Documetn_Name - is that just a
typo?

Paste the actual code which you have on your form's code page. It is
possible that the email broke up the message lines.


Evi

Evi,
I have done as you told me. After SortNum field next field is NAME. Instead
of Document_Name control
2nd field is NAME. So 1st field is SortNum. Once I enter name in NAME
filed I get an error message
Run-time error '3075':
Syntax-error (missing operator) in query expression '[Track_No]<'.

As you told me once I enter name in NAME filed the SortNum have to display
new field number.
Let me tell you what are the field in this form I created.
SortNum
NAME
TRACK_NO
DOCUMETN_NAME
PROCESS_FINISHED
PROCESS FOR
FORWARDED_TO
FORWARDED DATE
NOTES
Please comment.

See if you can perform the following steps: Tell us which steps you can't do
Add a number field called SortNum to the table on which the form is based.
It must be a Number field.
If your form is based on a query, Open the query in design view and add this
field to the query grid

In the Form's Design View, click the Field List button and add your new
SortNum field to the form by dragging it from the Field List onto the Form.
click on the Document_Name control
Click on Properties
Click on the Events tab
Click next to After Update so that you open a code page
Just above where it says End Sub, paste the following:
If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" &
[Track_No]))+1
End If
Replace the word table2 with the real name of the table which contains the
SortNum field.

Now, when you fill in your form, don't bother to fill in SortNum, as soon as
you have filled in Document_Name, the SortNum field will fill itself in
(hopefully!)
Evi


I use one form, created by wizard. This is form made by wizard from
Table2.
In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on.
:

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

 

[ak]

I changed the NAME filed to NAME1. Still keep getting the same error.
So let me forget about the issue of Document_Name.

Ak, you need to rename the Name field. Name is a reserved word in Access. At
some time, when you least expect it, it will cause an inexplicable problem.
I see that you have spelt Document_Name as Documetn_Name - is that just a
typo?

Paste the actual code which you have on your form's code page. It is
possible that the email broke up the message lines.


Evi

Evi,
I have done as you told me. After SortNum field next field is NAME. Instead
of Document_Name control
2nd field is NAME. So 1st field is SortNum. Once I enter name in NAME
filed I get an error message
Run-time error '3075':
Syntax-error (missing operator) in query expression '[Track_No]<'.

As you told me once I enter name in NAME filed the SortNum have to display
new field number.
Let me tell you what are the field in this form I created.
SortNum
NAME
TRACK_NO
DOCUMETN_NAME
PROCESS_FINISHED
PROCESS FOR
FORWARDED_TO
FORWARDED DATE
NOTES
Please comment.

See if you can perform the following steps: Tell us which steps you can't do
Add a number field called SortNum to the table on which the form is based.
It must be a Number field.
If your form is based on a query, Open the query in design view and add this
field to the query grid

In the Form's Design View, click the Field List button and add your new
SortNum field to the form by dragging it from the Field List onto the Form.
click on the Document_Name control
Click on Properties
Click on the Events tab
Click next to After Update so that you open a code page
Just above where it says End Sub, paste the following:
If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" &
[Track_No]))+1
End If
Replace the word table2 with the real name of the table which contains the
SortNum field.

Now, when you fill in your form, don't bother to fill in SortNum, as soon as
you have filled in Document_Name, the SortNum field will fill itself in
(hopefully!)
Evi


I use one form, created by wizard. This is form made by wizard from
Table2.
In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on.
:

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

 

[Evi]

I know what you mean about the questions and answers!
Ak, keep Track_No if it is your Autonumber Primary Key field. You don't
have to look at it, you can make it invisible in forms and reports but until
you are more accustomed to Access, it would be safer to let it do its work
in the background.

Currently the DMax uses it to find which records come before the current
one.

It *sounds* as if your database structure needs to be like this:
TblLocation
LocID
Location (all the different office locations)

TblEmployee
EmpSN
EmpFirstName
EmpSurname
LocID (Linked from TblLocation

TblIssue
IssueID
Issue (eg Passport renewal, Family Visa)

TblDocument
Track_No (if that is your Autonumber, primary key)
SortNum
EmpSN (linked from TblEmployee)
IssueID (linked from TblIssue)
ReceivedDate (Date you received the document)
Other fields which concern that employee with that document


I don't know what DesignationA or Contact refer to - whether they concern
the document or the employee or both or neither, so I don't know which
table they belong to or if they need to be linked from a seperate table.


Evi

Once going on this way by lot of question and answer I am confused. This is
my problem. Now question arise this way. What will do with track number in
Sub form.
I guess the sort number is only for sorting records. If there is SortNum I
hope no need of Track_No
Actually I need a databse to maintain my office documents.
For example there are 100 people in a company. Each one have several issues
daily such as
Passport renewal
Driving license renewal
need medical card
need exit reentry
need family visa
so on....
Once an employee e.g. serial number 7 bring 5 issues one day this 5 issues I
will file as 5 issues physically in five pages. In each page I will write
down serial number without duplicate number. In my way this is Track_No.
Main form will be keeping
SN (serial number of employee)
NAME1 (employee name)
DESIGNATIONA
LOCATION (employee's office location)
CONTACT

SN will be primary key.
Once one employee ask me about his certain date's certain case I will search
in database. So I can see his SN and Track_No.
I can take that paper according to the Track_No from the physical file which
I filed serially.
Please help me.

Ak, you need to rename the Name field. Name is a reserved word in Access. At
some time, when you least expect it, it will cause an inexplicable problem.
I see that you have spelt Document_Name as Documetn_Name - is that just a
typo?

Paste the actual code which you have on your form's code page. It is
possible that the email broke up the message lines.


Evi

Evi,
I have done as you told me. After SortNum field next field is NAME. Instead
of Document_Name control
2nd field is NAME. So 1st field is SortNum. Once I enter name in NAME
filed I get an error message
Run-time error '3075':
Syntax-error (missing operator) in query expression '[Track_No]<'.

As you told me once I enter name in NAME filed the SortNum have to display
new field number.
Let me tell you what are the field in this form I created.
SortNum
NAME
TRACK_NO
DOCUMETN_NAME
PROCESS_FINISHED
PROCESS FOR
FORWARDED_TO
FORWARDED DATE
NOTES
Please comment.

:

See if you can perform the following steps: Tell us which steps you can't do
Add a number field called SortNum to the table on which the form is based.
It must be a Number field.
If your form is based on a query, Open the query in design view and

add
this

field to the query grid

In the Form's Design View, click the Field List button and add your new
SortNum field to the form by dragging it from the Field List onto

the
Form.

click on the Document_Name control
Click on Properties
Click on the Events tab
Click next to After Update so that you open a code page
Just above where it says End Sub, paste the following:
If IsNull(Me.SortNum) then
Me.SortNum = NZ(DMax("[SortNum]", "table2","[Track_No]<" &
[Track_No]))+1
End If
Replace the word table2 with the real name of the table which

contains
the

SortNum field.

Now, when you fill in your form, don't bother to fill in SortNum, as soon as
you have filled in Document_Name, the SortNum field will fill itself in
(hopefully!)
Evi


I use one form, created by wizard. This is form made by wizard from
Table2.
In this form TRACK_NO, DOCUMENT_NAME, PROCESS_FINISHED and so on.
:

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