Create Autonumber & query problem

Apple

1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.

2. I had create a query by making relation to a table & query, but I
can't update record in query or in form. I believe the problem is due
to the source query.
In source query, there is a filter to show the incomplete record
("is null" in delivery date)], but I need to re-use the job no. if the
job is completed, so the same job no. will be more than one record
with no filter.

May anyone can help to solve the above problem.Thank!

Nov 13 '05 #1

Jeff Smith

You've asked these questions before and there has been some very good
replies to them. Please make the effort to read them. If you don't
understand the answers, post a reply to the same thread explaining why you
don't understand the answers and someone might be able to clarify the
answer.

Now for my 2 cents worth.

An autonumber is an unique record identifier which is not supposed to be
read by humans. It's very useful for the ID field of a table i.e.
CustomerID, OrderID, StudentID, ClaimID etc. What you're after her is a
human readable identifier which will be displayed on the form. FlSteve gave
you an answer on 29 January 2005. His solution requires some good VBA
knowledge and so does mine.

With VBA you need to find the value of the last number and compare the first
4 digits with the current year. If they don't match your custom autonumber
will start from 0001. If the year does match you then need to extract the
last 4 digits, add 1 to the number and then combine it with the year. This
code would be run in the form's Before Insert event and added to the field
which displays and stores this custom autonumber. If you understand VBA,
someone might post some code that might do this if you ask. But someone with
some basic VBA knowledge should be able to write some code that works using
the guide I've just given.

With your second question. This has also been answered before and you
replied to the answer. Joining a query to a table inside a query can make
that query not updateable which mean you won't be able to add, delete or
modify the records. Please post you table and query structures so we can
assess your problem.

Jeff.
"Apple" <tt*********@yahoo.com.hk> wrote in message
news:55**************************@posting.google.c om...

1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.

2. I had create a query by making relation to a table & query, but I
can't update record in query or in form. I believe the problem is due
to the source query.
In source query, there is a filter to show the incomplete record
("is null" in delivery date)], but I need to re-use the job no. if the
job is completed, so the same job no. will be more than one record
with no filter.

May anyone can help to solve the above problem.Thank!

Nov 13 '05 #2

Apple

"Jeff Smith" <No****@Not.This.Address> wrote in message news:<cu**********@lust.ihug.co.nz>...

You've asked these questions before and there has been some very good
replies to them. Please make the effort to read them. If you don't
understand the answers, post a reply to the same thread explaining why you
don't understand the answers and someone might be able to clarify the
answer.

Now for my 2 cents worth.

An autonumber is an unique record identifier which is not supposed to be
read by humans. It's very useful for the ID field of a table i.e.
CustomerID, OrderID, StudentID, ClaimID etc. What you're after her is a
human readable identifier which will be displayed on the form. FlSteve gave
you an answer on 29 January 2005. His solution requires some good VBA
knowledge and so does mine.

With VBA you need to find the value of the last number and compare the first
4 digits with the current year. If they don't match your custom autonumber
will start from 0001. If the year does match you then need to extract the
last 4 digits, add 1 to the number and then combine it with the year. This
code would be run in the form's Before Insert event and added to the field
which displays and stores this custom autonumber. If you understand VBA,
someone might post some code that might do this if you ask. But someone with
some basic VBA knowledge should be able to write some code that works using
the guide I've just given.

With your second question. This has also been answered before and you
replied to the answer. Joining a query to a table inside a query can make
that query not updateable which mean you won't be able to add, delete or
modify the records. Please post you table and query structures so we can
assess your problem.

Jeff.
"Apple" <tt*********@yahoo.com.hk> wrote in message
news:55**************************@posting.google.c om...

1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.

2. I had create a query by making relation to a table & query, but I
can't update record in query or in form. I believe the problem is due
to the source query.
In source query, there is a filter to show the incomplete record
("is null" in delivery date)], but I need to re-use the job no. if the
job is completed, so the same job no. will be more than one record
with no filter.

May anyone can help to solve the above problem.Thank!

Thank you Jeff!
1. I understand what you are talking, but I'm not good in use of VBA.
Is there anyone can teach me to do it step by step.

2. (a). source table with fields - receipt no(primary key), job no,
payment date, payment.
(b). source query with fields - record id(primary key), job no
(this no. will be re-used if the "complete date" is filled, customer
code, customer name, tel , mobile, goods id, order date, complete
date(filter-"is null")
c. New query with fields - (a)!receipt no, (a)!job no, (a)!payment
date, (a)!payment, (b)!customer code, (b)!cusotmer name.

Should there be enough information for someone to access my problem!
Thank!

Nov 13 '05 #3

Apple

tt*********@yahoo.com.hk (Apple) wrote in message news:<55**************************@posting.google. com>...

"Jeff Smith" <No****@Not.This.Address> wrote in message news:<cu**********@lust.ihug.co.nz>...

You've asked these questions before and there has been some very good
replies to them. Please make the effort to read them. If you don't
understand the answers, post a reply to the same thread explaining why you
don't understand the answers and someone might be able to clarify the
answer.

Now for my 2 cents worth.

An autonumber is an unique record identifier which is not supposed to be
read by humans. It's very useful for the ID field of a table i.e.
CustomerID, OrderID, StudentID, ClaimID etc. What you're after her is a
human readable identifier which will be displayed on the form. FlSteve gave
you an answer on 29 January 2005. His solution requires some good VBA
knowledge and so does mine.

With VBA you need to find the value of the last number and compare the first
4 digits with the current year. If they don't match your custom autonumber
will start from 0001. If the year does match you then need to extract the
last 4 digits, add 1 to the number and then combine it with the year. This
code would be run in the form's Before Insert event and added to the field
which displays and stores this custom autonumber. If you understand VBA,
someone might post some code that might do this if you ask. But someone with
some basic VBA knowledge should be able to write some code that works using
the guide I've just given.

With your second question. This has also been answered before and you
replied to the answer. Joining a query to a table inside a query can make
that query not updateable which mean you won't be able to add, delete or
modify the records. Please post you table and query structures so we can
assess your problem.

Jeff.
"Apple" <tt*********@yahoo.com.hk> wrote in message
news:55**************************@posting.google.c om...

1. I want to create an autonumber, my requirement is : 2005/0001
(Year/autonumber), which year & autonumber no. both can auto run.

2. I had create a query by making relation to a table & query, but I
can't update record in query or in form. I believe the problem is due
to the source query.
In source query, there is a filter to show the incomplete record
("is null" in delivery date)], but I need to re-use the job no. if the
job is completed, so the same job no. will be more than one record
with no filter.

May anyone can help to solve the above problem.Thank!

Thank you Jeff!
1. I understand what you are talking, but I'm not good in use of VBA.
Is there anyone can teach me to do it step by step.

2. (a). source table with fields - receipt no(primary key), job no,
payment date, payment.
(b). source query with fields - record id(primary key), job no
(this no. will be re-used if the "complete date" is filled, customer
code, customer name, tel , mobile, goods id, order date, complete
date(filter-"is null")
c. New query with fields - (a)!receipt no, (a)!job no, (a)!payment
date, (a)!payment, (b)!customer code, (b)!cusotmer name.

Should there be enough information for someone to access my problem!
Thank!

Can someone help me!!Please !!

Nov 13 '05 #4

Darryl Kerkeslager

> Can someone help me!!Please !!

Ask your question again. Ask one question. Explain what you have done
already to solve your own problem.

If you want a detailed tutorial on using VBA and Access, rather than an
answer to a question, you can use Google to search the web for such things.

This one from Florida Gulf Coast University looks promising:
http://www.fgcu.edu/support/office2000/access/

Darryl Kerkeslager

Nov 13 '05 #5