Results 1 to 10 of 10
  1. #1
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237

    how to filter a combo box when a form loads


    i have a continuous form jobsearch which has a list of jobs with primary key JobNoID_PK, when i select a job and open a form JobProgress has the details of the job. im implementing multiple combo boxes to cascade a subform which is fine ive been able to find alot of info on cascading combo boxes.

    my issue is the first combo box - cboStage i want to filter to only show the details for the JobNoID_PK and at the moment it shows all of the stages that are in the table not just for this select record. from what ive read and understand cascading happens with queries and ive gotta get my head around that but is it the same for this issue also.

    i would of thought a link child fields to master fields would do it but have no luck, ive tried messing around with queries but still learning there also with not alot of luck of filtering to 1 record on load or dont know how to it if possible.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    my issue is the first combo box - cboStage i want to filter to only show the details for the JobNoID_PK............
    So why use a combo box? Why not use a textbox control bound to JobNoID_PK
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    from what ive read and understand cascading happens with queries
    don't think so, think your are referring to 'cascading combos' where the rowsource of one combo is dependant on the rowsource of another

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Are you using form/subform arrangement? The subform should show only stage records assigned to JobNoID synchronized by master/child Links properties. If combobox is BOUND and used for data entry, its list could be restricted to show only those stages not yet assigned. If this is an UNBOUND combo used to search for stage record then it could be restricted to show only assigned stages. The RowSource would have to be a join of data table and lookup table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Bob Fitz View Post
    So why use a combo box? Why not use a textbox control bound to JobNoID_PK
    i have a control bound to JobNoID_PK on the form already, there could be 5 stages for 1 job hence why a combo box would be beneficial.

  6. #6
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by Ajax View Post
    don't think so, think your are referring to 'cascading combos' where the rowsource of one combo is dependant on the rowsource of another
    yeah as i said earlier im still getting my head around how to cascade combos but thats my next issue after i fix this first one i have.

  7. #7
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by June7 View Post
    Are you using form/subform arrangement? The subform should show only stage records assigned to JobNoID synchronized by master/child Links properties. If combobox is BOUND and used for data entry, its list could be restricted to show only those stages not yet assigned. If this is an UNBOUND combo used to search for stage record then it could be restricted to show only assigned stages. The RowSource would have to be a join of data table and lookup table.
    yeah correct im using this setup, to take you back few earlier steps, i had the stage originally as a subform but the main parent form i have all my subforms on is getting quite busy so to reduce space im changing some of the subforms to combo boxes.
    my subforms are link through master/child as you said and work as they should and each subform updates the next subform.

    my combo box is unbound but selects data from the stage table... yes thats what im trying to do restrict assigned stages, can you explain how join data and lookup table, ive tried various sql codes that ive found but yea no luck getting them to work

    this is my sql behind combo box row source, it has an inner join but not sure how to manipulate this to only show stages for the 1 job that is opened -

    SELECT tblStage_BuildingDetails.Stage_BuildingID_PK, tblStage_BuildingDetails.StageNo_BuildingName, tblJobDetails.JobNoID_PK
    FROM tblJobDetails INNER JOIN tblStage_BuildingDetails ON tblJobDetails.JobNoID_PK = tblStage_BuildingDetails.JobNoID_FK
    ORDER BY tblStage_BuildingDetails.Stage_BuildingID_PK DESC , tblStage_BuildingDetails.StageNo_BuildingName;

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Use WHERE clause that references JobNoID field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by June7 View Post
    Use WHERE clause that references JobNoID field.
    im not familiar at all with sql I tried a few different ways one being WHERE JobNoID_FK = JobNoID_PK but had no luck with combo box

  10. #10
    ShaunG is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jan 2018
    Posts
    237
    Quote Originally Posted by June7 View Post
    Use WHERE clause that references JobNoID field.
    thanks june7 i have just worked it out didn't realise you could right click on it and build it in the criteria in the query.... been working on this since friday night knew it would be something easy.

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

Similar Threads

  1. Replies: 4
    Last Post: 02-24-2018, 11:48 PM
  2. Replies: 4
    Last Post: 05-01-2017, 11:32 AM
  3. Replies: 11
    Last Post: 09-03-2015, 11:12 AM
  4. Form loads blank
    By sweetiepie in forum Forms
    Replies: 5
    Last Post: 06-07-2012, 02:34 PM
  5. list box not updating until form loads
    By cowboy in forum Forms
    Replies: 3
    Last Post: 03-12-2010, 12:02 PM

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