Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Exclamation Unanswered: Use command button to change recordsource in subform?

    I have some queries that run when I click the command buttons. But, they open in a new window. I want them to open up in the subform that I put in the Parent form. How can I get it to work so when I click on the command button and instead of a new window it will populate my subform?

    Thanks in advance

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    private sub myButton_Click()
    forms!motherForm!daughterForm.Recordsource = "SELECT blah FROM here;"
    end sub


    or (and my preferred way) make several subforms (subform1, subform2 etc etc) each does a different part of what you want, probably feeding off different queries, and they are all the same size.

    now find the name of the "hole" that your subform lives in (one click on the hole in the mother form and then properties/other/name): lets assume this comes out to be subFormHole

    then:

    private sub myButton_Click()
    me.subformhole.sourceobject = "subform1"
    end sub

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Posts
    21
    Here is my code below for the button. The Form is named Data_Scrub and the subform is named subform. I get the error message "Object doesnt support this property or method."

    Did I put the code in wrong or is it something with my SQL statement?

    Thanks again for your help



    Code:
    Private Sub Dups_First_Last_Click()
    On Error GoTo Err_Dups_First_Last_Click
    
    Forms!Data_Scrub!subform.RecordSource = "SELECT [Last Name], [First Name], [Registration ID], [Pre Disaster Address], [Pre Disaster Street], [Pre Town], [Pre Zip Code], [Phone Number1], [Phone Number2] FROM Current_Master WHERE [Last Name] In (SELECT [Last Name] FROM [Current_Master] As Tmp GROUP BY [Last Name],[First Name] HAVING Count(*)>1  And [First Name] = [Current_Master].[First Name])ORDER BY [Last Name], [First Name];"
    
    Exit_Dups_First_Last_Click:
        Exit Sub
    
    Err_Dups_First_Last_Click:
        MsgBox Err.Description
        Resume Exit_Dups_First_Last_Click
        
    End Sub

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    like i said, i use the .sourceobject switch rather than setting .recordsource but it ought to work!!

    are you certain the "hole" is properties/other/name: subform (this is nothing to do with the name of the .sourceobject, you need the name of the "hole" where the .sourcobject sits)

    is that query saved as an access query?

    does it run as an access query?

    if YES so far - try:
    me.nameOfTheHoleThatTheSubformSitsIn.recordsource = "nameOfTheSavedQuery"

    if still nogo, make a new form bound to the query and try the .sourceobject route. i'm sure you will prefer .sourceobject once you play with it.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Feb 2004
    Posts
    21
    Ok, I am missing something here. Its been quite awhile since I have used access.

    I have a form with 3 buttons. Each button ran a different query. Those queries opened in new windows.

    I didnt want it like that. I wanted it to show up on the same form. So I put a subform in.

    This is what I have atm for code:

    Code:
    Private Sub Dups_First_Last_Click()
    On Error GoTo Err_Dups_First_Last_Click
    
    Forms!Data_scrub!subform_list.recordsource = "Find Dups by Street/Address"
    
    Exit_Dups_First_Last_Click:
        Exit Sub
    
    Err_Dups_First_Last_Click:
        MsgBox Err.Description
        Resume Exit_Dups_First_Last_Click
        
    End Sub
    Find Dups by Street/Address is the name of the access query. With this setup when I click the button I get error message "Object doesnt support this property or method." I havent really used the .objectsource so I dont quite get where you are going with it.

    I think that is an accurate description of what I want to get accomplished. Whould you be able to include a little bit more detail for those of us that have been out of the playing field for a bit. Much Thanks!

  6. #6
    Join Date
    Mar 2004
    Location
    Huntington, IN
    Posts
    54
    I know this post is old, but it comes up first when searching for 'subform recordsource' and it really didn't answer the question rasised. The answer is to code it as follows:

    Forms!frmMainFormname!frmSubformname.Form.RecordSo urce = "Select..."

    The property "Form" must follow the subform name since access treats the subform like a control.
    Matt Wilcox
    Casters & Wheels at www.apollocaster.com
    www.apollocaster.com/store

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Going back to your original post, If you want to display the results of a query within a subform (on a main form), the trick is to set the SourceObject of the subform using a prefix as such: "Query." (ie. = "Query.myQueryName"). (note the . (period) between Query and the name of your query.)

    Ex:
    Forms!frmMain!frmMainSub.SourceObject = "Query." & Me!MyQueryName (where me!MyQueryName is your combobox or listbox of query names)
    or simply
    Forms!frmMain!frmMainSub.SourceObject = "Query.myQueryName"

    It works very nicely and the user will be able to navigate through the records in the query like excel. Here's a quick example if you're interested: http://www.dbforums.com/6390529-post84.html (it's got a few additional features as well).

    I use this to method to very quickly create new queries for the users without designing reports and I reuse the same mainform/subform and simply set the subform to whichever query name I want to. They can export the data if need be (it's in the example). I'd recommend setting the Recordset Type for the query from Dynaset to Snapshot if you're showing live data or it will be updateable (if it's an updateable type of query). I have it setup in the example so I only need to create a query called: "Export<something>" and it automatically shows on my 'reporting' type of form as a query the user can select from to then view the results or export as an excel, csv, or dbf file (it doesn't get any easier than that.)

    If your setting the recordsource for the subform (which I don't think is what you want per your initial post), that's different (and you'd need to setup all the fields in the subform so you'd then have to instead, set the sourceobject of the subform, not the recordsource, to the SUBFORM name you designed. You would need to design a subform for each of your queries. The .recordsource for subform's doesn't work when trying to set it externally off the subform (at least no way I've found to easily do it with .recordsource command no matter where I set it from unless the code is within the subform itself - ie. me.recordsource = ....)

    Also note: If you do set the sourceobject of the subform to a query, if the user adjusts the columns when viewing, it will prompt to save the query when the form is closed (to save the new query column widths which it will do - NOT to save the data!). If a user does save the query, if you then get into design view of that query, you'll have to drag down the bottom portion as it weirdly adjusts the bottom column designer to the top - just an MSAccess quirk. You'll see what I mean once you try it.
    Last edited by pkstormy; 02-01-10 at 23:09.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Mar 2012
    Posts
    7
    pkstormy you rule. This totally works!

  9. #9
    Join Date
    Apr 2013
    Posts
    9

    Wink

    Great stuff, it is the details that make the difference. THX

  10. #10
    Join Date
    Sep 2009
    Posts
    6
    PKstormy, thanks for the explanation. This was driving me nuts as I was using a query as my record source but coudnt find the correct syntax.

    Every post I have seen about this was referring it using recordsource and adding the "Query." helped too.

Posting Permissions

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