Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Cascading combo on subform (WHERE) Clause

    HI all,


    Should be simple but never seems to be... I have a form (dbAttached) called ProjectFrm and on that form is a sub form. I have have three combos in contious subform and two hidden. There is a Company Role and Project Scope combos, which once selected from Company Role, then i only want the values of that patictular company role when i select the Project Scope. I am trying to use a Where clause in the row source but cannot get the results. I have tried this many ways now and cannot get it to give me the correct results of the previous combo.

    What is wrong?

    Code:
    SELECT [ProjectScopeTbl].[ProjectScopeID], [ProjectScopeTbl].[CompanyRoleID], [ProjectScopeTbl].[ProjectScopeName] FROM ProjectScopeTbl WHERE CompanyRoleID = CboCompanyRoleID ORDER BY [ProjectScopeName];
    Parkway Projects.zip
    Dave

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if you are using this sql code as the cbo source, it should work, but are you refreshing the 2nd combo in the afterupdate of combo1?
    Code:
    sub cbo1_afterupdate()
    cbo2.requery
    end sub

    if you are using a query, then the criteria must be the full path to the subform.
    where [field]=forms!myForm!subform.Form.cbo1

    but you still need the cbo2.requery

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Ramman256
    I do have the requery in cbo1

    I just tried this and got a prameter error?
    Code:
    SELECT [ProjectScopeTbl].[ProjectScopeID], [ProjectScopeTbl].[CompanyRoleID], [ProjectScopeTbl].[ProjectScopeName] FROM ProjectScopeTbl WHERE [CompanyRoleID]=[Forms]![ProjectMemberSubFrm]!Subform.Form.CboCompanyRoleID ORDER BY [ProjectScopeName];

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by d9pierce1 View Post
    HI all,
    Should be simple but never seems to be... I have a form (dbAttached) called ProjectFrm and on that form is a sub form. I have have three combos in contious subform and two hidden. There is a Company Role and Project Scope combos, which once selected from Company Role, then i only want the values of that patictular company role when i select the Project Scope. I am trying to use a Where clause in the row source but cannot get the results. I have tried this many ways now and cannot get it to give me the correct results of the previous combo.

    What is wrong?

    Code:
    SELECT [ProjectScopeTbl].[ProjectScopeID], [ProjectScopeTbl].[CompanyRoleID], [ProjectScopeTbl].[ProjectScopeName] FROM ProjectScopeTbl WHERE CompanyRoleID = CboCompanyRoleID ORDER BY [ProjectScopeName];
    Parkway Projects.zip
    Dave
    Dave,
    Not sure if the attached is what you are looking for. If it is and you require any explanation please post back
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you Bob,\
    I see a new txtbox over the cbo and added to qry. That works well. I thank you
    Dave

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by d9pierce1 View Post
    Thank you Bob,\
    I see a new txtbox over the cbo and added to qry. That works well. I thank you
    Dave
    You should also be aware of the code in the After Updata event of the combo called "CboCompanyRoleID"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You cannot just do this
    Code:
    WHERE CompanyRoleID = CboCompanyRoleID
    The query has no idea what CboCompanyRoleID is, nor is it a valid syntax in code.
    If it's code on a form, then you have to concatenate with Me
    Code:
    WHERE CompanyRoleID = " & Me.CboCompanyRoleID & " ORDER BY..."
    if it's in a query then
    Code:
    WHERE CompanyRoleID = Forms!YourFormName.CboCompanyRoleID ORDER BY...
    and all that assumes the ID is a number.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Bob,
    I ran into an issue with the txt box overlay i didnt see coming. When i type in something new and not in list is fired, it wont let my add go through and add it as in the not in list code. Any way around that?
    Thanks

  9. #9
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks Micron
    i have tried this 10 ways to sunday and nothing seems to get the desired results. The ID is a num, Maybe because i used some look up fields in the table?
    I will play somemore with it tonight and see what i can get figured out.
    Thanks
    Dave

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm kind of surprised you're using lookup fields in a table - thought you had been around here long enough to know not to do that. I guess you are persevering with them or you've not been advised before. Not sure if that is relevant here because I didn't download the db(s) as I figured I'm too far behind. Just to clarify, by ID I meant the field bound to cboCompanyRoleID. If you have created a lookup field on an actual autonumber ID field, that would be odd for sure. If your syntax is correct now but you're not getting the right results, then yeah, a lookup field might be the root of the issue.

    I just wanted to point out what I saw as an issue with the syntax you posted - don't want to tread on Bob's toes.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks micron
    I was just seeing how they work actually as never used them before as far as lookup fields so was curious why not to use them mostly, now i know, LOL...
    I think i will play with this tomorrow, to tired this evening to mess with it.
    Thanks

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see Micron already brought up the look up at the field level, but I do have one question.

    I like to have the flow in the relationship window to go from left (the one side) to right (the many side).
    In rearranging the tables I see two table PK fields connected to one table FK field.
    Click image for larger version. 

Name:	Relationships.png 
Views:	14 
Size:	86.9 KB 
ID:	44097
    I'm thinking "That's not going to work very well"!
    Based on the FK field name, Maybe remove the link from "CompanyTbl.CompanyID???

  13. #13
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by d9pierce1 View Post
    Hi Bob,
    I ran into an issue with the txt box overlay i didnt see coming. When i type in something new and not in list is fired, it wont let my add go through and add it as in the not in list code. Any way around that?
    Thanks
    Dave,
    Does the code actually run?
    Check that you have [Event Procedure] selected in the Property sheet.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  14. #14
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Parkway Projects Test.zip

    Hi all,
    OK, I reworked my tables to not have lookups and I have managed to get the Where Clause to somewhat function.

    So, the problem is....

    When i Create a new record in the SubForm there are five fields. Two hidden and three that are visible.
    I have also made it a data sheet instead of a sub form.
    So I select a company from a combo, i select a company role from a combo (Which fires the afterupdate event to give me just the data = that selection, and then i select a project scope.
    If i then go to the next record in subform, then when i select a new company role, the others disapear that were not of that company role. How on earth do i get the record to stay visible
    when selecting a different company role when adding a new record? I cant use a text box overlay as it wont allow me to use my NotInList function to add.

    Once the after update event fires, it blanks out all my other Project Scope Fields?

    Any way to resolve this at all?

    Thanks
    Dave

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Once the after update event fires, it blanks out all my other Project Scope Fields?

    Any way to resolve this at all?
    Don't requery the CompanyRoleID control. Not sure if that will give you the result you need in terms of the last combo list. I see that your form is bound to a query with no criteria so I'm kind of surprised at the behaviour. At first I thought that the subform record list was no longer valid because it no longer fit the criteria. Since there is none, perhaps it no longer fits the joins. Sorry I don't have time to investigate further right now. Maybe you can follow up on that and see if it helps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Cascading Combo Box on Form to Subform?
    By d9pierce1 in forum Programming
    Replies: 14
    Last Post: 12-08-2020, 11:34 AM
  2. Replies: 1
    Last Post: 10-31-2018, 01:14 PM
  3. Cascading Combo Box and Subform Issues
    By RossIV in forum Forms
    Replies: 13
    Last Post: 07-18-2013, 09:16 AM
  4. Cascading Combo in SubForm
    By ggs in forum Forms
    Replies: 1
    Last Post: 02-16-2012, 01:32 AM
  5. cascading combo boxes in continuous subform
    By ayamali in forum Programming
    Replies: 1
    Last Post: 03-29-2011, 06:33 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