Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requery Form (2003)

    Two forms: frmCustomers & frmCustEdit

    Currently - after editing a record in frmCustEdit, it will return to frmCustomers (requeried to reflect the updated information), but will go to the first record. Any attempts to go to the edited record opens frmCustomers as a filtered form with the edited record ONLY .

    Desired - after editing a record in frmCustEdit, it should return to frmCustomers (requeried to reflect the updated information) and jump to the record that was edited while having all records available.

    CustomerID is the linked field.

    Any assistance would be greatly appreciated!

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

    Re: Requery Form (2003)

    That shouldn't be too hard. Can you post the relevant line or lines of code that you now use to return to frmCustomers?

  3. #3
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery Form (2003)

    The commented-out lines are the original code causing the form to go to the first record. The other code was an attempt to return to the desired record, but caused the form to be filtered.

    Private Sub cmdExit_Click()
    On Error GoTo Exit_cmdExit_Click

    strDocName = "frmCustomers"
    strLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"

    DoCmd.Close
    DoCmd.OpenForm strDocName

    DoCmd.GoToRecord , , acGoTo, strLinkCriteria

    'DoCmd.Close
    'Forms![frmCustomers].Requery
    'Forms![frmCustomers].SetFocus

    Exit_cmdExit_Click:
    Exit Sub

    End Sub

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

    Re: Requery Form (2003)

    Is frmCustomer still open (in the background) while frmCustEdit is open?

  5. #5
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery Form (2003)

    Yes. I originally thought it would be easier to close frmCustomer during editing, but eventually felt there would be no need as long as frmCustomer reflected the updated information when frmCustEdit was closed.

  6. #6
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery Form (2003)

    Do you have a CustomerID control (text box etc.) on frmCustomer? If so, here's what I would do:

    Private Sub cmdExit_Click()
    Forms("frmCustomers").requery
    Forms("frmCustomers").CustomerID.SetFocus
    DoCmd.FindRecord Me.CustomerID, acEntire, , , , acCurrent
    DoCmd.Close acForm, Me.Name
    End Sub

    Otherwise, let's wait and see what Hans suggests! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    (Edit: that code will also only work if frmCustEdit is not modal)
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Requery Form (2003)

    If the form is still open:
    <code>
    Private Sub cmdExit_Click()
    Dim strCriteria As String
    Dim rst As Object

    On Error GoTo Err_cmdExit_Click

    ' Set criteria before closing form
    strCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"
    DoCmd.Close
    ' Requery frmCustomers and go to record
    With Forms!frmCustomers
    .Requery
    Set rst = .RecordsetClone
    rst.FindFirst strCriteria
    .Bookmark = rst.Bookmark
    End With

    Exit_cmdExit_Click:
    Set rst = Nothing
    Exit Sub

    Err_cmdExit_Click:
    MsgBox Err.Description, vbExclamation
    Resume Exit_cmdExit_Click
    End Sub
    </code>
    The code is similar to that generated by the Combo Box Wizard if you select the option to search for a record.

  8. #8
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery Form (2003)

    Dave...

    When I originally added your code, it worked exactly as I was hoping. The problem ended up being that I originally had no intention of adding the CustomerID control to the form. (No use confusing the people using the form when the information is meaningless to them.) However, I then realized I could add the CustomerID control (text box), make it as small as possible, blend it in with the background and place it in a corner of the form. Voila!

    I then realized that the focus was not being returned to frmCustomers. I started to work on that when I noticed Hans' response.

    (To be continued in my response to Hans' response...)

  9. #9
    Lounger
    Join Date
    Nov 2003
    Location
    Scarsdale, New York, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery Form (2003)

    Hans...

    I used your code and, like Dave's code, I found the focus was not being returned to frmCustomers as I was hoping it would. However, I simply added Forms!frmcustomers.SetFocus immediately after DoCmd.Close and that finished the code.

    Dave...I decided to go with Hans' code since it didn't need to add a CustomerID control to the form.

    MUCH thanks to both of you!!!!

    Eric

Posting Permissions

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