Auto fill a Query with an increment number

[Tony Liane]

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.

[NeoPa]

I don't believe that's possible without using code. Even the Make + Update approach cannot work, as you've found out.

[Mihail]

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.

[TheSmileyCoder]

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?

[Tony Liane]

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.

[NeoPa]

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.

[Mihail]

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 !

[NeoPa]

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.

[Tony Liane]

@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.

[TheSmileyCoder]

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:

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.

[Tony Liane]

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.

[TheSmileyCoder]

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.

Public Sub GenereateTestData()
    'Variables
        Dim lngMemberNo As Long
        Dim intRandom As Integer
        Dim intAmountType As Integer
        Dim intI As Integer
 
    'Open table contributions for data entry
        Dim rsContributions As DAO.Recordset
        Set rsContributions = CurrentDb.OpenRecordset("tbl_Contributions", dbOpenDynaset)
 
    With rsContributions
 
        'Loop through creating a random number (1-50) of contributions for each member
 
            For lngMemberNo = 1 To 100
                intRandom = Int(Rnd() * 50 + 1)
                For intI = 1 To intRandom
                    .AddNew
                        !FK_MemberNo = lngMemberNo
                        'Create random date between 2010-01-01 and 2012-12-31
                        !dt_Date = DateAdd("d", Int(Rnd() * 1095), #1/1/2010#)
                        intAmountType = Int(Rnd() * 4 + 1)
                        Select Case intAmountType
                            Case 1
                                !lng_Amount1 = Int(Rnd() * 1000 + 1)
                            Case 2
                                !lng_Amount2 = Int(Rnd() * 1000 + 1)
                            Case 3
                                !lng_Amount3 = Int(Rnd() * 1000 + 1)
                            Case 4
                                !lng_Amount4 = Int(Rnd() * 1000 + 1)
                        End Select
                    .Update
                Next intI
            Next lngMemberNo
    End With
 
'Cleanup
    Set rsContributions = Nothing
End Sub

Public Function GenerateYearlyData(lngYear As Long) As Integer
On Error GoTo err_Handler
    'First make some very basic error validation. You may or may not want to expand on this. Your call
        If lngYear < 2010 Or lngYear > 2030 Then
            MsgBox "The year entered [" & lngYear & "] is invalid. Valid data range is:" & _
                    "2010 to 2030"
            GenerateYearlyData = vbNo
            Exit Function
        End If
 
 
    'Check that no entries allready exist in tbl_YearlyReceipt for requested year, as that would indicate some sort of problem
        If DCount("*", "tbl_YearlyReceipt", "lng_Year=" & lngYear) > 0 Then
            MsgBox "The year requested [" & lngYear & "] allready exists in table"
            GenerateYearlyData = vbNo
            Exit Function
        End If
 
 
 
 
 
    'Encapsulate in transaction, in case error occurs
        Dim wks As DAO.Workspace
        Dim myDB As DAO.Database
        Set wks = DBEngine.Workspaces(0)
        Set myDB = CurrentDb
 
 
 
    'Now run query to sum up the yearly amount and transfer to tbl_YearlyReceipt
        Dim strSQL As String
        strSQL = "INSERT INTO tbl_YearlyReceipt ( FK_MemberNo, lng_Year, lng_SumAmount1, lng_SumAmount2, lng_SumAmount3, lng_SumAmount4 )" & _
                " 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 " & _
                " FROM tbl_Contributions " & _
                " WHERE (((tbl_Contributions.dt_Date) >= #1/1/" & lngYear & "# And (tbl_Contributions.dt_Date) < #1/1/" & lngYear + 1 & "#)) " & _
                " GROUP BY tbl_Contributions.FK_MemberNo, Year([dt_Date]); "
        wks.BeginTrans
            myDB.Execute strSQL, dbFailOnError
 
        'Now open recordset to add the receiptnumber
        Dim rsYearlyReceipt As DAO.Recordset
        Set rsYearlyReceipt = myDB.OpenRecordset("SELECT * FROM tbl_YearlyReceipt where lng_Year=" & lngYear, dbOpenDynaset)
 
        Dim lngRecieptNumber As Long
        lngRecieptNumber = 1
        Dim strRecipt As String
 
        Do While Not rsYearlyReceipt.EOF
            rsYearlyReceipt.Edit
                rsYearlyReceipt!tx_ReceiptNumber = lngYear & Format(lngRecieptNumber, "000000")
            rsYearlyReceipt.Update
            lngRecieptNumber = lngRecieptNumber + 1
            rsYearlyReceipt.MoveNext
        Loop
 
    'Succesfull
        wks.CommitTrans
        GenerateYearlyData = vbYes
 
cleanup:
    Set wks = Nothing
    Set myDB = Nothing
    Set rsYearlyReceipt = Nothing
 
 
    Exit Function
 
 
err_Handler:
    MsgBox "An error occured while trying to generate yearly recipts data" & vbNewLine & _
            Err.Number & " - " & Err.Description
    GenerateYearlyData = vbError
    If Not wks Is Nothing Then wks.Rollback
 
    Resume cleanup
End Function
 

I hope you find it understandable and usefull. Best of luck with your project.

[Tony Liane]

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.

[TheSmileyCoder]

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.

[NeoPa]

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 ;-)

[NeoPa]

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 :-)

[Tony Liane]

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.

[NeoPa]

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).

[Tony Liane]

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.