Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51

    ComboBox List

    Attachment 33196

    The form is feed by the query and its to pick up any team members who have "no" in the team member combobox. Then the user can reassign them to a new team or leave them in the dead pile.

    Then problem is I cant get the combobox list to display the team names as a list. I used the same query that feed the form to create the combobox list and it only displays team ids. I have moved the bound column around but still will not display the team names only IDs or nothing.

    The current query pulls two records and displays them properly.



    Problem:
    I need combo box to display team names as a drop down list and than after update to update the teamID textbox then when user clicks exit button for it to save the record with the changes.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the team combo would be bound to col 1, teamid
    you can make this # invisible to the user , so they only see team names ,col 2
    in the combo properties: set col widths: 0;2

  3. #3
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    ranman256

    your solutions did excatly what you said it would do and apparently fixed the problem that I thought I had.

    The problem now is that my dropdown list is not a list of all the team name. The current dropdown list only contains the team names of the two records that the query pulled.

    I could type them into a value list but was taught this is never a good idea.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    Orange

    The database is 1.43 MB in size and the limit is 500 KB.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    Orange,

    Here is the DB.

    Thank you

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    On the form frmEmpAssign change the Control Source of cboTeam to Teams (you want all the Team Names right?) and for the cboTeam format Column Count to 2 and widths to 0";2"

    You seem to have a lot of queries??
    I identified how to make the cboTeam display all Team Names. There could be code added to modify records as you want.

    Update: I did add the following to the afterUpdate event of the cboTeam

    Code:
    Private Sub cboTeam_AfterUpdate()
        Dim sql As String
        sql = "Update tblEmployees set teamID = '" & Me.cboTeam & "', Employed = 'Yes'"
        'Debug.Print sql
        CurrentDb.Execute sql, dbFailOnError
        Me.Refresh
    End Sub
    Good luck.

    These changes result in the following when I click the cboTeam dropdown

    Click image for larger version. 

Name:	TeamCombo.png 
Views:	21 
Size:	101.2 KB 
ID:	33211

  9. #9
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    Orange,

    Thank You! So, yes I have alot of querries but that's how I manipulate my data. Is there a better way or should I be able to use one query for more purpose?

    Thank You

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There is no one right way. If it works for you and is maintainable then it's right.

    If all developers went back and reviewed some older code they had written, I'm sure most would say "geez what was I thinking", Followed by "I can think of x ways to improve that...".

    Good luck with your project.

  11. #11
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    Orange,

    So following your example above the combo box does populate with all the team names but the sql statement changes the team id in the table for every record to what ever team you choose for the team member being assigned. This may or could be promblematic in the least.

    Thanks for the help

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Daisy509th,

    I have looked little more into your database based on your last post. You are correct --the code updates all records and it shouldn't.

    The Update sql I gave should restrict the Update with a where clause to the Employee identified on the form.
    However, the record source of this form does not include the EmpID.
    To get the form and update to restrict the Update to the Employee involved,
    -I added EmpID to your query,
    -I added a text box on the form for EmpId,
    -the text box is NOT visible (format) {aesthetics only} it could be visible

    so the new code for he after update event becomes
    Code:
    Private Sub cboTeam_AfterUpdate()
        Dim sql As String
        sql = "Update tblEmployees set teamID = '" & Me.cboTeam & "', Employed = 'Yes' where empid = " & Me.EmpID
        'Debug.Print sql
        CurrentDb.Execute sql, dbFailOnError
        Me.Requery
    End Sub
    But I am not familiar enough with what you are trying to do to adjust other things.
    For example, why are the Team names stored in your tblEmployees??

    I think we are addressing issues sort of by trial and error, or as we discover new issues.

    As I mentioned previously, you seem to have more queries than usual. That isn't necessarily an issue. If you know the application, and the queries and logic makes sense to you and the processes involved, then all is OK.

  13. #13
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    Orange,

    The team name is not stored in the tblEmployees the TeamID is stored there. The reason to store the TeamID with employees is because each Team can have many employees.
    I also am receiving this error when trying to update a record. I believe that the sql statement is writting to the wrong place on the record. I believe it to be writting the Team name instead of the team id.

    On the form once the user picks a new team for the employee. I want the combobox to update the team id box on the form then maybe if possible have the sql statement write to the record Yes for employed and the new team id relative to the employee.

    Thanks

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmm?

    Using this Query1 SQL
    Code:
    SELECT tblEmployees.FirstName
    , tblEmployees.LastName
    , tblEmployees.EmpID
    FROM tblEmployees;
    I get these values in a portion of the result set???

    FirstName LastName EmpID
    Tony Walker 139
    Terry Williams 140
    Victor Herrera 141
    Victor Villanneva 142
    Willie Bills 143
    Wilbert York 144
    Team
    Alpha Bulldogs 147
    Team BAH 148
    Team Manor Regulators 149
    Team Dirty Dozen 150
    Team Heavy Hitters 151
    Team Roll as One 152
    Team Southside Bulldogs 153
    Team Wild Cards 154
    Team Misfits 155
    Team Kyle 156
    Team Liberty Hillbillies 157
    Cameron Crawford 158
    Larry Stephens 159
    Gary Curtis 160
    Gayland Hamilton 161
    This is what I see. ????? I've gone back to your original and you are correct --team name is not a field there????? But "Team" is listed a the FirstName??? and TeamName as LastName???


    With respect to what is being updated in the after update event of the combo, here is the SQL for the UPDATE. I modified a few records to Employed = 'No' to test the code. Here are 5 update statements used in my tests:

    Code:
    Update tblEmployees set teamID = 'MR', Employed = 'Yes' where empid = 196
    Update tblEmployees set teamID = 'DD', Employed = 'Yes' where empid = 73
    Update tblEmployees set teamID = 'MF', Employed = 'Yes' where empid = 83
    Update tblEmployees set teamID = 'SB', Employed = 'Yes' where empid = 196
    Update tblEmployees set teamID = 'BA', Employed = 'Yes' where empid = 77
    I think that you should post the specs and clear description of requirements if I am going to be of any further help. Perhaps someone else has a better interpretation of your set up and can offer advice.
    Last edited by orange; 03-22-2018 at 12:06 PM. Reason: clarification

  15. #15
    Daisy509th's Avatar
    Daisy509th is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    Central Texas
    Posts
    51
    Orange,

    Sorry, for the confussion but those are the names for the Admin of each team. There are ways that they can earn points as teams so we fill them under those names so no one person gets the credit for earning those points.

    I am also still getting the error that was earlier post about cboTeam. I am confused.

    I changed the cboTeam control source to tblteams I also adjusted the column count to 2.

    Put code under after_update

    Then I receive error

    Thanks

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

Similar Threads

  1. Update Combobox List from another value
    By Joakim N in forum Access
    Replies: 7
    Last Post: 10-12-2016, 11:19 AM
  2. Limit Combobox list
    By gw2013 in forum Forms
    Replies: 21
    Last Post: 11-26-2015, 12:09 PM
  3. Combobox list does not match query
    By tcox in forum Access
    Replies: 5
    Last Post: 11-20-2015, 10:41 AM
  4. Change Combobox list after use it
    By TesZero in forum Programming
    Replies: 4
    Last Post: 07-22-2014, 02:14 AM
  5. How to select ComboBox list in Run time
    By sdondeti in forum Forms
    Replies: 2
    Last Post: 06-20-2011, 10:21 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