Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65

    Query update after new record is entered

    I have a form that displays the most recent records in order to assist the user entering data to help keep from double entering data. However, currently the query data that is displayed doesn't update until you close the form and reopen it.

    I am wondering how to get the query results to update when a new record is entered on this same form. I have tried but using
    Code:
    Me.[ControlSource].Requery
    However, when I run this sub using the AfterUpdate event it comes back with an error. I think it is because the control source I am trying to use is not on the main form and is on the subform I need to update.

    Any suggestions?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What process did you use to add the new record?

  3. #3
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    The new record is just enter in through text boxes on the form. The user can just hit enter or go to the next record using the built in nav buttons

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    In AfterUpdate on MyForm:

    Forms![MyForm].Requery

  5. #5
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I tried that and it came back saying it couldn't find the field that I was referencing. I double checked to make sure I spelt everything correctly.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    It's not a field, you are requery the whole form after a record is added or updated. "MyForm" = the name of your form. Post your code again.

  7. #7
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I typed exactly what you initially posted, except changed myform to the name of the subform that I am trying to update. I typed it in the AfterUpdate event, I tried it as a Macro and also as a subroutine in the actual VBA code

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If it is a subform, syntax is different as you have to reference the main form, then subform. Something like:

    Forms![MainForm]![SumForm].Requery

    If needed to get syntax right, go to a query, or create a new one, in the Criteria row on a column, right click and hit Build, double click Forms, double click Loaded Forms, double click your Main form, select your SubForm then double click <form> from middle column. This will give you the syntax to use.

  9. #9
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I made sure everything was correct and it is still not working. don't know if I am not putting it in the right place. If I was putting it in a sub routine for the form in the VBA code it should look like this:
    Code:
    Private Sub Form_AfterUpdate()
    Me.Forms![MainForm]![SumForm].Requery
    End Sub
    Is this correct?

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    How you write the .requery depends on where that code is, and what form/subform you want to requery, and there are three possibilities.

    1. If you want to requery the same form the code is in, whether it is the main form or the subform, use:

    Me.requery (it's the same for both)

    2. If the code is in the subform, and you want to requery the main form use:

    Me.parent.requery

    3. If the code is in the main form, and you want to requery the subform, it gets a little bit more complicated, because you cannot refer to the subform by the form name; you have to use the name of the subform control on the main form:

    me!subformcontrolname.form.requery

    Replace subformcontrolname with the actual name of the control on your main form.

    Note: you cannot use Forms!subformname.requery either, because the subform is not a part of the Forms! collection.

  11. #11
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Spot on John_G.

    Again if you enter data on MainForm and want data in SubForm to update, in the AfterUpdate on MainForm put your requery line.

    Forms![MainForm]![SubForm].Requery

  12. #12
    Radtastic10 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    65
    I appreciate all the help. However, I have tried all of these suggestions to no avail. When I test it out by entering a new record it goes into the debugger and highlights the requery line. I have typed it in and double checked everything. Just doesn't seem to like it.

    The form itself works, everything is updated every time it is opened. I might just leave it like that as I can't see the need for the users to have to enter multiple things on a given day.

  13. #13
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Try this Rad, Open your Main form in Design. Now Open a new query or existing one. In one of the data columns in the Criteria box, right click and hit Build, double Click Forms, double click Loaded Forms, double click your Main form, select or click the subform, then in the middle list box double click on <form>. This will show you the right syntax to reference the subform. So copy that code and paste into your MainForm AfterUpdate window and add .Requery at the end and that should give you the right syntax to do that requery.

  14. #14
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Can you post the code line that is causing the error (and indicate which sub it is in), and the error message you are getting?

    It might be an easy fix.

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    There's enough helpers so I'll just watch for now.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data Entered into Form to update on Table
    By aussiegrl in forum Forms
    Replies: 2
    Last Post: 02-22-2016, 03:27 PM
  2. Replies: 3
    Last Post: 10-04-2015, 10:17 AM
  3. Replies: 3
    Last Post: 01-09-2015, 12:24 AM
  4. Replies: 14
    Last Post: 08-12-2014, 06:33 AM
  5. Replies: 2
    Last Post: 11-30-2013, 02:53 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