Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Drill down' (XP, 2000)

    I have a form with tabs that displays client information. One of the tabs contains a subform showing engagements associated with that client. This subform displays those engagement in datasheet view with only a few columns to describe the engagement. Is it possible to somehow select a particular engagement and have it automatically open another form that will display detailed information about that particular engagement?

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    This sub used as an event procedure in the Double Click event of the EngagementID field of the Engagements subform will open open an engagements Form which has the complete information. You will need to adapt it to appropriate field and form names. It grabs the ID of the field you double clicked on and matches to the ID of the record you want to look at.

    Private Sub EngagementID_DblClick(Cancel As Integer)
    Dim intID As Integer
    ' Open Engagments form from Clients form
    intID = Me!EngagementID
    If Len(intID) > 0 Then
    DoCmd.OpenForm "frmEngagements", , , "EngagementID=" & Me!EngagementID
    End If
    End Sub

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

    Re: 'Drill down' (XP, 2000)

    Do you reallyt want to "automatically open another form"? It would mean that the other form would pop up the moment the user clicked in an engagement. That would make it rahter difficult to edit the engagement.

    Since you are using a subform in datasheet view, you could use the On DblClick event of the subform to open the other form; this event occurs when the user double clicks the record selector on the left hand side of a record (of course. the Record Selectors property of the subform must be set to Yes for this to work). Alternatively. you could use a command button on the main form (you can't display a command button on a form in datasheet view.)

    The code behind the On DblClick event of the subform or the On Click event of the command button would use DoCmd.OpenForm to open the other form, and specify the WhereCondition argument to filter it. You will need to supply a unique identifier for the engagement. Something like:

    DoCmd.OpenForm FormName:="frmEngagements", WhereCondition:="EngagementID = " & Me.EngagementID

    from the subform or

    DoCmd.OpenForm FormName:="frmEngagements", WhereCondition:="EngagementID = " & Me.sbfEngagements.Form.EngagementID

    from the main form

    Replace the names used in these examples by the ones you use.

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    You're right - I think I do not want it to pop up. I will try the double click on the record selector. thank you.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    Well, I tried that - I probably did not do the code correctly but here is what I entered
    --
    DoCmd.OpenForm FormName:="jobs", WhereCondition:="job_number = " & Me.job_number
    --
    The result was that Access did not recognize the "DoCmd.OpenForm" See the pop up window.
    Attached Images Attached Images

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

    Re: 'Drill down' (XP, 2000)

    Where did you put the DoCmd.OpenForm instruction? It should be in an event procedure. The following assumes that you tried the method I proposed.
    <UL><LI>Open the subform in design view.
    <LI>Activate the Event tab of the properties window.
    <LI>Click in the On Dbl Click event.
    <LI>Select Event Procedure from the dropdown list in this event.
    <LI>Click the builder button (the three dots to the right of the dropdown arrow)
    <LI>Make the code look like this:

    Private Sub Form_DblClick(Cancel As Integer)
    DoCmd.OpenForm FormName:="jobs", WhereCondition:="job_number = " & Me.job_number
    End Sub[/list]If you still get the same error message, there must be something wrong with code elsewhere in the database. Check Tools | References... to see if there are missing references.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    Thanks Hans, I did it incorrectly - I entered the code directly into the doublecklick property. I should have known better - it looked like a procedure.

    When i did it correctly, it almost worked except that I receive a run-time error '2501' and it cancellec the OpenForm action. any ideas? The code was typed correctly, I checked many times.

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

    Re: 'Drill down' (XP, 2000)

    What kind of field is job_number? I assumed that it is numeric; if it is text, you should adapt the code slightly:

    DoCmd.OpenForm FormName:="jobs", WhereCondition:="job_number = " & Chr(34) & Me.job_number & Chr(34)

    If that doesn't help either, try removing the where-condition temporaril

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    Sorry, I should have mentioned that the field was text. In any case, I changed the code as you suggested and it did work. However, the form it opend (jobs) contains some subforms and I expected them to be populated with data but they were not. When I open the jobs forms normally (manually or through the switchboard), the subforms display the data correctly.

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

    Re: 'Drill down' (XP, 2000)

    You are opening the "jobs" form with a filter, to restrict it to one job_number. If there are no subrecords for that specific job_number, the subforms will remain empty. Otherwise I can't explain it; there is no a priori reason that opening a form using DoCmd.OpenForm would fail to populate subforms.

  11. #11
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    You are quite right again, Hans. My mistake. It does work correctly.

  12. #12
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    Another question. I have decided that instead of opening another form, I would prefer to have the subform I am using to display the search results be part of the main form on which I want to display the record information on my double click choice. In other words, the main form would already be open and a subform would display the search results. When I double click on on of the records shown, I would like it to populate the mainform. Do I need to modify the code you suggested?

    Also, i am not clear about your choices of double clicking the record in the subform or using a command button. Do you mean to use the command button after having selected a record instead of simply double clicking the record?

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

    Re: 'Drill down' (XP, 2000)

    If you want to display the details in the same form/subform combo, there is no need for double clicking or command buttons. You can have the detail info appear automatically as the user moves from record to record. The easiest way is to create a second subform for the detail info.

    Put a text box on the main form with control source =[Subformname]![SubformID] where Subformname is the name of the subform as a control on the main form, and SubformID is the name of a field that uniquely identifies the current record in the subform. Set the Visible property of the text box to No, the user doesn't need to see it. Use the name of this text box as Link Master Field for the second subform.

  14. #14
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Drill down' (XP, 2000)

    Not sure if I posed my question correctly, let me try again. I probably have a clumsy design but I have a main form (prospect) with a tab control that contains two subforms (formA, formB). I want to add another page to the tab control to contain a search form used to find prospects based on wildcard searches and it will contain a search results subform that will display the search results. Then, of the results displayed, be able to choose one that will cause the main form (prospect) to display the relevant data for that prospect

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

    Re: 'Drill down' (XP, 2000)

    In that case, the subform with search results should contain a field that uniquely identifies the record you want to locate on the main form, say ID (substitute the correct name in the following code. You can set an On Dbl Click procedure for the subform. It will be invoked when the user double clicks the record selector.

    Private Sub Form_DblClick(Cancel As Integer)
    Dim rs As Object
    Set rs = Me.Parent.Recordset.Clone
    rs.FindFirst "[ID] = " & Me.[ID]
    If Not rs.EOF Then Me.Parent.Bookmark = rs.Bookmark
    Set rs = Nothing
    End Sub

    This assumes that the ID field is numeric. If it is text, use

    rs.FindFirst "[ID] = " & Chr(34) & Me.[ID] & Chr(34)

Page 1 of 2 12 LastLast

Posting Permissions

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