Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autopopulate Subform based on parent (2000/2002)

    I have a form (and eventually many forms) to track Preventive Maintenance on a machine (eventually many machines). I want the user to select the Frequency (daily, weekly, monthly, yearly) from a combo box, and have a list of Tasks auto-populate in a continuous Subform. This list of tasks shall be editable by a select few to allow for changes to the PM routine for that machine and frequency, but allow previously entered PM checks to remain unchanged.

    The subform will list the task description and have a Completed field to be filled out by the Operator. I have the following Tables:

    tblTask (TaskKey, Description)
    tblMill_Tasks (Key, Frequency, TaskKey)
    tblMill_PM (PMNumber, Operator, Date, Frequency)
    tblMill_PMSub (Key, PMNumber, TaskKey)

    How do I autopopulate the Subform (tblMill_PMSub)with all of the TaskKey's based on the mainform!Frquency (tblMill_PM!Frequency)? Can I do this with a query? Code on the afterUpdate event of the Frequency Combo Box?

    Suggestions, Idea's, Gentle nudges, all appreciated!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autopopulate Subform based on parent (2000/2002)

    This sort of thing can be done in a couple of ways. One is to execute a query from code triggered by the appropriate event for the combo box. In that case it's a fairly simple set of VBA, but the query you run may be more involved, or may need to be a series of queries if you need to append to multiple tables (your table structure suggests it shouldn't need to). You can use stored queries, in which case you specify the name in either a RunSQL or an Execute command, or you can use a SQL String stored in the VBA procedure and invoke it with either command.

    The other alternative is to use DAO or ADO, and actually append the records using the Insert command. There is more to learn if you choose to do it that way and are not familiar with DAO or ADO, but it gives you more flexibility in deciding what values to use for the fields and to do some conditional tests that are pretty complicated (or impossible) in SQL.

    Finally, I don't understand where you are going to store the completed date - it seems to me each task should have a completed date.
    Wendell

  3. #3
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate Subform based on parent (2000/2002)

    Thank you Wendell.

    As for the completed date, just one date will be stored on the parent form - this is by design.

    I am having trouble writing a query, but would prefer to do it in code anyway as I love to learn new VBA tricks. Can you get me started on some code using this insert command? Thnaks!

  4. #4
    Star Lounger
    Join Date
    Jul 2003
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autopopulate Subform based on parent (2000/2002)

    Thanks for the resourses!

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autopopulate Subform based on parent (2000/2002)

    <P ID="edit" class=small>(Edited by WendellB on 27-Jan-04 15:45. fix some errant html that sneaked into the message)</P>
    <hr>The subform will list the task description and have a Completed field to be filled out by the Operator<hr>
    This was what triggered my comment - from it I presumed each task would have a completed date, but you are carrying the completed date in a different table.

    To do the autopopulate stuff in VBA, you first have to decide whether to use ADO or DAO - if you were running 2000 I'd suggest DAO, but since you are on 2002 (I presume by your Version), it's about a toss-up. In the long run, ADO will probably become the standard, and is applicable across a wider range of products (i.e. ASP.net and VB.net) so it might be the right choice for you. The topic is really longer than I can put into a post, so let me point you to a couple of references.
    For ADO - <!mskb=301987>Microsoft Knowledge Base Article 301987<!/mskb>
    For DAO - <!mskb=142938>Microsoft Knowledge Base Article 142938<!/mskb> (Not a great resource, as MS is pushing developers to ADO - the Help files give you a few ideas as well - try searching the VBA help for "AddNew" and you should find help pages for both DAO and ADO that show how to insert a new record.)
    To really dig into it requires a book - "Beginning Access 2000 VBA" by Smith & Sussman would be my first choice, though you can find good stuff in Alison Balter's books on Access, and in the Access Developer's Handbook. Hope this helps.
    Wendell

Posting Permissions

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