| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 52464 | 2004-12-19 22:35:00 | MS Access 2000 coding question | Tony (4941) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 305766 | 2004-12-19 22:35:00 | I have an application that administers membership info for a club. It has a table with annual-type data (president name etc), keyed on year. I want to click a button labelled "new year", and extract the latest year data, create a new record, increase the year, insert the extracted data as default and present the data for editing. I'm not sure of the best way to get the record with the highest year value and extract the data. I can see various what seem to me to be awkward and long-winded ways, but I suspect there is a more direct way that I can't see. I've done a lot of programming, but not a lot of Access VBA. Any help would be greatly appreciated. |
Tony (4941) | ||
| 305767 | 2004-12-20 08:10:00 | Hi Tony I think the easiest way of doing this (without using VB, it's not my forte) is to do it with 2 new queries. I can point you in the right direction based on the limited info you have provided but the computer I am on doesn't have Access so this is all from memory. If you create a new query, add the relevant table, add the fields you want onto the query (including the year), click View -> Totals, under the year field change the 'Total' value from 'Group By' to 'Max'. Save the query. This query should give you the last record from the table - you can check it by viewing the results of the query. Create another new query, based on the first query, make it an append query (from memory Click Query -> Append, choose the table where you want the new data to be added) add the fields you want to append (all of them I think), make sure the 'Append to' fields are all populated with the right names from the target table, change the 'Year' field to this : NewYear : [Year] + 1 {NB : I have assumed your field name is 'Year' - make sure you use your actual field name and also make sure it is still pointing to the year field in the target table} Save and run the query. This should work but if it doesn't have play around with the 2 queries until you get what you want. If you get stuck and you need an urgent reply (I may not be online given I am in the UK) you can always post your question on the Access form at www.mrexcel.com. Good luck, Andrew. |
andrew93 (249) | ||
| 305768 | 2004-12-20 08:36:00 | Thanks Andrew - I can see how that would work, but my instinct tells me there ought to be a better way! At least we are talking about a very small amount of data, and probably an annual operation, so efficiency is not an issue - it also means I am not going to spend huge amounts of time on it - especially as this is a freebie. :rolleyes: | Tony (4941) | ||
| 305769 | 2004-12-20 08:45:00 | Hi Tony In terms of 'a better way', if you are using a form driven database, then you can wrap up the 2 queries in a macro that runs both queries (in the correct sequence) when a button is pressed / clicked - such that it is very easy for the user to use and the set-up behind the scenes should stand the test of time. To make the append process automatic, I usually turn off the 'Echo' and 'Set Warnings' commands at the start of the macro and then turn them back on towards the end, once again making it very easy for the user. Andrew. |
andrew93 (249) | ||
| 305770 | 2004-12-20 09:57:00 | Yeah, that's pretty well what I expect to do - although I'll probably use VBA rather than a macro. | Tony (4941) | ||
| 1 | |||||