Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Updating records in subform

    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.

    Any thoughts on a simple way to do this?

    Pete Townsend
    PGT

  2. #2
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    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;

    Code:
    SqlStr = "UPDATE Table2 SET Table2.[RecordDate] = DateAdd(""yyyy"",1,[Table2]![RecordDate]) 
    WHERE (((Year([Table2]![RecordDate]))=Year(Now())) 
    AND ((Table2.ClientID)=[Forms]![Form1]![ClientID]));"
    
    DoCmd****nSQL (SqlStr)
    That final line should say DoCmd dot RUNSQL but Db forums is asterixing it for me...

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Thanx Christyxo,

    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.
    Pete T.
    PGT

  4. #4
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    To be honest I pretty much cheat when writing SQL. I use Access' query builder to form the basis and then tweak the SQL at the end.

    "UPDATE Table2 SET Table2.[RecordDate] = [Table2]![RecordDate] + 1
    WHERE ((([Table2]![RecordDate])=Year(Now()))
    AND ((Table2.ClientID)=[Forms]![Form1]![ClientID]));"

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •