Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    7

    Unanswered: Datasheet to Open Child Form

    (I'm using Access 2003 on a Windows XP O/S.)

    I've gotta present an update on my database this week--possibly within the next 24 hours. A solution or guidance to this inquiry would really, really help! Any immediate assistance would be appreciated!

    I have a subform (sfrmProjects) in datasheet view. I'd like to double-click my datasheet records to open up a form (frmViewAllResources) in data entry mode. Every time I double-click on the project and open up this data entry form, it should generate a new subrecord for the double-clicked project.

    Then, once a resource is associated to a project, I would like the datasheet to show the "+" sign that means there's an associated record. Then, for each subdatasheet record, I'd like to be able to view (as read only) by double-clicking on it and edit by maybe shift+double-click.

    I found the following code, but it only provides a data entry copy of the childform if the parent record is completely empty. I would like it to do what I described above.

    Code:
    Private Sub FilterChildForm()
    
        If Me.NewRecord Then
            Forms![frmViewAllResources].DataEntry = True
        Else
            Forms![frmViewAllResources].Filter = "[FiscalYear] = " & """" & Me.[FiscalYear] & """" & " AND [BulkObligation] = " & """" & Me.[BulkObligation] & """" & " AND [Project] = " & """" & Me.[Project] & """"
            Forms![frmViewAllResources].FilterOn = True
        End If
        
    End Sub
    Someone in a thread on TheScripts (http://www.thescripts.com/forum/thread691533.html) was convinced to change the datasheet to a continuous form and use command buttons. However, the "+" sign feature on datasheets for associated records would be extremely useful for my clients, so, if this can be done with datasheets, that would be fantastic.

    Thanks!

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    So, you want the plus sign next to a record with more data, is that correct?

    It looks to me like the trick is the make a query (or table) of the records you want to have in your subform, use that as the control source of your subform/subreport control then go to you source object and insert a Subdatasheet. That will give you plus signs that you can click on the expand and see the data associated.

  3. #3
    Join Date
    Nov 2007
    Posts
    7
    Starkmann,

    Thank you so much for your reply. Yes, I want a subdatasheet within the datasheet. My problem is that I want the subdatasheet activated by a separate form altogether. After I double-click a record on the datasheet, I want a form (frmViewAllResources) to pop up for data entry. Then, once I fill in the frmViewAllResources, I want that new record to be reflected in the datasheet through the "+" sign feature.

    This is the setup:

    sfrmProjects > frmViewAllResources

    To elaborate, this is the problem:

    1) Double-click on a project in sfrmProjects (datasheet) and have frmViewAllResources not only open in data entry mode, but also copy the related fields (FiscalYear, BulkObligation, Project; these are already related in relationships). Ideally, this would be the key to relating the records and having the "+" sign show up in sfrmProjects. Only after the "+" sign shows up would I like to be able to double-click on the subdatasheet records to open up frmViewAllResources again, but this time as an edit or view window.

  4. #4
    Join Date
    Feb 2007
    Posts
    348
    so really, your question is, how do I create an OnDblClick event for a subform.
    I'm not sure of the answer but I'll bet someone knows.

  5. #5
    Join Date
    Nov 2007
    Posts
    7
    Essentially, yes, but I think it's quite complicated because of the fact that it's a datasheet.

  6. #6
    Join Date
    Nov 2007
    Posts
    7
    I hope this clarifies things a bit:

    Looking at the following linked images: When you open 1.jpg, look at the Project "Vehicles." I want to double-click on it to open 2.jpg. I want to fill in 2.jpg, close it, and then have the "+" appear next to Vehicles that represents that the datasheet has a subdatasheet record. It would be ideal if the datasheet in 1.jpg looked like 3.jpg, which are the underlying tables with the collapsible " +/- " signs indicated by the red circle.

    1.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/1.jpg
    2.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/2.jpg
    3.jpg: http://i235.photobucket.com/albums/ee140/BASSPU03/3.jpg

    So, I guess my first question is: How do I associate records from frmViewAllResources (2.jpg) to frmProjects (1.jpg) without the ability to just TAB between the two forms?

  7. #7
    Join Date
    Nov 2007
    Posts
    7
    Update:

    I got the forms linked successfully so that the concatenated PKs in sfrmProjects project into frmViewAllResources. As a subform, my datasheet (sfrmProjects) doesn't show the "+" sign to indicate subrecords. (There ARE indeed subrecords.)

    Someone suggested a workaround which manually inputs a "+" sign (not the built-in [+] sign box) in a textbox named txtPlus. However, it shows the "+" sign even if the subrecords are blank. Here's that person's explanation of the behavior both for their own workaround and the built-in [+] sign feature that Access automatically employs:

    To the best of my knowledge both table datasheets and form datasheets show [+] box when subdatasheet is present, no matter whether it contains records or not, only NewRecord line doesn't show [+] box.
    This is their code that helped me to link the forms successfully and utilize the "+" sign workaround:

    [frmProjects].RecordSource
    Code:
    SELECT tblProjects.*, IIf(IsNull(DLookUp("AutoNumber","tblViewAllResources","FiscalYear=" & nz(tblProjects.FiscalYear,0) & "AND BulkObligation='" & nz(tblProjects.BulkObligation,0) & "' AND Project='" & nz(tblProjects.Project,0) & "'")),NULL,"+") AS txtPlus FROM tblProjects;
    [txtPlus] is bound to the form's [txtPlus] textbox

    [frmProject]'s DblClick event handler


    Code:
    Private Sub Form_DblClick(Cancel As Integer)
        
        Dim strFilter As String
        
        With Me
            If .NewRecord Then Exit Sub
            strFilter = "FiscalYear=" & Nz(.FiscalYear, 0) & _
                " AND BulkObligation='" & Nz(.BulkObligation, 0) & _
                "' AND Project='" & Nz(.Project, 0) & "'"
            DoCmd.OpenForm "frmViewAllResources", acFormDS, , strFilter
        End With
        
        With Forms!frmViewAllresources
            !FiscalYear.DefaultValue = Me.FiscalYear
            !BulkObligation.DefaultValue = Me.BulkObligation
            !Project.DefaultValue = Me.Project
            .Modal = True
        End With
        
    End Sub
    Question: Is there a way to use the "automatic" [+] sign feature (expandable/collapsible box) that Access automatically employs (as opposed to this manual workaround), and, if so, can it be restricted to display only when the target subform (frmViewAllResources) is dirty?

  8. #8
    Join Date
    Nov 2007
    Posts
    7
    I'm sorry: I've spent most of this thread talking to myself. I figured it out. All it took to make a subdatasheet and get the "+" sign feature to activate next to each record when there's a subrecord was to actually create a subform in datasheet mode, enable the subdatasheet (subdatasheet=yes), and link it to the primary datasheet. I tried this so many times at first, but apparently I was doing it incorrectly.

    At least I found out how to correctly link separate forms through a similar thread on another forum.

    Thanks to all for your attention and to starkmann for your input.

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    hope it helped.

    So did you abandon opening the other forum in add mode and all that? I kinda liked that idea, I was looking forward to a solution cause I could see how it could work out.

  10. #10
    Join Date
    Nov 2007
    Posts
    7
    Hey, starkmann,

    I've made significant progress thanks to someone on another forum, but we're still working out some kinks. If you'd like to follow my progress, visit http://www.thescripts.com/forum/thread741110.html.

    Best wishes,
    BASSPU03

Posting Permissions

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