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

    Combo Box won't select from list

    So my database has a combo box where the user can select a district from a list. There is a combo box which uses this information to then allow the user to select a team from those available which the district was using - I'm not sure if this has caused the problem or not.

    Unfortunately, the user is not able to select anything from the list - the options are shown, as below:
    Click image for larger version. 

Name:	DistrictDropdown.png 
Views:	30 
Size:	92.5 KB 
ID:	25778
    But when the user attempts to select one of the options, nothing happens.
    I just noticed that when I try to make the change, the following message appears in the bottom left corner:


    Click image for larger version. 

Name:	DistrictDropdownError.PNG 
Views:	30 
Size:	1.6 KB 
ID:	25779
    Does anyone have any ideas?

    The problem is on the AssessorRecords form of my database, which I have attached to assist you in finding the problem.
    Attached Files Attached Files

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Though I don't completely understand what the form is trying to accomplish, as the error states your Combo Box would be trying to change the AutoNumber DistrictID Field, which is the Primary Key (PK) field of the TblDistrict table. I suspect you may want to change the DistrictID field of the TblTeam table instead but that is not what you included in the QryAssessor query to which the form is bound.

  3. #3
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    I can't believe it was something so simple, that's had me confused for ages!

    It has caused a problem though - when I update that field, the Team field below it empties and then updates - as before this is a cascading combo box so the change in the district field affects the options in the team field, for some reason, changing the district now results in an error however, see box below:
    Click image for larger version. 

Name:	DistrictUpdateError.PNG 
Views:	30 
Size:	19.0 KB 
ID:	25781
    When I debug, it highlights the section which empties the team field:
    Click image for larger version. 

Name:	DistrictUpdateDebug.PNG 
Views:	30 
Size:	2.7 KB 
ID:	25783
    Do you know what the reason for this is?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What happens when you comment out (put a ' at the beginning of the line) the Me.Team = Empty line?

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    When a combobox is used for selecting which record to display it cannot be a bound control, it must be left unbound and the record retrieved based on the selection.

  6. #6
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    If I don't bound the combobox, it doesn't pull through the assessor's current district - I want it to be pulled through, and changed if necessary.

    By commenting out the the line you mentioned, it allows me to select something, but I then experience the problem I had before where I cannot select anything from the Team dropdown (although it does work if I just change the team, not the district).

    Again, I have attached the database. The problem is still on the AssessorRecords form.
    Attached Files Attached Files

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    These functions have to done separately, none can be combined :
    1 - select district
    2 - retrieve correct record from table
    3 - make changes to record

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    After looking at your dB, I would say the table "TblAssessor" structure is incorrect. From form "AssessorRecord", you keep trying to change the "DistrictID" in "TblDistrict". This cannot be done.

    The rule is one table, one form. But "QryAssessor" has 3 tables in it.

    I would change the table structure for "TblAssessor" by adding field "DistructID" (Long).
    Click image for larger version. 

Name:	Assessment1.jpg 
Views:	24 
Size:	31.3 KB 
ID:	25796

    I would change "QryAssessor" to
    Code:
    SELECT TblAssessor.AssessorID, TblAssessor.AssessorName, TblAssessor.JobTitle, TblAssessor.Organisation, TblAssessor.WorkNumber, TblAssessor.EmailAddress, TblAssessor.TeamID_FK, TblAssessor.Address, TblAssessor.IsCurrent, TblAssessor.DistrictID_FK
    FROM TblAssessor
    WHERE (((TblAssessor.AssessorName)=[Forms]![Homepage]![SearchAssessorName]));
    The "District" after update event would be:
    Code:
    Private Sub District_AfterUpdate()
        Me.Team = Empty
        Me.Team.Requery
    End Sub


    Edit:
    @#$%#$..... I just realized I used my naming convention for the two fields in "TblAssessor". Just forget the "_FK" after the names.

  9. #9
    Heathey94 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2015
    Location
    United Kingdom
    Posts
    282
    That's brilliant, thank you!

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    @Steve I've got to ask, what does Me.Team = Empty accomplish?

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @RG
    It clears the value from the team combo box.

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks Steve. I had not seen it used that way before.

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

Similar Threads

  1. Select All in Combo Box List
    By kdub in forum Forms
    Replies: 9
    Last Post: 10-30-2014, 09:50 AM
  2. Replies: 4
    Last Post: 07-18-2014, 10:51 AM
  3. Add a combo box to select from a list
    By Sonny101 in forum Forms
    Replies: 12
    Last Post: 06-26-2014, 10:40 AM
  4. Combo Box Won't Select Last Item in List
    By Autumn227 in forum Forms
    Replies: 9
    Last Post: 12-18-2013, 02:37 PM
  5. Turn a list into a Multi select combo box
    By seth1685 in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 07:52 AM

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