473,883 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Auto fill a Query with an increment number

7 New Member
I am using Access 2003 and I need your help. I have a "Contributi on Summary Query" that adds up all year members contribution. In the query I have to generate a Receipt No. to each recordset in the query which will start with the "YEAR000001 " and increment (i.e. +1) to the next recordset.

I tried Make table Query, Update Query but does not work.

Please help.
Feb 1 '12 #1
18 15073
32,584 Recognized Expert Moderator MVP
I don't believe that's possible without using code. Even the Make + Update approach cannot work, as you've found out.
Feb 1 '12 #2
759 Contributor
Are you really interested to design the query that way ? or you need to show the results that way ?

There are two different things. Since first can't be accomplished (I don't know exactly but I belive in NeoPa's skills) the second one can be done for a report. Here, in this forum, is a thread (sorry but I have no time to looking for) with answer to question how to increment values on a column.
Feb 2 '12 #3
2,322 Recognized Expert Moderator Top Contributor
We need more details on your application and what is expected to happen to the data after you have created the query. I presume that the information has to be stored once generated?

Its certainly possible, but I do believe as NeoPa said that you will require some code to make it work. That said its not terribly complicated.

Other relevant details, how often do you expect to run this query? How r your (relevant) tables setup?
Feb 2 '12 #4
Tony Liane
7 New Member
1) We will run this Query once a year, then a report will be run from this Query to show this receipt no. "YEAR0000X" ..." on each printed receipt (one per member). We then issue receipts to the members. We can store the info using the Make to table Query and keep it for future reference.
Feb 2 '12 #5
32,584 Recognized Expert Moderator MVP
Although the reporting side could be done within the report itself (as indicated by Mihail), this is not the case for storing the data for later.

As such, you will need to take the code route as suggested by a number of responses. If you'd like help with this I suggest you respond with the information it was made clear would be necessary in Smiley's post #4. To have omitted this information in your post is curious to say the least.
Feb 2 '12 #6
759 Contributor
Smiley and NeoPa !
I can fill a field in a table (using VBA, of course).
Is it possible to do that for a query ?
I can't see any way even using VBA.
Thank you !
Feb 3 '12 #7
32,584 Recognized Expert Moderator MVP
You cannot put data into a query Mihail, as queries are not able to store data. The data shown by queries can only come from the record sources and/or the design of the query itself. As such, the most you can really do is to populate data in a table that a query is built on, which will then be displayed when the query is run.

I suspect this is also your understanding.
Feb 3 '12 #8
Tony Liane
7 New Member
Please see attached doc file for relevant information. If it cannot be done in the query then maybe in the report.

If in the report, How then can we store the info for future use.
Attached Files
File Type: doc Bytes.doc (37.0 KB, 661 views)
Feb 3 '12 #9
2,322 Recognized Expert Moderator Top Contributor
Hey Tony

It surely is doable, and I would be happy to help you with it. A quick question first though, based on your example, since I think this is a simpler solution. Why not simply base the receipt numbers on the year and the member no. The member no is unique, and will thus generate a unique receipt number as well.

This approach would have several benefits in my oppinion:
  • No Need for Extra Tables
  • Normalized properly
  • No need for a function that should only be run once per year
  • Less risk of data consistency errors

Drawbacks would be:
  • You get missing numbers, I.e. no receipt 201200000001 if member no 1 makes no contributions that year.

This simple statement should work as part of your query:
Expand|Select|Wrap|Line Numbers
  1. ReceiptNo: Year([dt_ContributionDate]) & Format([lngMemberNo], "0000000")
Where dt_Contribution Date is the date of the contribution and lngMemberNo is the member ID field. Rename as appropriate. I should warn you that using Date (per your word example) as a field name can sometimes cause trouble since it is also the name of the VBA function Date, which returns todays date.
The above code yields for member no 128 the value: 20120000128

If the above is not acceptable, please let me know, and I can whip up some code for you.
Feb 3 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Maurice Mertens | last post by:
Does anyone know how to get an auto-filling combobox on a form? When I add a combobox to a form I can set the DropDownStyle property to DropDownList. This will make the combobox auto-filling. But the user then can't enter text in the combobox which isn't in the list. So I have to set the DropDownStyle property to DropDown to enable the user to enter text which isn't in the list. But unfortunately the combobox doesn't auto-fill the text...
by: Maurice Mertens | last post by:
Hi all, In VB.NET you can set the DropDownStyle for a combobox to 'DropDown' or 'DropDownList'. When you set it to DropDownList, it supports auto-fill. But when you set it to 'DropDown', the auto-fill support is gone. How can I get auto-fill on a combobox with a dropdownstyle 'DropDown'? --
by: rasta | last post by:
i've a web page that loads the entire cust record tbl to simulate a auto fill during data entry this works great when tot cust recs < 1500, going over the 1500 numbers the conescute page loads take several seconds. that is the user must wait few seconds before the next page appears and then they can continue their order entry i am looking for a way to imporve this at for least 10000 records
by: ChuckDubya | last post by:
It's common in web browsers to have a text auto-fill function for personal information or passwords or whatnot. The flavor that I'm referring to is the kind that pops up as you're typing the word, not the kind that fills text fields before typing has started. Well I want to know how to do that in Python. I'm looking to edit IDLE so that it doesn't take me forever to code four lines. Eventually, I want to create a Visual Basic-type...
by: MatGyver | last post by:
I am going nuts trying to figure this out, any help will be appreciated. I have an existing table called "Parts". And in this table I have the following columns: "ID" "Part Number" "Part Description" "Part Cost" I am trying to build an order form that will have "Part Number" and "Part Cost" both on the form and in that forms specific table.
by: big_boy | last post by:
i am working with access 2000. i have a form that when you feel it out, it stores the information in the table for the form. am trying to write a auto fill query for it to automatically fill the rest of the information when the name matches the one already there......help
by: Elainie | last post by:
I have been trying to auto fill fields with DLookup - but it doesn't seem to work..... can you help... I have a name field and I want the address to automatically fill in when the name field is filled in. I have load of othre fields that would benefit from this so I need to get it working.... =DLookUp("",""," =") I have an unbound gp_practice field and want to fill in the gpcode
by: KelHemp | last post by:
Greetings, I've been using this site for lots of access help in the past, and it's very helpful! I have a new complexity for you all. Reworking a form to record 70-80 years of oil production on multiple leases, which have multiple wells within them. Most wells can fit on one printed page (one record), but in the case of overspill, we want to do the following: -Move to a new record and auto fill the LeaseName, LeaseNumber, Operator,...
by: Steve67 | last post by:
I didn't see a search feature in these forums, so I couldn't look up this question and I am sure it is a common questions. I am trying to auto fill some information on an form. Here is what I have going on. I created a form that will fill in information on one table, but I also want information from another table on this form, but I want this infomation to automatically fill in was soon as I type lets say a part number. I tried to do this by...
by: Charles Richmond | last post by:
Is there some parameter I can set via Javascript that will cause the browser to *not* auto-fill forms, regardless of the browser settings??? -- +----------------------------------------------------------------+ | Charles and Francis Richmond richmond at plano dot net | +----------------------------------------------------------------+
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...
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,...
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...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
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...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
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
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.