473,722 Members | 2,161 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Create Autonumber & query problem

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
4 7745
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*********@ya hoo.com.hk> wrote in message
news:55******** *************** ***@posting.goo gle.com...
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
"Jeff Smith" <No****@Not.Thi s.Address> wrote in message news:<cu******* ***@lust.ihug.c o.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*********@ya hoo.com.hk> wrote in message
news:55******** *************** ***@posting.goo gle.com...
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
tt*********@yah oo.com.hk (Apple) wrote in message news:<55******* *************** ****@posting.go ogle.com>...
"Jeff Smith" <No****@Not.Thi s.Address> wrote in message news:<cu******* ***@lust.ihug.c o.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*********@ya hoo.com.hk> wrote in message
news:55******** *************** ***@posting.goo gle.com...
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
> 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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5826
by: John | last post by:
Hi I have linked a master and a detail table in a 'make-table' query. Both tables have their own autonumber id fields. I am getting the 'Resultant table not allowed to have more than one Autonumber fields' when running the query. I don't want any autonumber fields in the resultant table just the current values of both id fields from each table. Is there a way to get round this problem? Thanks
1
3360
by: poohnie08 | last post by:
i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet, ============================================================================== A1 |A2 A3 A4 A5 A6 A7 A8 |A9 A10 A11 | 01/02/04 |02/02/04 StaffName |Work Hr OT Hr Slot1...
3
51266
by: Gekko . via AccessMonster.com | last post by:
I want to create a query that the first field should be an autonumber. For example I want the name and last name from the Emp table, but I want that the first field is an autonumber that identifies the record so the results of the query should look something like Field1 Name LastName 1 Gekko NoLast 2 Mary Jane 3 Jon Doe ***************************************** * This message was posted via...
5
3054
by: Apple | last post by:
May anyone can teach me how to assign a autonumber, I want to create a number that is starting with year(auto change to year 2006) + autonumber (eg. 2005-0001, 2005-0002)
1
2255
by: Smriti Dev | last post by:
Hi, I have a question regarding autonumbering . I'm using linked tables between msql and MS Access. Quite a few linked tables exist with relationships. The auto increment fields once linked loose thier autonumber property and come up as numbers. I have developed a form interface and when I press the next record the next number does not come as the autonumber property has been lost. Thier are quite a few relationships with linked...
5
2454
by: marko | last post by:
I would like my autonumber field to start at 100000. How can i do that?
2
2823
by: Hiyella2 | last post by:
Hello, I have a problem with a table in my MS Access 2003 table. The primary key of this table is an autonumber field set to a Long Integer, Yes - no duplicates. The autonumber made it to 10252. Now when I try to add a new record the autonumber started over at 1. Therefore I am getting an error message saying that it can't save this new record because it contains duplicate values. My best guess is that I need to reset the autonumber...
9
11230
by: Tom_F | last post by:
To comp.databases.ms-access -- I just discovered, to my more than mild dismay, that some tables in my Microsoft Access 2003 database have duplicate numbers in the "AutoNumber" field. (Field Size is set to "Long Integer", and New Values is set to "Increment".) I know that an old version of the Jet database engine can cause this problem, but my version of msjet40.dll is 4.0.8618.0, which is supposedly bug-free in this respect. I am...
4
4600
by: jacc14 | last post by:
Hi This is the second query I have had with Dmax. The first one I resolved as it was in a form and by using DMAX everytime I click for new record it gave a new order number ie dmax("ordernumber", "tbl_orders")+1. This problem is quite different and I am hoping someone out there can advice. I have a group of orders in a table. I tick a box to select which records I want to batch for that particular month. An update query then copies...
0
8863
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9384
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9238
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9157
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9088
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6681
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2602
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.