469,306 Members | 2,121 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Auto fill a Query with an increment number

I am using Access 2003 and I need your help. I have a "Contribution 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 14002
NeoPa
32,173 Expert Mod 16PB
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
Mihail
759 512MB
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
TheSmileyCoder
2,321 Expert Mod 2GB
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
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
NeoPa
32,173 Expert Mod 16PB
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
Mihail
759 512MB
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
NeoPa
32,173 Expert Mod 16PB
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
@TheSmileyCoder
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, 610 views)
Feb 3 '12 #9
TheSmileyCoder
2,321 Expert Mod 2GB
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_ContributionDate 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
Dear Smiley,

Thanks for the code, I have tried this in the past but it is a government issue and there should not be any missing no.

What is the other alternative.
Feb 4 '12 #11
TheSmileyCoder
2,321 Expert Mod 2GB
Hi Tony.

I understand completely. I just want to make sure we dont get so caught up in the solution that we forget to make sure we are solving the correct problem.


Now I have created a sample database (attached).

It contains:
2 tables:
  • tbl_Contributions
    Stores the individual contributions as per your word file. Field names are slightly different (they follow my own naming convention, I trust you can adapt as needed)
  • tbl_YearlyReciept
    Stores the receipt data. The issue here is that the table should ONLY be updated once a year (Probably some time in january)

1 Query:
  • qry_Template
    Just the query from the query designer. I designed the query here, then copy/pasted SQL over into the code module to make the modifications needed to make it work on a per year basis.

2 Modules
  • modTestData
    A module with some code I used to generate testdata. See code below.
  • modGenerateYearlyData
    A module with the code I used to sum up values and transfer them to a second table. Then I open a recordset and sequentially add the receipt number into the table. The append query as well as the recordset operation is enclosed in a transaction to ensure that if one fails, no updates are made.
    Code is also attached below.
Expand|Select|Wrap|Line Numbers
  1. Public Sub GenereateTestData()
  2.     'Variables
  3.         Dim lngMemberNo As Long
  4.         Dim intRandom As Integer
  5.         Dim intAmountType As Integer
  6.         Dim intI As Integer
  7.  
  8.     'Open table contributions for data entry
  9.         Dim rsContributions As DAO.Recordset
  10.         Set rsContributions = CurrentDb.OpenRecordset("tbl_Contributions", dbOpenDynaset)
  11.  
  12.     With rsContributions
  13.  
  14.         'Loop through creating a random number (1-50) of contributions for each member
  15.  
  16.             For lngMemberNo = 1 To 100
  17.                 intRandom = Int(Rnd() * 50 + 1)
  18.                 For intI = 1 To intRandom
  19.                     .AddNew
  20.                         !FK_MemberNo = lngMemberNo
  21.                         'Create random date between 2010-01-01 and 2012-12-31
  22.                         !dt_Date = DateAdd("d", Int(Rnd() * 1095), #1/1/2010#)
  23.                         intAmountType = Int(Rnd() * 4 + 1)
  24.                         Select Case intAmountType
  25.                             Case 1
  26.                                 !lng_Amount1 = Int(Rnd() * 1000 + 1)
  27.                             Case 2
  28.                                 !lng_Amount2 = Int(Rnd() * 1000 + 1)
  29.                             Case 3
  30.                                 !lng_Amount3 = Int(Rnd() * 1000 + 1)
  31.                             Case 4
  32.                                 !lng_Amount4 = Int(Rnd() * 1000 + 1)
  33.                         End Select
  34.                     .Update
  35.                 Next intI
  36.             Next lngMemberNo
  37.     End With
  38.  
  39. 'Cleanup
  40.     Set rsContributions = Nothing
  41. End Sub


Expand|Select|Wrap|Line Numbers
  1. Public Function GenerateYearlyData(lngYear As Long) As Integer
  2. On Error GoTo err_Handler
  3.     'First make some very basic error validation. You may or may not want to expand on this. Your call
  4.         If lngYear < 2010 Or lngYear > 2030 Then
  5.             MsgBox "The year entered [" & lngYear & "] is invalid. Valid data range is:" & _
  6.                     "2010 to 2030"
  7.             GenerateYearlyData = vbNo
  8.             Exit Function
  9.         End If
  10.  
  11.  
  12.     'Check that no entries allready exist in tbl_YearlyReceipt for requested year, as that would indicate some sort of problem
  13.         If DCount("*", "tbl_YearlyReceipt", "lng_Year=" & lngYear) > 0 Then
  14.             MsgBox "The year requested [" & lngYear & "] allready exists in table"
  15.             GenerateYearlyData = vbNo
  16.             Exit Function
  17.         End If
  18.  
  19.  
  20.  
  21.  
  22.  
  23.     'Encapsulate in transaction, in case error occurs
  24.         Dim wks As DAO.Workspace
  25.         Dim myDB As DAO.Database
  26.         Set wks = DBEngine.Workspaces(0)
  27.         Set myDB = CurrentDb
  28.  
  29.  
  30.  
  31.     'Now run query to sum up the yearly amount and transfer to tbl_YearlyReceipt
  32.         Dim strSQL As String
  33.         strSQL = "INSERT INTO tbl_YearlyReceipt ( FK_MemberNo, lng_Year, lng_SumAmount1, lng_SumAmount2, lng_SumAmount3, lng_SumAmount4 )" & _
  34.                 " SELECT tbl_Contributions.FK_MemberNo, Year([dt_Date]) AS YearValue, Sum(tbl_Contributions.lng_Amount1) AS SumOflng_Amount1, Sum(tbl_Contributions.lng_Amount2) AS SumOflng_Amount2, Sum(tbl_Contributions.lng_Amount3) AS SumOflng_Amount3, Sum(tbl_Contributions.lng_Amount4) AS SumOflng_Amount4 " & _
  35.                 " FROM tbl_Contributions " & _
  36.                 " WHERE (((tbl_Contributions.dt_Date) >= #1/1/" & lngYear & "# And (tbl_Contributions.dt_Date) < #1/1/" & lngYear + 1 & "#)) " & _
  37.                 " GROUP BY tbl_Contributions.FK_MemberNo, Year([dt_Date]); "
  38.         wks.BeginTrans
  39.             myDB.Execute strSQL, dbFailOnError
  40.  
  41.         'Now open recordset to add the receiptnumber
  42.         Dim rsYearlyReceipt As DAO.Recordset
  43.         Set rsYearlyReceipt = myDB.OpenRecordset("SELECT * FROM tbl_YearlyReceipt where lng_Year=" & lngYear, dbOpenDynaset)
  44.  
  45.         Dim lngRecieptNumber As Long
  46.         lngRecieptNumber = 1
  47.         Dim strRecipt As String
  48.  
  49.         Do While Not rsYearlyReceipt.EOF
  50.             rsYearlyReceipt.Edit
  51.                 rsYearlyReceipt!tx_ReceiptNumber = lngYear & Format(lngRecieptNumber, "000000")
  52.             rsYearlyReceipt.Update
  53.             lngRecieptNumber = lngRecieptNumber + 1
  54.             rsYearlyReceipt.MoveNext
  55.         Loop
  56.  
  57.     'Succesfull
  58.         wks.CommitTrans
  59.         GenerateYearlyData = vbYes
  60.  
  61. cleanup:
  62.     Set wks = Nothing
  63.     Set myDB = Nothing
  64.     Set rsYearlyReceipt = Nothing
  65.  
  66.  
  67.     Exit Function
  68.  
  69.  
  70. err_Handler:
  71.     MsgBox "An error occured while trying to generate yearly recipts data" & vbNewLine & _
  72.             Err.Number & " - " & Err.Description
  73.     GenerateYearlyData = vbError
  74.     If Not wks Is Nothing Then wks.Rollback
  75.  
  76.     Resume cleanup
  77. End Function
  78.  


I hope you find it understandable and usefull. Best of luck with your project.
Attached Files
File Type: zip TonyLiane.zip (54.6 KB, 265 views)
Feb 4 '12 #12
Dear Smiley,

It is with gratitude and thanks to you for taking the time to help me with my project. I appreciate it bro and I will put it to the test in the following days and will advise you the results.

I tried the database you attached to your message a little to get to nunderstand it. I deleted all the records in tbl_YearReceipt and ran module modGenYearlyData. It did create all the data in the table except for the Receipt no, the fields are blank.

1) Is this something I should tell you about.
2) To run the module modGenYearlyData once per year, should this
be done through a form?

Thanks again.
Feb 5 '12 #13
TheSmileyCoder
2,321 Expert Mod 2GB
1) Yes
I have tested it before I uploaded it, and found no issues. So I am a bit baffled by your experience. Did you not receive any error messages? Did you make any changes to the code?
2) It could be done through or form, or it could be done through the Immediate pane in vba. This all depends on whether its an application your developing for yourself, or for others, and how much work you want to put into desinging a form when its only going to be used once per year.
Feb 5 '12 #14
NeoPa
32,173 Expert Mod 16PB
Smiley:
and how much work you want to put into desinging a form when its only going to be used once per year.
If I may interject here - I would suggest a form could even be designed for your own use. The bare necessities of such a form are :
  1. Create form (from scratch will do).
  2. Add a single CommandButton control.
  3. Set the OnClick property to "[Event Procedure]".
  4. #3 takes you to the VBA IDE where you simply type in what you would in the immediate pane every year otherwise - a call to the procedure.

Once you've named the form it's always there as an obvious reminder of what's required annually ;-)
Feb 5 '12 #15
NeoPa
32,173 Expert Mod 16PB
Tony,

Pleased though I am that you've chosen my post as the best answer, I can't help thinking that one of the posts that actually dealt with the main question of the thread would be a more appropriate choice. I'm sure this post helped you at that stage of your project, and I'm happy to have helped, but the earlier posts were more directly responsible for answering the question of the thread, and it's important for other searchers to find relevant answers when they go searching for similar problems. I'll reset it for you, and leave you to decide which post was most helpful in that light :-)
Feb 18 '12 #16
NeoPa has suggested the following, please help me out with this since you have written the code "module modGenYearlyData"


1) #3 takes you to the VBA IDE where you simply type in what you
would in the immediate pane every year

2) Otherwise a call to the procedure.

You see I am new to VB and I get lost easily.
If a form is created to call the procedure "Function modGenYearlyData", is it possible to enter a criteria for example: "Which year we are generating the Tx_Receipts for?". The contribution tabe will be filled with different years contributions and let's say we want the current year

Thanks again for your help.
Feb 19 '12 #17
NeoPa
32,173 Expert Mod 16PB
If you were to use the Immediate Pane every year to invoke your code, then you would type in the name of the procedure with any parameters that are required. To make this same procedure run in the same way, you simply put the calling code in the procedure which is created for you when you follow the instructions #1 to #3. Does that make more sense?

It's certainly possible to include a TextBox, or even ComboBox, control on your form to allow the operator to choose a year to make available to the procedure. this is another question though. I suggest you get the basic form working with the instructions as already explained and understood, then create a new question explaining what you already have and ask clearly for what you need with an extra control. The more clear the new question, the easier it is to fit the answer specifically to it (Thus, the better help you get).
Feb 20 '12 #18
Guys!

I took another approach to tackle this problem that I am still struggling to resolve.

I normalized my contibution table to add up all the types of contributions per member per year in a Query called Contrib_Query. This Query has the following recordset:

FamilyId
Year
Total_contibution
MemberName
Address
City_State
Postal code

Now the issue is to generate a yearly receipt to the member based on the above query. I can run a report and incorporate the above records in my report. The only remaining issue is to generate this auto number by calling a function GenYeardata(Year) from the Query, which will create a receipt number base on the year (sequential #). The year+000001, 000002, 0000003, etc…

I appreciate your time, please help.
May 28 '12 #19

Post your reply

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

Similar topics

1 post views Thread by Maurice Mertens | last post: by
3 posts views Thread by Maurice Mertens | last post: by
1 post views Thread by ChuckDubya | last post: by
2 posts views Thread by Elainie | last post: by
10 posts views Thread by Charles Richmond | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.