473,226 Members | 1,358 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

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 7709
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
"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
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
> 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
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...
1
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,...
3
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...
5
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
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...
5
by: marko | last post by:
I would like my autonumber field to start at 100000. How can i do that?
2
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....
9
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...
4
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",...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.