Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Button with 2 functions (Access 97)

    Is there a way to get 1 button to do 2 things: close 'Form2' and go back to 'Form1' while keeping a specific record open? I have set up separate buttons to do these things - but I am finding that if 'Form2' stays open after going back to 'Form1', my combo box on 'Form1' for looking up records no longer works. I have a main details page with information about each employee, and sub-forms that open in a new window for Appointment details, Qualifications, Leave, etc. There is a combo box on the main details form for looking up employees. It periodically 'sticks' and won't look up whatever is selected. It seems to happen when one of the sub-forms is still open.

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

    Re: Button with 2 functions (Access 97)

    I'm not sure I understand. If you close Form2 (the "subform" in your terminology, I assume) is closed, don't you return automatically to Form1 (the main form)? If you make Form2 modal (set its Modal property in the Other tab of the Properties window to Yes), the user won't be able to select another form, (s)he will have to close Form2 first.

    Note: the term "subform" is used in Access for a form that is placed as a control on another form (the main form), not for a form that is opened in a separate window.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button with 2 functions (Access 97)

    Yes, apologies for using the term 'sub-form' - I couldn't think of another suitable term, that's why I said 'in another window' and called them Form1 and 2 to try and make it clearer.
    When Form2 is closed, Form1 seems to be reverting to the 'top' record (the form is based on a query that sorts alphabetically). I don't recall it doing this when I designed the database, but it sure does it now. If I use a button on Form2 to go back to Form1 and find the right record, Form2 stays open, and then the combobox look up field on Form1 no longer works.
    Have I made sense yet?
    Thanks

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

    Re: Button with 2 functions (Access 97)

    Do you have code behind Form2 that requeries Form1?

    Anyway, how do we know which is the "right record"?

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button with 2 functions (Access 97)

    The 'right record' is found by matching doctorID on one form with doctorID on the other.

    Here is the scenario:
    Find a doctor's main record form.
    Click on (say) the Leave Form button (it uses doctorID to find correct details).
    Leave Form opens with correct doctor listed.
    Make amendments
    Now either :
    - close form using a DoCmd.Close button ... main form reverts to record at top of alphabet, not the doctor I am currently working on
    OR
    - close form using a button with this code:

    stLinkCriteria = "[DoctorID]=" & Me![DoctorID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    to return to main form. This finds the record for the right doctor, but leaves the Leave Form open, and disables the combo lookup field on main form, so when I am ready to move on to another doctor, I can't.

    How am I doing? Thanks for your help!

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

    Re: Button with 2 functions (Access 97)

    (You haven't told us yet if and how the main form is being requeried.) Try this - cmdClose is the name of the command button on the popup form:

    Private Sub cmdClose_Click()
    Fim frm As Form
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    ' Force record to save (may not be necessary)
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    Set frm = Forms!frmMain ' Use name of main form here.
    Set rst = frm.RecordsetClone
    rst.FindFirst "DoctorID=" & Me.DoctorID
    If rst.NoMatch = True Then
    Beep
    Else
    frm.Bookmark = rst.Bookmark
    End If

    ExitHandler:
    On Error Resume Next
    Set rst = Nothing
    Set frm = Nothing
    DoCmd.Close acForm, Me.Name, acSaveNo
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button with 2 functions (Access 97)

    The main form is not re-queried when form2 is closed. (jeepers, I feel ignorant here)
    It's time to leave work now, so I'll have to continue this later at home...
    Thanks for your help. No doubt you'll be hearing from me again.
    Thanks again, this is such a great forum.

  8. #8
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button with 2 functions (Access 97)

    The code you suggested resulted in an error message that my form does not exist or is not open (It does exist and is open). ?


    Private Sub cmdClose_Click()
    Dim frm As Form
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler

    ' Force record to save (may not be necessary)
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    Set frm = Forms![SMO Details] ' Use name of main form here.
    Set rst = frm.RecordsetClone
    rst.FindFirst "DoctorID=" & Me.DoctorID
    If rst.NoMatch = True Then
    Beep
    Else
    frm.Bookmark = rst.Bookmark
    End If

    ExitHandler:
    On Error Resume Next
    Set rst = Nothing
    Set frm = Nothing
    DoCmd.Close acForm, Me.Name, acSaveNo
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

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

    Re: Button with 2 functions (Access 97)

    Temporarily make the line On Error GoTo ErrHandler into a comment by inserting an apostrophe ' in front of it. When you click the button now, you will be given a choice of End, Debug and Help. Choose Help. The code will pause, and the line on which the error occurs will be highlighted in the Visual Basic Editor. If, as I suspect, it is the line

    Set frm = Forms![SMO Details]

    it must mean that either the name [SMO Details] is misspelled (it must be the name of the form as it appears in the database window), or it is not open at that time.

  10. #10
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Button with 2 functions (Access 97)

    Hans, thank you. I have been able to take a good long look at it this morning, and found a DoCmd.Requery where I didn't expect it! Thanks for the clue that led to the solution! I am most grateful.

Posting Permissions

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