Results 1 to 4 of 4
  1. #1
    Donno-45 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Location
    Perth Western Australia
    Posts
    2

    Filtering a combo with a combo in a subform doesn't work


    Hi. My first visit here
    I am trying to develop a database to assist with management of job numbers Each job number has at least one (or more) tasks associated with it. The tasks consist of two fields - Category and Description which are contained in a table.

    I have set up a form which uses two combo boxes to read each field. The Category field has multiple occurrences and a SELECT DISTINCT statement is used in that combo to display a unique list. This works OK. A requery is coded for the afterupdate event: Description01Cmb.Requery

    I have then used a query to select the Description field list based on the selected value of the Category field. The code is:
    SELECT Task_Category_TBL.Description, Task_Category_TBL.Category FROM Task_Category_TBL WHERE (((Task_Category_TBL.Category)=Forms![Task_Register01_FRM]!Category01Cmb));

    That Description field is passed to the Description combo via SELECT [Task_Register_QRY].[Description] FROM Task_Register_QRY;

    When the above is set in a single form everything operates correctly. When it is set up as a subform the Description combo does not populate!

    Hope that is clear enough. I was going to upload the db, but it is too big

    Any ideas please?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    by the sound of it both your combos are now on the subform in which case you need to reference it via the mainform>subform control>. You may find this link helpful http://access.mvps.org/access/forms/frm0031.htm

    Or you can put the sql as the rowsource (rather than reference a query) like this

    SELECT Task_Category_TBL.Description, Task_Category_TBL.Category FROM Task_Category_TBL WHERE Task_Category_TBL.Category=[Category01Cmb]

    in either event, you will need some code in the Category01Cmb after update event to requery the description control

  3. #3
    Donno-45 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2017
    Location
    Perth Western Australia
    Posts
    2
    Thanks Alex. Changing the Description RowSource away from the query and back to the table worked, although that does not work on the stand-alone form!?

    I did have Requery code for the afterupdate event.

    I am a newbie and wonder if you have a moment you could give a little explanation to your "need to reference it via the mainform>subform control>" statement.

    Many thanks again!

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    take a look at the link I provided

    If you have a control called 'myControl' on a form called 'myForm' you would reference it from outside of the form through the forms collection as

    forms!myForm!myControl

    but if the myForm form as a subform control called 'mySubForm' and the myControl,is on that subform, you would reference it from outside the form as

    forms!myForm!mySubform.Form.myControl

    sticking with the form with subform. If myControl is on the main form. From the subform you could reference it as

    forms!myForm!myControl

    or you can just use

    parent.myControl

    if myControl is on the subform you would either reference it from the subform as

    forms!myForm!mySubform.Form.myControl

    or simply

    [myControl]

    and finally if myControl is on the subform. From the main form you could reference it as

    forms!myForm!mySubform.Form.myControl

    or

    mySubForm.Form.myControl

    using forms! means you are using the forms collection. You must use this method if you are accessing the control from another form, report or a query.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 07-03-2014, 12:00 PM
  2. Replies: 1
    Last Post: 07-24-2013, 11:48 AM
  3. Combo box doesn't work properly
    By joe1987 in forum Forms
    Replies: 9
    Last Post: 11-10-2011, 04:34 PM
  4. Error with filtering subform using 2 combo boxes
    By dharmacloud in forum Forms
    Replies: 4
    Last Post: 08-22-2011, 10:46 AM
  5. Replies: 0
    Last Post: 08-17-2008, 12:19 PM

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