Results 1 to 4 of 4
  1. #1
    Evilferret is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    27

    Repopulating Table On Form_Open


    I have a table whose data needs to be reset each time one of my forms loads. The form contains a subform that contains data from the database table that's being reset.

    I've been trying to get it to reset in the Form_Open of the main form however it doesn't seem to affect the table - The old data remains there. Any ideas? I was wondering if it might be a timing issue, EG, the subform has already opened before the DELETE statement has executed?

    Code:
    Private Sub Form_Open(Cancel As Integer)
        CurrentDb.Execute "DELETE from Temp_Shipping"
        CurrentDb.Execute "INSERT INTO Temp_Shipping (PartID, Quantity, Anodized) SELECT PartID, 0, No FROM Parts"
        CurrentDb.Execute "UPDATE Temp_Shipping INNER JOIN Parts ON Parts.PartID=Temp_Shipping.PartID SET Temp_Shipping.Anodized=Parts.GetsAnodized;"
    End Sub
    EDIT: There seems to be something odd when using Form_Open and Form_Close. For instance, Form_Close events don't trigger if I right-click the form tab and select "Close" however they do trigger if I use DoCmd.Close acForm. Any suggestions?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,914
    Either put your code in the SubForm's events or Requery the SubFormControl at the end of this code.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Evilferret is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    27
    Requerying the subform fixed the problem...

    I don't fully understand why this occurs. I thought data was retrieved OnLoad, but I'm assuming it must occur OnOpen based on the results I just saw. What's the difference between OnLoad and OnOpen events?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,914
    Your problem was that a SubForm completely loads *before* the MainForm.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 03-22-2012, 08:48 AM
  2. Replies: 2
    Last Post: 08-01-2011, 11:35 PM
  3. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums