Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    requery form after update (2000)

    I have a form that is based on a query. On the form is a combo box that will find the record in the combo box. That all works fine. If I want to change the information in any of the fields, I cannot make a change probably because the query has several tables. So I created a form based on one of the tables. I have a button on the first form that will bring up the second form with that record filled in, I can make my changes and when I close the form, I have an on close event that requeries the first form. When the form is requeried, it brings up the first record of the query, not the record I was working on. How do I show the record on the first form with the changes without having to go to the combo box and select the record again? Thank you for your help

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: requery form after update (2000)

    You could use the Refresh method of the form instead of the requery method.

    A query based on multiple tables is not necessarily read-only. If you have joined the tables correctly and if there is a unique key on the field on the "one" side of the join, the query is often updateable (there are exceptions - totals queries are never updateable, for example).
    Another option might be to use a mainform based on one table, with one or more subforms based on other tables.

  4. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: requery form after update (2000)

    I had tried to use the refresh method to refresh the first form (forms.frm1stform.refresh) in the on close event of the second form. It shows the record that I was working on but it does not show the change. Should the refresh method be put some where else?

    The first form was updatable till I added a query to the query for the first form ( I now have 3 tables and a query). I tried setting the properties of the added query to unique records yes but the form still is not updateable.

    We don't have to make changes very often so I would hate to add a subform that we would have to look at all the time.

    Thank you for your help.

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: requery form after update (2000)

    If Refresh doesn't work, you may have to run the code to find the record in the first form again after executing Requery.

    Without seeing the database it's impossible to say why the query is not updateable and whether this could be changed.

  6. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: requery form after update (2000)

    I have attached a stripped down version to see what is possible to change to either make the form updatable or when the second form closes, the first form will show the record with the updated information.
    Thanks
    Attached Files Attached Files

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: requery form after update (2000)

    Your query is read-only because the query it is based on is a totals query.

    You can use the following code for the On Close event of the second form:
    <code>
    Private Sub Form_Close()
    Forms!frmInventoryItem.Requery
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Forms!frmInventoryItem.Recordset.Clone
    rs.FindFirst "[JN] = '" & Me.JN & "'"
    Forms!frmInventoryItem.Bookmark = rs.Bookmark
    End Sub</code>

  8. #7
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: requery form after update (2000)

    Perfect Hans.
    Thank you for your help and thank you for explaining why the query is not updateable.
    John

Posting Permissions

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