Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform.RecordSource

    I wish to limit the number of records shown in a subform based on an option group button selection. How do I do that? I have written the SQL string OK, but when I put in "Forms!frmSubmission!RecordSource = strSQL", I get an error message saying that the frmSubmission cannot be found.

    I am using A2000.

    TIA,

    kiwi44

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

    Re: Subform.RecordSource

    I take it that frmSubmission is your subform and you're trying to set the recordsource from the parent form, right? You'll get the message because the form is open as a subform. Try this instead:

    Me!frmSubmission.Form.Recordsource = strSQL
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform.RecordSource

    You are wonderful - fixed the syntax error in my SQL expression, and requeried the form, and it works like a charm.

    The user thinks I walk on water - and I don't want to disillusion them!

    Thanks again.

    kiwi44

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

    Re: Subform.RecordSource

    Glad to help. It's one of the basic rules never to disillusion a user who thinks you're brilliant. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform.RecordSource

    This thread seems to be the closest to a problem I've just encountered.

    I frequently set a subform's recordsource from its main form. I just created a new main form and subform in which I set the subform's recordsource in the Open event of the main form with the syntax me!subformcontrol.form.recordsource=stSQL. In initial testing thisseemed to be working. Now this statement is raising err 2455 "You entered an expression that has an invalid reference to the property Form/Report". In debug I can sucessfully test/see the control's properties with ?me!subform.propertyname. But If I try me!subform.form.name for instance I get the 2455 err. I've double checked and even copied and pasted the sub form control name to make sure it is correct. Hopefully I've just overlooked something really obvious, but should that not be the case any thoughts on how to go about debugging the problem? Thinking that somehow either the form/subform were corrupted, I've tried importing them into a new blank database, deleteing them for the original database, compressing the original database and importing them back in ... no luck.

    Thanks in advance for your insights and suggestions.
    Marty

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

    Re: Subform.RecordSource

    What version of Access are you using? The error message suggests that either the parent form (Me) or the subform (subformcontrol.form) isn't actually a form object or the object hasn't been populated yet. Is there a form in your subformcontrol? If not, you're going to have to set it before you can set the recordsource.
    Charlotte

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Subform.RecordSource

    Try shifting the code to the Load event. This occurs a bit later than Open event.
    Regards
    John



  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Subform.RecordSource

    You said you can see the subform control's properties in debug by using "?me!subform.propertyname". However, can you see the properties of the form in the control?

    Try something like: ?me!subform.form.name

    Does it work or do you get an error?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform.RecordSource

    No I can't. As soon as I add the .form and anything else I get the error. As I mentioned, I use this same syntax successfully in a number of other form/subforms in the application, so it seems to me to be something about this specific form/subform.

    Marty

  10. #10
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform.RecordSource

    John,

    Thanks for the suggestion. I tweaked the overall design and moved the code to the main form's on current event. Working fine now.
    Marty

  11. #11
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform.RecordSource

    Charlotte,

    I'm using Access 2007 in the accdb format. The subform control has a form. While I'm using the main form's open event to build the sql for the subform, I wasn't relying on any of the main's forms controls for values. I was passing the needed information to the mainform through it OpenArgs. I've since tweaked the design to created the sql in the main form's on current event. Now its working. Still a puzzle as I have other forms working fine even though they build the their subform's sql in their on open event.

    Marty

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

    Re: Subform.RecordSource

    The Current event fires repeatedly when the form loads and when you navigate through a recordset, which makes it a poor candidate for setting a subform recordsource. You would be better off moving the code to the Load event of the parent.
    Charlotte

  13. #13
    Star Lounger
    Join Date
    Apr 2006
    Location
    California
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform.RecordSource

    Thanks again. I'll move it.
    Marty

Posting Permissions

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