For all general questions relating to Excel but not including VBA or formulas. Login or Sign Up Log in with; Search in titles only Search in Excel. (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code. Automatically Increment Cell Once Per Day VB is not needed. Put =TODAY()-39570 in. Autoincrement a single cell on a template - Hi all. Uses this named range); You need to change the path / file name information for the text file.
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 01 if member no 1 makes no contributions that year. This simple statement should work as part of your query. ReceiptNo: Year(dtContributionDate) & Format(lngMemberNo, '0000000') Where dtContributionDate 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: 8 If the above is not acceptable, please let me know, and I can whip up some code for you.
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:.
tblContributions 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). tblYearlyReciept 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:.
qryTemplate 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. 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 tblYearReceipt 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.
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:. Create form (from scratch will do). Add a single CommandButton control. Set the OnClick property to 'Event Procedure'. #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;-). 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:-). 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 TxReceipts 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.
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). 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 ContribQuery. This Query has the following recordset: FamilyId Year Totalcontibution MemberName Address CityState 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.
. PLEASE FULLY READ THE SIDE-BAR BEFORE SUBMITTING A POST! SUBMISSION GUIDELINES FAQ PROBLEM SOLVED? OPs can reply to any solutions with: This will award the user a and change the flair to SOLVED. TO APPLY CODE FORMATTING Use 4 spaces to start each line You can select code in your VBA window, press Tab, then copy and paste into your thread or comment. PROVIDE YOUR DATA! Include a or use the website to generate the reddit table markup from your spreadsheet.
Recent ClippyPoint Milestones! Congratulations and thank you to these contributors Date User CP 25 10 10 10 600 50 600 10 50 600 10 10 A community since March 11, 2009 Download the official to convert Excel cells into a reddit table.