Results 1 to 6 of 6
  1. #1
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282

    Dropdowns based on other dropdowns

    Hi, this might get a little confusing...

    I need the user to be able to select the District, which then limits which team is available in the dropdown below... I believe I have done this.
    I then realised that if the incorrect district was selected initially, and then changed, that the team dropdown still showed those available for the first district... I believed I had corrected this using the code below:
    Private Sub District_AfterUpdate()
    Me.Team = vbNullString
    Me.Team.Requery
    End Sub

    However, now it is saying that I need to populate the Team first, so that in can make it blank... any ideas?

    The form I was using was the "AddAssessor" form, but it will (eventually) also apply on the "AssessorRecords" form.



    Note: the forms which begin with an "o" are old versions of my forms, as I had to change the design of my database and I have not yet changed the new ones since I am also having trouble with this.

    If you need any more info etc just let me know
    Attached Files Attached Files

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    in general you are on the right track but you need to state when the error is being trigger and more precisely state the error message;

    also try Me.Team ="" for a simple blank out

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    "TeamID" is a Long Integer field... The control "Team" is bound to "TeamID". Therefore you cannot set it equal to "vbNullString" because a number field cannot hold a string (even a null string).
    I always use "Empty" when trying to clear a number field.

    Combo box "Team" is a union query. Don't know why. Removed the union..... seems to work now...





    -----------------------------------------------------------------------------

    Every code page should have these two lines at the top:
    Code:
    Option Compare Database
    Option Explicit
    You can develop without either of the lines, but having them could/will save you hours of debugging.

    In "TblAssessor", "Position" is a reserved word in Access and shouldn't be used for an object name. Also, there is a field name "Current?". A better name would be "IsCurrent". Shouldn't use spaces, punctuation or special characters (exception is the underscore) in object names.



    BTW, "Dropdowns based on other dropdowns" is often referred to as "Cascading combo boxes".
    Attached Files Attached Files

  4. #4
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Thanks both of you, I got the vbnullstring from Baldy-Cascading Combos, it appears it just wasn't quite right in this scenario.

    I did try NTC's suggestion so I know for the future but it didn't seem to like it, not sure why.

    Similarly I got the Row Source code from another site online and just tried it, hence the union.

    I didn't realise "Position" was reserved and had forgotten I had a question mark for that field, I have changed them now.

    I wasn't aware there was another piece of code that was required (since it doesn't automatically pull through), while I appear to have accidentally deleted some while copy and pasting, I have changed this now.

    I used the same code on the "AssessorRecords" Form but it is asking for value in the District field... I believe this is because the query is trying to pull through the team ID, but there isn't anything in the district ID for it to base it on. I would like it to pull through the district anyway, so we could (possibly) kill two birds with one stone here.

    Also, how did you get it to automatically to open the "AddAssessor" form? I would like to change it so that the Homepage is automatically opened if possible as this will be better for the eventual user.

    Thanks for your help.
    Attached Files Attached Files

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Basic troubleshooting.... (no pun intended).

    I open form "AssessorRecords" (remember this - it is important)
    I know I have cascading combo boxes (As developer/programmer you should)
    I select a value from the first combo box (District) - and get an error
    The error is "Enter Parameter Value". Below that it has "Forms!AddAssessor!District"
    Looking at the "District" combo box afterupdate event, it has "Me.Team.Requery". I know that this is the second (dependent) combo box of the cascading combo boxes.
    I look at the row source for the combo box "Team". Where is the criteria coming from?
    Oh, yeah. From the error message and looking at the SQL, I see "Forms!AddAssessor!District".
    But wait! What form am I on? It is "AssessorRecords". So the criteria needs to look at the at the primary combo box (District) to get the criteria.
    Should "Forms!AddAssessor!District" be referenced or "Forms!AssessorRecords!District" be referenced?


    -------------------------------------------------------------------------------------
    Why do the two TEAM combo boxes (on different forms) have different row sources?

    Form AddAssessor - combo box TEAM row source
    Code:
    SELECT DISTINCT TblTeam.TeamID, TblTeam.Team 
    FROM TblTeam 
    WHERE (((TblTeam.DistrictID)=[forms]![AddAssessor]![District]));
    Form AssessorRecords - combo box TEAM row source
    Code:
    SELECT TblTeam.TeamID, TblTeam.Team, TblTeam.DistrictID 
    FROM TblDistrict INNER JOIN TblTeam ON TblDistrict.DistrictID = TblTeam.DistrictID 
    WHERE (((TblTeam.DistrictID)=[forms]![AssessorRecords]![District]));
    The combo boxes are doing the same thing, just on different forms..........?????


    Does all of the above make sense??




    -------------------------------------------------------------------------------------

    Also, how did you get it to automatically to open the "AddAssessor" form?
    FILE/Options/Current Database/Display Form


    -------------------------------------------------------------------------------------
    These might help you: (they are a couple of my bookmarked sites I reference)
    Problem names and reserved words in Access
    http://allenbrowne.com/AppIssueBadWord.html

    This might help you: (It is one of my bookmarked sites)
    Debugging VBA Code
    www.cpearson.com/excel/DebuggingVBA.aspx

  6. #6
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    Aah. One of the problems with copying and pasting code...

    I can't really understand the error messages which is why I had to come on here.

    Why do the two TEAM combo boxes (on different forms) have different row sources?
    I was using the design view, and in one I pulled through the tblDistrict, the other I didn't. I have now made them the same and all works, thanks for pointing this out.

    OK, I think I only have one problem left, but it's a big one so it has its own thread: https://www.accessforums.net/showthread.php?t=58056

    Thanks for your help with this, you've fixed a whole lot of headaches...
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 11
    Last Post: 02-12-2015, 12:15 PM
  2. best way to create form with 30 dropdowns
    By tagteam in forum Forms
    Replies: 5
    Last Post: 04-07-2014, 07:20 PM
  3. Replies: 2
    Last Post: 02-20-2014, 05:54 PM
  4. Access Dropdowns
    By gor in forum Access
    Replies: 6
    Last Post: 07-06-2012, 11:42 PM
  5. dropdowns and listboxes
    By t_dot in forum Forms
    Replies: 6
    Last Post: 08-19-2010, 11:12 AM

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