Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300

    "Close" on PopUp Form needs to update underlying form

    I have a popup form I use for adding and editing contacts. When I make a change and click the command button to close the form I want it to refresh the main form so the change will show.

    If I leave the record and return the change is there because of the relationship I set up, but I can't seem to get the record to refresh without leaving the record.

    I've tried using several refresh and requery macros on both the main and popup form (i.e. got focus, lost focus, after update, etc.)

    I have the code for the close button in the popup form as below but it to does not work.

    Can you give me direction on this?

    Private Sub command_close_form_Click()
    On Error GoTo Err_command_close_form_Click



    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
    Exit_command_close_form_Click:
    Exit Sub
    Err_command_close_form_Click:
    MsgBox Err.Description
    Resume Exit_command_close_form_Click

    Forms!Fm_Inventory.Refresh
    End Sub
    Last edited by Huddle; 02-17-2012 at 09:02 AM.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    You'd want Requery, to see new records. Does this help stay on the record?

    http://www.baldyweb.com/Requery.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300
    Requery in the code doesn't make it work. When I close the pop up it is on the record I was working with. If I leave that record and come back the changes take. It is just a matter of not wanting to leave the record to see the change.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    What exactly is your code with the attempt at using Requery? The code you posted earlier wouldn't work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300
    I just changed it from refresh to requery. I don't know code. I looked at your example but since I don't fully understand code it confused me.

    Is there a way to do it without having to use code? i.e. macro

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    Your previous code had the refresh down where it will never run. It should be right after the "Dirty" line (but changed to Requery). If you follow the code line by line, you'll see why it would never run.

    It can probably be done with a macro, but I don't use them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300
    That worked. Just to learn...what does Dirty refer to?

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    As an FYI, Access has context sensitive help. You can click on the word Dirty in your code and hit F1 for help on it. Basically that line is testing to see if the data in the form has been edited (a form with an uncommitted save is Dirty), and if so force a save (Dirty = False).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300
    I was trying to use your code to make it return to the record I was working on rather than the 1st record.

    I'm getting an "Syntax error (missing operator) in expression". Can you tell me what I'm missing?

    Private Sub command_close_form_Click()
    Dim rs As Object
    Dim IngBookmark As String
    On Error GoTo Err_command_close_form_Click
    'set a varible to the current record
    IngBookmark = Me.PK_GSA_Contact
    'requery the form
    Me.Requery
    'bring us back to the original record
    Set rs = Me.RecordsetClone
    rs.FindFirst "PK_GSA_Contact =" & IngBookmark
    Me.Bookmark = rs.Bookmark
    Exit_command_close_form_Click:
    Set rs = Nothing
    Exit Sub
    Err_command_close_form_Click:
    MsgBox Err.Description
    Resume Exit_command_close_form_Click

    End Sub

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    Wouldn't you want to requery the other form? I'd expect all references to "Me" to be replaced by "Forms!Fm_Inventory". What data type is PK_GSA_Contact?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300
    I changed the Me. to Forms!Fm_Inventory. Now I get a Application-defined or Object-defined error.

    PK_GSA_Contact is text

    Private Sub command_close_form_Click()
    Dim rs As Object
    Dim IngBookmark As String
    On Error GoTo Err_command_close_form_Click
    'set a varible to the current record
    IngBookmark = Forms!Fm_Inventory.PK_GSA_Contact
    'requery the form
    Forms!Fm_Inventory.Requery
    'bring us back to the original record
    Set rs = Forms!Fm_Inventory.RecordsetClone
    rs.FindFirst "PK_GSA_Contact =" & IngBookmark
    Forms!Fm_Inventory.Bookmark = rs.Bookmark
    Exit_command_close_form_Click:
    Set rs = Nothing
    Exit Sub
    Err_command_close_form_Click:
    MsgBox Err.Description
    Resume Exit_command_close_form_Click

    End Sub

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300
    I've attached a dumb down version of the db. I think I left in all the vital information to address this issue.

    The information we are dealing with is on the GSA tab of Fm_Inventory

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,285
    I think this is closer to what you want:

    Code:
      Dim rs                      As Object
      Dim strBookmark             As String
    
      On Error GoTo Err_command_close_form_Click
    
      'set a varible to the current record
      strBookmark = Forms!Fm_Inventory.Bldg_Number
      'requery the form
      Forms!Fm_Inventory.Requery
    
      'bring us back to the original record
      Set rs = Forms!Fm_Inventory.RecordsetClone
      rs.FindFirst "Bldg_Number ='" & strBookmark & "'"
      Forms!Fm_Inventory.Bookmark = rs.Bookmark
    
    Exit_command_close_form_Click:
      Set rs = Nothing
      DoCmd.Close acForm, Me.Name
      Exit Sub
    
    Err_command_close_form_Click:
      MsgBox Err.Description
      Resume Exit_command_close_form_Click
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    Huddle is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jun 2010
    Posts
    300
    I had to add the Me.Dirty line in but I think it works OK. I'll run some more tests just to make sure. Thanks for your help.

    Private Sub command_close_form_Click()
    Dim rs As Object
    Dim strBookmark As String
    On Error GoTo Err_command_close_form_Click
    If Me.Dirty Then Me.Dirty = False

    'set a varible to the current record
    strBookmark = Forms!Fm_Inventory.Bldg_Number
    'requery the form
    Forms!Fm_Inventory.Requery
    'bring us back to the original record
    Set rs = Forms!Fm_Inventory.RecordsetClone
    rs.FindFirst "Bldg_Number ='" & strBookmark & "'"
    Forms!Fm_Inventory.Bookmark = rs.Bookmark
    Exit_command_close_form_Click:
    Set rs = Nothing
    DoCmd.Close acForm, Me.Name
    Exit Sub
    Err_command_close_form_Click:
    MsgBox Err.Description
    Resume Exit_command_close_form_Click

    End Sub

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

Similar Threads

  1. Replies: 7
    Last Post: 01-29-2012, 07:44 AM
  2. Update query from form "invalid use of null"
    By Lady_Jane in forum Programming
    Replies: 4
    Last Post: 08-16-2011, 01:37 PM
  3. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  4. Replies: 4
    Last Post: 01-06-2011, 10:52 AM
  5. "Close form" action does not release table
    By TFisher in forum Programming
    Replies: 7
    Last Post: 07-22-2010, 06:33 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