combo box question

[Lhuffst]

My coworker has a form that has 2 combo boxes.

cboteam bound to dbo_teams.TeamID[/b
Rowsource = SELECT dbo_Teams.TeamId, dbo_Teams.TeamName FROM dbo_Teams;
Column Count = 2
Bound column = 1
After Update = Me.cboGroup.Requery

Table Data
TeamId TeamName
13 Engineering
14 IT

cboGroup bound to dbo_groups.groupid
Rowsource = SELECT dbo_GROUP.GroupID, dbo_GROUP.TeamId, dbo_GROUP.TEAM, dbo_GROUP.GROUP FROM dbo_GROUP
WHERE (((dbo_GROUP.TeamId)=[Forms]![dbo_TeamGroups2]![TeamID]));
Column Count = 4
Bound column = 1

Table Data
Groupid TeamID GroupName
1 13 Infrastructure
2 13 Project Delivery
3 14 Revenue
4 14 Disbursements


On his form, he selects the team from the first combo box. Based on that selection, the 2nd combo box populates. (I had him add a cbogroup.requery after update onthe first combo box. This works fine.
I don't do much with bound forms so I'm not sure what to tell him on this next part.
When you select the group from the 2nd combo box, it saves the group id to the result table along with the team id -- this is fine. However, when he navigates between records,
the group id doesn't display in the group combo box. I did verify that the data was in the table.

What he actually wants to do is save the group id to the table but when he navigates between records, he wants the combo box to show the actual group name not the id. Is this possible and how would you go about doing this.

there are 2 issues that i think he will come back and ask me
1. When you select the item you want in the combo box, can you display the team name (combo box 1) or group name(combo box 2) but save the id to the table.
2. When he pages back and forth on the records, how can he display the team name or group name in the combo box?

Thanks
lhuffst

 

[MajP]

The normal trick is something like this

"Select EmployeeID, EmployeeName from table employees"

Column count = 2
bound column = 1
Column widths = "0";"1"

So the ID is bound, but since it is invisible (width of 0) only the name shows.

 

[Lhuffst]

I tried that and that works when they are initially selecting the data.

If the id number is what is stored in the table, then how do I get the name to display when they navigate through records? Right now when I first put it in, it shows as expected but when I navigate off that record (go to new record or go to last record) and come back, it is blank At the table level I have

Team Id Group ID
1 9
11 2
12 3
1 10

I was expecting to see at least the group id.
1. How do I get it to display the id at least (not blanks) but preferably
2. How do I get it to translate that group id 9 is really SEC1 ?

 

[MajP]

OK, I see the problem. If the value you want to show is not included in the select statement of the combobox, then it cannot show. It will show blank.

The original rowsource should only be
Rowsource = SELECT dbo_GROUP.GroupID, dbo_GROUP.TeamId, dbo_GROUP.TEAM, dbo_GROUP.GROUP FROM dbo_GROUP

On the on enter event of cbo group you change the rowsource to
Rowsource = SELECT dbo_GROUP.GroupID, dbo_GROUP.TeamId, dbo_GROUP.TEAM, dbo_GROUP.GROUP FROM dbo_GROUP
WHERE (((dbo_GROUP.TeamId)=[Forms]![dbo_TeamGroups2]![TeamID]));

Then on the on current event (every time you move) you need to change the rowsource back to just this

Rowsource = SELECT dbo_GROUP.GroupID, dbo_GROUP.TeamId, dbo_GROUP.TEAM, dbo_GROUP.GROUP FROM dbo_GROUP

 

[Lhuffst]

I made the changes and that does display the actual name now. The only hiccup is now when I initially click the group combo box it is showing all the groups, not just the groups associated with their correspondening team. I did comment out the requery but wanted to also double check that when you said to put the

Rowsource = SELECT dbo_GROUP.GroupID, dbo_GROUP.TeamId, dbo_GROUP.TEAM, dbo_GROUP.GROUP FROM dbo_GROUP

at the oncurrent event, you did mean the form on current correct?

 

[MajP]

Not sure what you code looks like, but it should be like this

Private Sub CboGroup_Enter()
  Me.ComboGroup.RowSource = "SELECT dbo_GROUP.GroupID, dbo_GROUP.TeamId, dbo_GROUP.TEAM, dbo_GROUP.GROUP FROM dbo_GROUP WHERE dbo_GROUP.TeamId =[Forms]![dbo_TeamGroups2]![TeamID]"
  Me.CboGroup.Requery
End Sub

So when you enter the combo it has to apply the filter. You could use the on exit event to set it back instead of on current.

Private Sub CboGroup_Exit(Cancel As Integer)
 Me.ComboGroup.RowSource = "SELECT dbo_GROUP.GroupID, dbo_GROUP.TeamId, dbo_GROUP.TEAM, dbo_GROUP.GROUP FROM dbo_GROUP   
Me.CboGroup.Requery
End Sub