Results 1 to 7 of 7
  1. #1
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44

    Dual Column Comboxbox

    Good day.



    I've been struggling with this for a while.

    I have a subform (subfrmPlayerStats) which includes a combobox (called [Team]) with two columns (NHLTeam in one column, and their respective AHLTeam in the second).

    Click image for larger version. 

Name:	help2.PNG 
Views:	17 
Size:	17.5 KB 
ID:	26107

    The record source for this subform is called "qryPlayerStats". In this query, there are three tables:

    Click image for larger version. 

Name:	help1.PNG 
Views:	17 
Size:	17.4 KB 
ID:	26106

    There is a checkbox in the main form (frmPlayerStats) called [Minors].

    What I want to do is when I check the box, the combobox will store column 2, and when unchecked, it will store column 1.

    Any help is greatly appreciated.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It would make more sense to have only one table for all teams, with a "League" field (or whatever you call it!)

  3. #3
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    I tried that route before, but if it makes more sense, I reverted back to that. Now I have one table (tblTeams):
    Click image for larger version. 

Name:	help3.PNG 
Views:	16 
Size:	50.9 KB 
ID:	26108

    Click image for larger version. 

Name:	help1.PNG 
Views:	16 
Size:	13.4 KB 
ID:	26109

    The combobox contents are still the same.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Just one field as well - "Team", with a type of team field noting NHL or AHL, maybe one for Minor yes/no. Plus whatever else you need associated with that team (city, etc)

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry, not a sports fan here! You say "associated" team, not sure what that means, but that would be another table which would be a cross-reference between the two holding each team's primary key value.

    The Teams table requires a primary key which in this case would be an AutoNumber - TeamID.

  6. #6
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    Each NHL team has their own AHL Team (see first picture where the NHL team is on the left column and their respective AHL team is on the right hand column). Therefore, I want to create another record where the player played on the respective AHL team. For example, looking at the above first picture, if a player was on the Washington Capitals, and I want to have that person play to their respective AHL Team, I would add a record ("Next Team/Season" button - see below), check the [Minors] checkbox on the main form, and select "Washington Capitals | Hershey Bears" but it would store "Hershey Bears" in the table below the form (see picture below).

    Click image for larger version. 

Name:	help2.PNG 
Views:	17 
Size:	16.0 KB 
ID:	26110

    [NHLTeam] is tblTeams is the primary key (see query picture above)

  7. #7
    dotcanada is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jul 2015
    Location
    Alberta, Canada
    Posts
    44
    I think I may have found a solution. Not sure if it will work in the long run, but the result so far is what I'm looking for. What I did was create another query for AHL Teams and their stats. I also had to create a new AHL subform that would appear when I check the [Minors] checkbox. Here was the big thing. I changed the record source for the table (the last photo above) from qryPlayerStats to tblPlayerStats. Therefore, all records would show up relating to the Player's ID.

    It's not exactly I was trying to do, but close enough.

    Cheers.

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

Similar Threads

  1. Similar to DUAL in Oracle
    By orange in forum Sample Databases
    Replies: 2
    Last Post: 04-30-2016, 08:18 AM
  2. Filter dual-column combo box
    By lisa071 in forum Forms
    Replies: 1
    Last Post: 03-31-2015, 12:18 PM
  3. Dual language message box
    By Derrick T. Davidson in forum Programming
    Replies: 1
    Last Post: 07-10-2013, 07:11 AM
  4. Replies: 3
    Last Post: 09-16-2012, 12:34 AM
  5. Replies: 3
    Last Post: 09-11-2012, 10:14 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