Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: RecordSource problem

    I'm trying to update a subform using record source. As you can see when I state:

    Forms![main]![sub].Form.RecordSource = "Select * from [query]"

    this only results in my subform having one record whereas the actual query has 507 records. Please could you tell me how to assign the data in the query to the subform,

    I've been trying to sort this out for hours. Any thoughts very much appreciated,

    Mike

    Dim dbs As Database
    Set dbs = CurrentDb
    Dim rst As Recordset

    Forms![main]![sub].Form.RecordSource = "Select * from [query]"
    Me.Requery
    Me.Refresh


    Set rst = dbs.OpenRecordset("Select * from [query]")
    Debug.Print rst.RecordCount & " Query Records" //outputs 507 records


    Set rst = Forms![a_f_contacts]![k_f_list].Form.RecordsetClone
    Debug.Print rst.RecordCount & " Records" //outputs 1 record

  2. #2
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Is the DefaultView property in your subform set to Continuous Forms or to Single Form ? Your spec for the RecordSource seems to be OK, so this seems the most obvious thing to look at first,

  3. #3
    Join Date
    Oct 2008
    Posts
    4

    DefaultView property is set to Continuous

    DefaultView property is set to Continuous. If I open the underlying query or open the subform separately I can see the correct data. Really appreciate your help,

    Mike

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I don't understand the recordsetclone bit
    traditionall a subform is a subset of data in a tbale, that subset is based on the selction of a record in the parent table. for example you may have an order in the parnt form, and the details of items ordered in the sub form. Access knows the linking column (in my example the order no) and requeries the sub from for you.

    if ou are in the unbound wold then you have to do a bit mor ework.

    perhaps you could either explain what you are trying to do or consider posting your db (suitably sanitised of confidential data, possibly only contaning the problem tables/forms etc, and compacted prior to posting

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Clear your link child and link master fields for the subform object?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Oct 2008
    Posts
    4
    Very confused now....

    I created a new database and main form, then copied just the troublesome parts to the new database. The search worked fine which seems to indicate the code is okay.

    The part of the system that has been causing problems consists of two subforms. A module, triggered by a command button, takes data from the first subform and populates the second subform. All is well until I assign a query (or table) to the main form's record source property. Then the search runs, but the subform is not updated. Not sure what the form's record source has to do with the updating of the subform, but this is what is causing the problem and I don't know why.

    Any ideas?

    Thanks again for you input,

    Mike

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I created a new database and main form, then copied just the troublesome parts to the new database. The search worked fine which seems to indicate the code is okay.
    Might it be corruption? Try creating a new database and import everything into it. See if the problem persists.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Oct 2008
    Posts
    4
    Yes the problem does persist with a new database.

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Are you able to upload a .zip of the DB?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think it's one of two things:

    The Link Child Fields and Link Master Fields properties are set which is restricting the records to something unexpected.

    or

    The subform is set to Single Form rather than Continuous Forms. You'd always get one record in this scenario, but able to navigate the 507 records using the subform navigation buttons.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Aug 2008
    Posts
    58
    Check the subform's Cycle property (this is found under the "Other" tab in the property sheet). Make sure it's set to "All Records" and not just "Current Record".

    J

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ Why? That will have no effect on how many records appear.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you are changing the recordsources for the mainform and subform, save these as queries. You would then populate your mainform's recordsource with something like this: Forms!MyMainForm.recordsoucre = "MyMainQuery". You subform's recordsource would be populated with something like this: Forms!MyMainForm!MySubForm.Recordsource = "MySubformQuery"
    Make sure in "MySubformQuery", it has criteria where the ID field = Forms!MyMainForm!IDField. Also make sure the linking ID field in the relational subform table is on the form but invisible and has a default value of: = Forms!MyMainForm!IDField.

    If you are just changing the subform on the main form (not any recordsources), in your code to populate the subform (the recordsource query is already set up in the subform WITH criteria = Forms!MyMainForm!IDField), you would enter syntax like this: me.MySubformName.SourceObject = "MySubformName" or (if calling externally) Forms!MyMainFormName!SubFormName.Sourceobject = "MySubformName". I find this method of telling it the subform name to put in versus trying to put in the recordsource is much easier.

    Again, MAKE sure your ID fields (ie autonumber for Mainform and linking integer field for subform) are on both forms (make invisible if you have to). But they need to be on the forms.

    If you have problems with a subform sometimes not "updating information" when you go to a new record on the main form. 1. Make sure there is criteria in the recordsource query of the subform (for the relational ID field) similiar to =Forms!MyMainForm!IDField. and 2. Try issueing a me.MySubformName.requery command in the OnCurrent event of the MainForm.

    Also check your tables directly to make sure the relational field in the sub-table is being/has been populated correctly with the ID value it's supposed to link to in the Main-table.
    Last edited by pkstormy; 10-29-08 at 02:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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