After being off for 5 months, I'm back with what I hope is a quick question with a simple answer.
Is there a way of accessing the parent-child linkage of a masterform/subform?
I have a form with a client's name and demographics. On that form is a subform that displays 2 fields (a function and the last year authorized) from a linkage table. The two are linked by the client's ID number.
On the main form I want to add a command button that will select any records of the subform that have the current 4-digit year, and update that year to the next year.
I could write a routine to perform an update query for any record in the linkage table matching the client's ID and where the year is the current year. But it seems logical that the engine is already displaying records matching the client's ID, and it might be possible to loop down through those 20 or less records rather than work with the 4000-5000 linkage records for 300+ clients.
Here I am assuming that the last year authorised is stored as a date. I.e. 01/01/2011 rather than 2011.
This looks up all the ClientID's within Table2, that match the field called [ClientID] on the open form [Form1], and where the year of [RecordDate] is equal to the year of now().
Then it updates those records by adding 1 year, onto the RecordDate...
If this isn't what you want, please advise.
So, behind the button's onclick event;
SqlStr = "UPDATE Table2 SET Table2.[RecordDate] = DateAdd(""yyyy"",1,[Table2]![RecordDate])
That final line should say DoCmd dot RUNSQL but Db forums is asterixing it for me...
I've not done much writing of SQL and hadn't thought of the Update function. Sounds like a winner, and a great learning opportunity.
The Year is actually just 4 characters, not a date, so its a simplification of your code that should be easy. Sounds like the only thing I'll need to add is a refresh of the form and sub-form, so the new data is displayed when the routine completes. The tables are indexed so the WHERE clauses should evaluate fairly fast.
This is not a high-priority task, so I'll give it a try as soon as I can get back to it.