Re: Track_No in sub form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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" <akuttym@xxxxxxxxxxx> wrote in message
news:52362A54-62E3-4B0B-9F77-7A9203087FB0@xxxxxxxxxxxxxxxx
Hi Evi,
I did not find any Default Value in Data Tab in control of sub form.
What can I do? Please help?

"Evi" wrote:

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" <akuttym@xxxxxxxxxxx> wrote in message
news:289BA7C0-DDE7-4843-A54A-7166F26CB56C@xxxxxxxxxxxxxxxx
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" wrote:

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" <akuttym@xxxxxxxxxxx> wrote in message
news:BFE61847-BA38-4EAA-A7F6-214599F4EB29@xxxxxxxxxxxxxxxx
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" wrote:

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" <akuttym@xxxxxxxxxxx> wrote in message
news:63222FF2-D5ED-4E4C-A23C-B133B1C1B259@xxxxxxxxxxxxxxxx
This is the Code I typed :
=NZ(DMax("[TRACK_NO]","table2"))+1
Awaiting your response.

"Al Campagna" wrote:

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" <akuttym@xxxxxxxxxxx> wrote in message
news:A4BD6897-3C41-4971-9205-9EC026072045@xxxxxxxxxxxxxxxx
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" wrote:

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" <akuttym@xxxxxxxxxxx> wrote in message
news:C2418C52-09B2-455D-B4B9-62EDC634502C@xxxxxxxxxxxxxxxx
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.


















.

Relevant Pages

  • Re: How to set default date format in Access 07
    ... If the code compiles and the missing ref has been removed then no it should ... "Nih" wrote: ... Doug Steele, Microsoft Access MVP ... Do I put the =Dateas the default value in the control on the ...
    (microsoft.public.access.gettingstarted)
  • Re: How to set default date format in Access 07
    ... unchecking/claering the missing reference. ... "Nih" wrote: ... Doug Steele, Microsoft Access MVP ... Do I put the =Dateas the default value in the control on the ...
    (microsoft.public.access.gettingstarted)
  • Re: Track_No in sub form
    ... I did not find any Default Value in Data Tab in control of sub form. ... "Evi" wrote: ... instructuions from Evi or Doug to locate the missing reference. ... Microsoft Access MVP ...
    (microsoft.public.access.reports)
  • Re: How to set default date format in Access 07
    ... If the code compiles then there is probably no problem at all in unchecking/claering the missing reference. ... It the code does not compile, then you either have to track down the missing library or locate the code that is broken. ... Doug Steele, Microsoft Access MVP ... Do I put the =Dateas the default value in the control on the ...
    (microsoft.public.access.gettingstarted)
  • Re: Track_No in sub form
    ... I have typed in my database form properties of control as you told. ... "Evi" wrote: ... I did not find any Default Value in Data Tab in control of sub form. ... instructuions from Evi or Doug to locate the missing reference. ...
    (microsoft.public.access.reports)