Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Unanswered: Subform will not display data

    Hello again, Colleagues All,

    I am being driven mad by a silly problem in an Access form/subform structure and I hope some of you gurus out there can point me to where I have no doubt overlooked a simple and obvious setting of some kind.

    I have put a couple of screen grabs and some notes in a Word document which I attach hereto.

    Hoping to hear from someone soon (the work is quite urgent).

    Regards

    Jim Wright.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Briefly looking through your documentation and experience I have with subforms relating to the mainforms, besides using the linked child/master property, I'll also make the recordsource query of the subform so that the ID field equals that of the ID on the main form. I usually don't rely on just the link child/master property to do the linking. So my subform might have a recordsource to something like = Select * from myTable where IDField = " & Forms!MyMainForm!IDField & "". (then of course I'll set the default of the ID field on the subform = Forms!MyMainForm!IDField.) Not sure if you're doing this already but it's something to consider verses utilizing the filter (I've had problems using the filter especially when wierd characters are in a certain field for data.) I usualy avoid using the filter command and instead use a select query. Also sometimes a refresh on the main form before setting the sourceobject of the subform or requerying the subform helps. I'll try to look at your document more thoroughly but this is something I could write quickly which may or may not help you.
    Last edited by pkstormy; 03-01-08 at 21:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    Thank you for your insightful remarks. As I said in my notes, I was mystified by the fact that the identical process worked elsewhere in the project. Your suggestion should work very well in my context. The main idea of the form/subform architecture is to capture the relevant foreign keys automatically. In my case, however, the subform fields are created by an INSERT statement initiated by the double-click event in the list box. All of the key fields are mentioned explicitly in the SQL. Even if, in the end, I can maintain my original process (which I would prefer to do in the interests of consistent maintenance by others hereafter), what you have suggested could be the basis of a very useful diagnostic tool. I will incorporate it into the current screen and see what happens. Thanks again. Jim Wright.

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    Subform will not display data

    Hello again, Colleagues All,
    This is sooooo embarassing. I discovered that the key field which provides the master link to the subform, did not in fact have the Control Source set - hence no selection. I should explain that the various primary and foreiegn (numeric) keys, which were of no use to the user, were hidden in non-visible fields, usually below other fields and therefore it was not obvious (though I should have thought to go looking for such a simplistic cause ! ). Having said that, there are a couple of useful lessons to be learnt. First of all, when cutting and pasting controls,etc. in Access forms, be prepared for various properties to be reset to defaults. In other words, after the transfer - CHECK EVERYTHING. MS logic is not always of this world. Secondly, colleague pkstormy made a couple of suggestions of considerable merit. In particular, he addressed the situation where the subform only requires a subset of all of the records in the child table. I was filtering out the unwanted records by setting the Filter property in the subform, whereas he suggested including the filter in a query which would actually become the control source. The advantage of his suggestion is that when the form is opened with the filtering keys already known, the subform is immediately filtered and it is not necessary to perform a requery. The only time you need the requery would be if the filter was changed dynamically by user operations within the form. Where the filter is unchanging, then his suggestion is probably more robust.
    So you see, colleagues all, even silly errors can lead to useful bits of knowledge !
    Regards,
    Jim Wright.

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for the update Jim.
    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
  •