Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2005
    Posts
    44

    Unanswered: Requery combo box on subform based on value on main form

    Hey guys! I'm creating a database to show which modules degree students are completing as part of there degree.

    Each student can only complete one degree therefore the degree information is shown on the main form.

    I then wish to show which modules they are taking and have done so using a continous sub form.

    I have no problem with showing which student is taking which module but what i would really like to do is have a drop down box which allows me to select modules only relevant to a particular degree. I've written a query which allows me to filter out which modules are linked to a degree, but if the person changes the degree they are on, I Cant get the module drop down box to change to reflect the newly selected degree. what i basically need to do is requery the combo box on the subform to reflect changes in a combo on the main form...... I know how to do this is the info is based on the same form, but its not liking it on 2 different forms

    I've read some where that in order to requery on a subform based info on a main form I have to write a requery statement in Visual Basic I havent written anything in VB in years - so I havent got a clue where to start with this..... can any one help?????????

  2. #2
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    13
    Hey netherton,

    If all your wanting to do is requery the cboBox on the subform after making a choice in the cboBox on the main form, then one way to do that would be to use the AfterUpdate event of the cboBox on the Main form and put this:

    Forms![MainFormName]![cboNameOnMainForm]![subFormName].Form.Requery

    or

    Me.[subFormName].Form.Requery

    HTH,
    Shane

  3. #3
    Join Date
    Apr 2005
    Posts
    44
    I wish i could say that worked, but its still not liking it.

    where abouts should i be writing it, in properties, event, then afterupdate box? or do i have to invoke on of the editor things

  4. #4
    Join Date
    Feb 2006
    Posts
    56
    a couple of questions. Do you have your sub-form and main form linked with an appropriate indexed field? (Since this is a 1:m relationship). You could also in the after update event set the focus to the sub-form and requery. Hope this helps.

  5. #5
    Join Date
    Apr 2005
    Posts
    44
    Yes. The linked indexed field is that of degree id. which is firstly used to uniquely identify a degree and secondly used to link a module to a degree.

    I can get the combo box to refresh after closing and re-opening to reflect a new degree being selected, but i want it to do this on updating the degree. I currently have a macro to update a date field which shows the dates the degree could possibly run on. could it be its failing because there are two events in the afterupdate field?????

    thanks for all your help so far guys!

  6. #6
    Join Date
    Feb 2006
    Posts
    56
    it depends on how you have your code structured for that event.

    when you say updating the degree, are you updating the values in the sub-form or the main form?

    first do this. comment out all of the code (don't delete it, )
    and try this in the after update event. for your combo box. If I understand your issue correctly, this should take care of it.

    change for your control name and field name appropriately.

    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[SomeID] = " & Me![UsrCombo]
    Me.Bookmark = Me.RecordsetClone.Bookmark

    let me know if this works. if not, we will try a different approach.

    You are saving your modifications before initiating the requery of the combo box correct?

  7. #7
    Join Date
    Apr 2005
    Posts
    44
    Ok, I'll start from the begining. I have created a form which has a series of tabs to show details relevant to a particular student i.e the first tab shows the students details such as name, address etc, i then have a tab which shows all the training a student has completed and finally a tab to show which degree a student is on followed by a table of the students modules.

    On the degree tab, i want the user to be able to select the degree the student is on therefore i have placed a combo box to list all available degrees (combo_degree) on the main form of the tab. I then have a combo box for dates this degree is available on - so when a degree is selected in combo_degree the possible start dates for that degree are shown in combo_dates.

    I then wish to detail all modules that a student is enrolled on which i have done using a subform called subform_modules. This is basically a table of modules which a particular student is on. So what i would like is to be able to use a combo box on subform_modules which allows the user only to select modules linked to the degree selected on the main form in combo_degree. Therefore if a student changes the degree they are on, the combo on the sub form reflects this change.

    When creating the previous AfterUpdate procedures I have used the macro builder as my knowledge of VB is very very poor and the requerying has taken place using combos in the same location - either both on a main form or both on a subform.

    I am unsure where about I should put the code you suggest - should this be in VB/code builder, expression builder or directly in properties under AfterUpdate.

    I can get it to filter out the modules according to degree, but not update the possible module choices if the degree is changed. I can only get it to work with data that is already present.

    I tried shaneS suggestion but it always said there was a runtime error and i am sure i typed the combo names and form names in correctly. grrrrrrrrrl

  8. #8
    Join Date
    Feb 2006
    Posts
    56

    Smile

    ok, thanks for the background. Can you post what your macro actions are and what you are exactly doing for the date formatting?

    For my example, you would click on code builder and past the code there.

    BUT- let's wait on that. What I would like to do is to get the information about the macro first so that I can combine what I sent you and implement the code to format the date all in the same module.

    I appreciate the information.

  9. #9
    Join Date
    Apr 2005
    Posts
    44
    i've attached a very dodgey looking data model - i've just changed pcs and havent got all my software yet so i threw something together in paint

    the dates are just short dates stored in the degree_start_year table which when a degree is selected the date combo on the form is updated to reflect what dates the degree could possibly run on - its basically to signify whether its a 2004 entry or a 2005 entry etc so for example if degree1 runs in 2004, 2005 & 2008 i would be able to select the relevant entry date for my students.

    all i did for that was create a macro on the degree_title field which i did by selecting event in properties then AfterUpdate -> then in macro builder select requery and typed in the name of the combo box i wanted to requery. so i dont know which code you mean as i havent written any
    Attached Thumbnails Attached Thumbnails db.bmp  

  10. #10
    Join Date
    Dec 2005
    Location
    Texas
    Posts
    13
    Quote Originally Posted by netherton
    I am unsure where about I should put the code you suggest - should this be in VB/code builder, expression builder or directly in properties under AfterUpdate.

    I can get it to filter out the modules according to degree, but not update the possible module choices if the degree is changed. I can only get it to work with data that is already present.

    I tried shaneS suggestion but it always said there was a runtime error and i am sure i typed the combo names and form names in correctly. grrrrrrrrrl
    Hey netherton,

    So where did you post the code I provided? Did you go to the 1st cboBox properties and click on the Event tab and then go to the AfterUpdate event and choose Event Procedure, then put the code I provided in the forms module? This is where that code would need to be and as long as your refering to the form, subform and control names correctly, it should work. You do have you two cboBoxes linked to each other, right?

    Shane

  11. #11
    Join Date
    Apr 2005
    Posts
    44
    wahooooooooooo i've done it!!!!! thank you guys so much!!!!!! you are little stars!

    what i did in the end was write the code in AfterUpdate event, then event procedure as suggested by Shane, but amended it slightly as follows:

    forms![main_frm_name]![sub_frm_name].form![combo_on_sub_frm].requery


    ...... now all i have to do is work out how to have two update procedures on one combo box!

  12. #12
    Join Date
    Apr 2005
    Posts
    44
    ok, i've got that bit working fine and can do two procedures on one combo box........ but how I want to be able to flick through the records and the combo on the subform to change according to the data that has already been entered. As at the moment i can only get it to change once the combo on the main form has been updated

Posting Permissions

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