Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Event From a subform (97)

    A new challenge.
    I have three forms. A main form with a subform (frmMain), the subform (sfrmMain), and separate form that contains a single memo field (frmNotes). The plan was to double click on a field in sfrmMain, and have that open frmNotes. IF I open sfrmMain by itself, all works great. The frmNotes form pops open, and shows the notes for the relevant record on the (sub)form.

    However, if I open the main form (frmMain), which includes sfrmMain as a subform, and double click I get a "Enter Paramater Value" for the field used as the key/link between sfrmMain and frmNotes.

    But when running just the form, not as a subform of the main form, it works correctly. Is there a workaround for this?

    Thanks in advance for your ideas and help.

    Ken

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

    Re: Event From a subform (97)

    It probably has to do with the way you refer to the control on the subform sfrmMain. When you open it by itself, you can refer to a text box txtLink as Forms!sfrmMain!txtLink.

    However, if you open the main form, the subform is not part of the Forms collection; you must refer to it through the main form: Forms!frmMain!sfrmMain!txtLink or Forms!frmMain!sfrmMain.Form!txtLink. Here, you must use the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window.

    Of course, you must use the name of your "link" control instead of txtLink. If this doesn't help, post back with more details (how do you open frmNotes; what is the record source of frmNotes?)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Event From a subform (97)

    Thanks Hans. The following is the code I used in the Event of the control.

    Private Sub txtTermDate_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmNotes"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

    Record source for form frmMain is tblPerson, which includes an Employee ID field that is used as the parent.
    Record source for subform sfrmMain is tblRehire, which includes an Employee ID field that is used as the child.
    Record source for frmNotes is tblRehire, which includes a primary key that acts as the link to sfrmRehire.

    sfrmMain contains three visible controls (txtTermDate, txtReason, chkRehire) and on hidden control (pkey).
    frmNotes contains two controls, memNotes (visible) and pkey (hidden)
    tblRehire contains the following fields:
    pkey
    fk_EmployeeID
    dtmHireDate
    dtmTermDate
    strReason
    logRehire
    memRemarks

    Does this help?

    Thaniks,
    Ken

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

    Re: Event From a subform (97)

    Hi Ken,

    It is not clear from your description how frmNotes "knows" which record to display. You don't set a value for stLinkCriteria in txtTermDate_DblClick, and the record source for frmNotes is a table, not a query with criteria.

    Does frmNotes have code to select a record or records, for example in the On Load or On Open event?

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Event From a subform (97)

    Hans,
    I'm sorry about the lack of clarity.
    frmNotes "knows" which record to display based on the query that feeds the form:
    SELECT tblRehire.pkey, tblRehire.memRemarks
    FROM tblRehire
    WHERE (((tblRehire.pkey)=[Forms]![sfrmMain]![txtpkey]));

    In other words, when they double click on the control on the record displayed in the subform I use the txtpkey to relate to the appropriate record in tblRehire. Does that make sense?

    Again, I apologize for the lack of information.

    Thanks,
    Ken

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Event From a subform (97)

    In Access 97, you have to include the Form reference in order to be sure you are pointing at the subform's control rather than the subform control on the parent form.

    WHERE (((tblRehire.pkey)=[Forms]![sfrmMain].[Form]![txtpkey]))

    However, if the form you are popping up is already open, you may wind up with unexpected results unless you force the form to requery. It would probably be faster and cleaner to open the form directly from the dblClick event using DoCmd.OpenForm and pass in a WhereCondition to specify the where clause. Then you could simply base the popup form on tblRehire instead of having to force a requery on it to get the condition to be recognized.
    Charlotte

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

    Re: Event From a subform (97)

    I was confused because in your previous reply, you wrote that the record source for frmNotes was tblRehire. Now it turns out to be a query instead.

    Charlotte has already pointed out that it's more elegant to put the where condition in the On DblClick code that opens frmNotes:

    Private Sub txtTermDate_DblClick(Cancel As Integer)
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmNotes"
    stLinkCriteria = "pkey=" & Me.[sfrmMain].[Form]![txtpkey]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End Sub

    In that case, the Record Source of frmNotes should be tblRehire instead of the query.

    If you prefer not to adapt the code, and to keep the query as record source of frmNotes, the WHERE part should be

    WHERE (((tblRehire.pkey)=[Forms]![frmMain]![sfrmMain].[Form]![txtpkey]));

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Event From a subform (97)

    Charlotte and Hans,

    THANKS! ! !
    I tried it both ways and it worked! You guys are awesome.

    Charlotte,
    RE: "However, if the form you are popping up is already open"
    No. The form can only be opened from the double click event, and when open is modal forcing user to close to notes form before going anywhere else. There may be caveats to that approach, but I don't want the user to have the ability to have a bunch of forms, report previews, etc. open at one time.

    THANKS for sharing your brilliance.

    Ken

Posting Permissions

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