locked
Issue with "disappearing" form/ subform RRS feed

  • Question

  • I have a complex form that Hans has helped me with various parts of, so I'm hoping he's on today. That said, I am of course open to and grateful for suggestions from anyone.

    Description: The form has a parent form and a subform which represent a one-to-many relationship. The parent form shows basic demographics and contact information for students; the subform shows that student's service plan. A student can have multiple service plans, but only one service plan can be active at a time. Whether or not a plan is active does not affect the plan's visibility, it only affects which service plan appears by default when the user browses to that student.

    There are several ways a user can get to a specific plan: there is a search window in the parent form's header; there are navigation buttons, and there is a duplication window. In the last case, if the user tries to enter a student that already exists, when s/he types in the first three letters of the student's last name, if there are already existing students that match those letters, a window will pop up asking if the student is one of these. If the user selects a student in the pop-up and clicks the appropriate button, the main form will move to the selected record.

    Additionally, I have activated Access's default navigation controls until I solve this problem.

    The problem: If a student is entered in the parent form, but no information is entered in the subform (which is unusual but possible, if a service plan is pending), when the record is saved and closed, the student record then becomes becomes inaccessible when the form is opened again. The record exists in the student table, but cannot be viewed using this form.

    If the first three letters of the last name are entered, as described above, the pop-up window appears, showing the "missing" record. But if the user selects that student and clicks the appropriate button, all that appears is a blank form.

    Bottom line: if data is not entered in the Service Plan, the data in the Student Info become inaaccesible.

    It's a mystery to me. ANyone have any ideas?


    —nick

    Friday, June 8, 2012 6:14 PM

Answers

  • I can't say for sure without seeing the recordsource of the main form, but it sounds like that form's recordsource is a query that contains an inner join between the student record and the service plan record.  If that is the case, then the query will simply not return a record for any student who doesn't have a service plan.

    What is the recordsoure of the main form?  If it's a stored query, what is the SQL of that query?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Nick Vittum Monday, June 11, 2012 8:37 PM
    Monday, June 11, 2012 6:25 PM

All replies

  • Can you include code in the cbo that if the SubForm is Null, then popup Form to enter the info in the Table. Then use the OnClose Event of the popup Form to refresh the Parent Form?

    Chris Ward

    Friday, June 8, 2012 6:51 PM
  • What I don't understand is why, if the student record exists in the table, it should be invisible in the parent form if there is no record in the Service Plans table (subform). Is this likely to be a setting I've made inadvertantly, or is it the way parent form/subforms work?

    —nick

    Monday, June 11, 2012 5:38 PM
  • I'll start by saying I don't know as I am not familar enough with what you have. As a test to make sure this is the problem can you make a copy of the db with the Access controls returned to your preferred distribution settings. Then add a checkbox field (yes/no) to the Table for the SubForm and on the subform add the checkbox titled something like Pending. Then create a student that has no info in the subform but then select pending. could this be a solution for you. It should make the student visible as it gives it a record in the subform, however this may affect when you don't want a student to show which I don't have information on the criteria for that.

    Chris Ward

    Monday, June 11, 2012 6:10 PM
  • I can't say for sure without seeing the recordsource of the main form, but it sounds like that form's recordsource is a query that contains an inner join between the student record and the service plan record.  If that is the case, then the query will simply not return a record for any student who doesn't have a service plan.

    What is the recordsoure of the main form?  If it's a stored query, what is the SQL of that query?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Nick Vittum Monday, June 11, 2012 8:37 PM
    Monday, June 11, 2012 6:25 PM
  • Thanks!

    Sorry for the slow response, I had someone in my office who. . .   talks. . .

    You are right. Building this database has been my Intro to Access 101, and this form was created fairly early on in that process. At that time, I had no idea that forms even used queries as Record Sources.

    SO yes, there is indeed an Inner Join in the sourse of the parent form, which is what I think you were saying (i.e., not the subform). The FROM line reads like this:

    FROM Students INNER JOIN ServicePlans ON Students.StudentID = ServicePlans.StudentID;

    There is only one bit of data in the query that relates to the ServicePlans table, and I'm not sure why it's there (though I obviously must have put it there) or that it serves any purpose.

    I will experiment with it— but what I'm wondering is if the FROM line is neccesary as it is in order to key the information in the subform to the student on the parent form?


    —nick

    Monday, June 11, 2012 8:08 PM
  • Thanks, Chris

    I think Dirk is onto something (below) with his quetion about an Inner Join. However, if this turns out to be true and there's no way to get rid of that join, then your soultion sounds viable. I'm going to do some experimenting now that I have a direction to look, and will get back. . .


    —nick

    PS: As it turns out, there was an Inner Join just where Dirk predicted, and I was able to delete it, and all works perfectly now. But I thank you for your work-around idea. Had I been unable to avoid the join for some reason, I'm sure this would have worked. 
    Monday, June 11, 2012 8:11 PM
  • SO yes, there is indeed an Inner Join in the sourse of the parent form, which is what I think you were saying (i.e., not the subform). The FROM line reads like this:

    FROM Students INNER JOIN ServicePlans ON Students.StudentID = ServicePlans.StudentID;

    There is only one bit of data in the query that relates to the ServicePlans table, and I'm not sure why it's there (though I obviously must have put it there) or that it serves any purpose.

    That inner join would be the culprit.  Seeing as how you have a subform based on ServicePlans, it seems unlikely that you ought to be joining to that table in the main form's recordsource, although there could be some exceptions to that assertion.  Most likely you don't need to include ServicePlans in the query at all, but if you do, you could use a left join instead:

        SELECT ...
        FROM Students LEFT JOIN ServicePlans ON Students.StudentID = ServicePlans.StudentID;

    That would let you pick up the data from ServicePlans if you need it, while still letting a student record appear that has no service plan.

    Note, though, that if a student could have multiple service plans (all but one inactive), a recordsource query that joins the tables will return multiple records for the same student -- one record for each associated service plan.  You would have to add a WHERE clause to filter out all but the active service plan;  something like:

        WHERE ServicePlans.IsActivePlan = True Or ServicePlans.IsActivePlan Is Null

    The above clause is based on the (probably false) assumption that you have a boolean (yes/no field) named "IsActivePlan" in ServicePlans that indicates whether this record is the student's active plan.  The actual representation of this information could be entirely different, of course, so the above WHERE clause was just to show the idea.

    Simplest is if the main form doesn't need any information from ServicePlans at all, so that data is confined to the subform.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, June 11, 2012 8:24 PM
  • That Inner Join was indeed the culprit.

    It was created inadvertantly by the fact that early in the design stages, before I was informed that students could have more than one service plan, all information had existed in a single table, accessed by a single form, with no subform. When I'd realized my error and made the split, one field that should have been only on the subform also remained on the parent form. I had long ago deleted it from the parent form, but it remained "vestigially" in the query.

    I deleted that, and deleted the Inner Join, and Presto! it all works as it  should. Thanks Dirk!  the voice of experience.


    —nick

    Monday, June 11, 2012 8:37 PM