Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Sep 2011
    Posts
    14

    Question Unanswered: Displaying Records in a Subform

    Hi,

    I am new to the forum, and have had a little scout around to see if my problem has already been solved. The closest I can see is this link http://www.dbforums.com/microsoft-ac...rm-report.html which was a thread by TCPhD, answered by CyberLynx.

    The thread has examples, but when I try to download them, I get a message that the zip files are corrupted.

    I have a main form with a subform. The subform contains records which have the same job code as the main form. Each record in the subform is created by a specific user, and I want to use a drop down box in the main form to select a user, and then display all records in the subform that the user has entered. I also want an option in the list to show All records, so the user can see entries from other users.

    Has anybody done this, or can anybody help?

    Any help much appreciated.

    NoviceBoy

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create a new form that will become the subform (you can use the wizard, if you're not used to working with Access), select the fields you want to see in it and save it (let's call it SF_Jobs).

    2. Create a new form that will become the parent (main) form and arrange it the way you want (Wizard O.K.).

    3. Add a SubForm/SubReport control to it (let's call it Child_Jobs). If you use the wizard, left the LinkChildFields and the LinkMasterFields blank or remove their values afterwards. For the SourceObject, enter the name of the subform (SF_Jobs).

    4. Add a ComboBox to it (let's call it Cbo_Users) but don't use the wizard. Assign this to its RowSource property (adapt the names to those in your database):
    Code:
    SELECT  DISTINCT user FROM Tbl_Job ORDER BY user
    UNION
    SELECT ' All' FROM Tbl_Job
    Notice the space in front of ' All'.


    5. Create an Event Handler for the AfterUpdate event of the ComboBox, like this:
    Code:
    Private Sub Cbo_Users_AfterUpdate()
    
        If Me.Cbo_Users.Value = " All" Then
            Me.Child_Jobs.Form.Filter = ""
            Me.Child_Jobs.Form.FilterOn = False
        Else
            Me.Child_Jobs.Form.Filter = "user = '" & Me.Cbo_Users.Value & "'"
            Me.Child_Jobs.Form.FilterOn = True
        End If
        
    End Sub

    6. Save the form ( (let's call it Frm_Jobs).

    That's it.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    14
    Thanks Sinndho, I'll try this when I get in tomorrow.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    14

    Filtering Sub Form

    Hi Sinndho,

    I've tried what you suggested and got a bit lost, so I've decided to try and simplify things until I can build my skills up. I've looked at some other responses of yours and think this one may be what I'm looking for http://www.dbforums.com/microsoft-ac...g-subform.html with the following code which works.

    Me.frmAMDisbursement.Form.Filter = "frmAMDisbursement.acType = '" & "Matter" & "'"
    Me.frmAMDisbursement.Form.FilterOn = True

    My code is as follows,

    Private Sub Form_Load()

    Me.ADC_REC_Add_Final.Form.Filter = "ADC_REC_Add_Final.Details = '" & "*REC*" & "'"
    Me.ADC_REC_Add_Final.Form.FilterOn = True

    End Sub

    Where ADC_REC_Add_Final is the name of the subform and Details is the name of the field I am trying to filter with all records with the string REC in it.

    Apologies if I haven't explained this right, beginning to pull my hair out!

    Many thanks
    Last edited by NoviceBoy; 09-13-11 at 11:22. Reason: Edit form name

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Glad you could find a solution. You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Sep 2011
    Posts
    14

    Filtering Sub Form

    Hi Sinndho,

    Don't know if you read this right, but it still doesn't work. Do you know if my code is ok?

    Sorry to be a pain.

  8. #8
    Join Date
    Sep 2011
    Posts
    14

    Filtering Sub Form

    Hi Sinndho,

    The error message I am receiving is Compile Error : Method Or Data Member Not Found

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is ADC_REC_Add_Final the name of the subform, the name of the control of type SubForm/SubReport, or both?
    Have a nice day!

  10. #10
    Join Date
    Sep 2011
    Posts
    14

    Filtering Sub Form

    Thanks Sinndho

    The Subform is called ADC_REC_Add_Final
    The field Control Source is called Details
    The name of the field in the subform is also called Details
    The main form is called Site Details REC

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Right! But on the main form there is a control of type SubForm/SubReport that "contains" the subform. Its name can be different from the name of the subform. What is it?
    Have a nice day!

  12. #12
    Join Date
    Sep 2011
    Posts
    14

    Filtering Sub Form

    Sorry, I misunderstood.

    Please see the attached. The name of the _control_ of type SubForm/SubReport is ADC_REC_Add_Financial

    I've attached a snap shot so you can see it.
    Attached Thumbnails Attached Thumbnails SubReport.jpg  

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then it should be:
    Code:
    Me.ADC_REC_Add_Financial.Form.Filter = "ADC_REC_Add_Final.Details = '" & "*REC*" & "'"
    Me.ADC_REC_Add_Financial.Form.FilterOn = True
    The syntax is: Me.Control.Form.Control, with Me being a reference to the Form (or the Report) where the code is being excecuted. The Form object is a property of the control, not of itself, if I may say so.

    Moreover, I'm not sure that ADC_REC_Add_Final is needed in ADC_REC_Add_Final.Details, it depends on the subform RecordSource.
    Have a nice day!

  14. #14
    Join Date
    Sep 2011
    Posts
    14

    Filtering Sub Form

    Ok, I'm definitely getting closer. The code is in the "On Load" Event. Now when I open the main form, the subform has no records in it. Then when I go to save and exit, a message comes up with

    Enter Parameter Value
    ADC_REC_Add_Final.Details

    When I click Ok the main form closes.

    Am I in the right Event?

  15. #15
    Join Date
    Sep 2011
    Posts
    14

    Filtering Sub Form

    Still trying and have now got to the point where it executes the code and returns

    Syntax error in string in query expression 'ADC_REC_Add_Final.Details = '*REC*'

    Too many apostrophies?

    Current code

    Me.ADC_REC_Add_Financial.Form.Filter = "ADC_REC_Add_Final.Details = '" & "*REC*"
    Me.ADC_REC_Add_Financial.Form.FilterOn = True
    Last edited by NoviceBoy; 09-13-11 at 16:52. Reason: Spelling

Posting Permissions

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