Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71

    Filter subForm comboBox based on main form combobox selection - SAMPLE ATTACHED


    Here is my problem. I am trying to filter a combobox on a subform based on a combobox on my main form. I am successfully able to do this with two combo boxes on the same mainform but not with one on the mainform and one on the subform. I have attached a sample database with the tables, queries, and forms associated with this issue.

    I would greatly appreciate some help on this issue. I have read numerous topics on this site relating to this topic and I have tried all the responses but I just keeping getting lost in the middle of the code.

    Thank you in advance to all takers.

    Sean

  2. #2
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Ok, I am getting somewhere but the finish line is still not in sight. I have added the following code to the mainform combo box 'cboDivSelect' AfterUpdate.

    With Me.subFrm_LessonCardLessons.Form.cboLessonSelect
    .RowSource = "SELECT [TblLessonList].[LessonID],[tblLessonList].[LessonName] FROM TblLessonList WHERE DivID = " & Me.cboDivSelect.Value
    .Requery
    End With

    Now it partially works. By that I mean if I have a class that is already in the database and I want to add another lesson to it, I have to re-click on the DivID combobox and reselect the division that is already displayed in order to get the subform 'cboLessonSelect' combobox to repopulate with the correct records. And if I go to the next class(record) I have to do the same thing.

    I think I am missing some Requery function but I am not sure where to put it.

    I have attached an update version. Have at it.

    If the attachment doesn't download, here is a direct link:

    http://www.mediafire.com/?x38yeh50qs3g9sx

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I will have to admit that this issue sure looks familiar.

  4. #4
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Quote Originally Posted by RuralGuy View Post
    I will have to admit that this issue sure looks familiar.
    RG, I need all the help I can get on this one. I must admit though that you and one other person are the only ones who have responded to my post. So quodo's to you.

    Sean.

    P.S. problem still not solved.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Did you get some sleep? As I said in the other forum, the problem is not that complicated. I think it can all be done with the LinkMaster/ChildFields properties but I need to understand the problem better. I'll study your latest description of what you need.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    BTW, this site has no trouble with your uploaded zip files. Just thought you'd like to know.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    See if this is closer to what you wanted.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    OK...did you go take a nap?

  9. #9
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    I am back; had to grade some tests. I am looking at your update now.

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Ah...Haa. Now we know.

  11. #11
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Quote Originally Posted by RuralGuy View Post
    See if this is closer to what you wanted.
    Still not updating the subform combo on main form combo when the form is loaded and when I move to the next record in the form.

    Here is what I get.

    1. When I open the form frm_LessonCard the first record is assigned to the 'Naval Expeditionary Operations Division' but If I go directly to the subform without touching anything in the main form, all the lessons belong to the 'Professional Studies Division'.

    2. Now if I click on the Division combo box in the main form and "reselect" 'Naval Expeditionary Operations' the lessons in the subform now are filtered to only show those that belong to the 'Naval Expeditionary Operations Division'. This is what I want to see when the form is initially opened without having to first reselect the division.

    It seems like there needs to some on load and requery function when the main form is initially opened.

    3. Additionally, if I move to the next record without touching anything on the main form and select a lesson from the subform, the lessons are still displaying 'Professional Studies ....' whereas the division of the second record is 'MAGTF Operations Ashore'.

    In this case, it seems like there should be a requery function when I change records.

  12. #12
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    The way it is set up now works perfect if all I am doing is added new records. But if I need to update lessons for records already in the database, I would have to make sure that I first reselect the division for each record before I add more lessons.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Too easy. Try it now.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You also have a problem in that you can add the same lesson more than once.

  15. #15
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    will try right now. As for the lessons, different classes can teach to the same lesson so yes the lesson can be added more than once to multiple classes but not to the same class. BRB

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populate a field based on combobox selection
    By rscott7706 in forum Access
    Replies: 5
    Last Post: 06-02-2011, 03:18 PM
  2. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  3. Replies: 0
    Last Post: 12-16-2009, 01:14 PM
  4. Replies: 7
    Last Post: 05-24-2009, 10:24 AM
  5. Replies: 0
    Last Post: 04-17-2008, 09:24 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums