ComboBox events

[jacks]

I have two ComboBox'es. The values of the second Combo depend on the first one.

If I select a value from the first Combo then a statement (Me.club_a.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club WHERE club.[league-id]=" & Me.league
) in the Change event causes the RowSource of the second Combobox to display only the related values.

That way I entered some data.

When I paged back through the records I found the second Combo only displaying values if they match the then current value in the first Combo. (okay.. logical I then thought.. i'v changed the rowsource, and the corresponding value can't be found anymore, so they won't show)

So, I decided to reset the RowSource of the second Combo to the entire table again on the Current event (Me.club_a.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club").

That worked. The values in the second Combo displayed fine. The value in the first Combo stayed the same.

But, adding new records didn't work the way I wanted anymore. The Current event caused that I have to explicitly select a value from the first Combo again to get the second Combo to show only the dependent records.

If I page back I want the first Combo to display the value related to the second Combo box. If I enter new records, I want the second ComboBox to have a RecordSource dependant on the value in the first ComboBox.

maybe someone can help me with a hopefully very simple solution.

thanks in advance.

 

[RuralGuy]

I suspect your ComboBoxes have no ControlSource. There is nothing tying the ComboBox to a field in a record. If cbo2 is already set to filter results based on cbo1 then all you need to do in cbo1 is requery cbo2. I would use the AfterUpdate event of cbo1 rather than the Change event. The Change event occurs for *every* keystroke.

 

[jacks]

I suspect your ComboBoxes have no ControlSource. There is nothing tying the ComboBox to a field in a record. If cbo2 is already set to filter results based on cbo1 then all you need to do in cbo1 is requery cbo2. I would use the AfterUpdate event of cbo1 rather than the Change event. The Change event occurs for *every* keystroke.

maybe I need to describe the problem better..

tables : club, league, match
relation : club-league 1:n
form based on match (match has two fields, club a and club b)

The form has
1. an unbound ComboBox league.
2. two ComboBoxes club a and club b.

The form has an On Load event with the statement : Me.league = Me.league.ItemData(0)

The league ComboBox has no ControlSource and a RecordSource : SELECT league.[league-id], league.league FROM league;
The club ComboBoxes have as ControlSource club a and club b respectively.
Their RowSource is : SELECT club.[club-id], club.naam, club.[league-id] FROM club;

The only other event is AfterUpdate on league :
Me.club_a.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club WHERE club.[league-id]=" & Me.league
Me.club_b.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club WHERE club.[league-id]=" & Me.league

the problem :

If I start the form I can navigate from record to record. The values of the club ComboBoxes are visible.
When I insert a new record and I select a league, then the AfterUpdate event fires and the RowSource of the club ComboBoxes changes. That's okay for inserting.. and I select values. After inserting other records, I want to navigate to previous inserted records.

And then I don't see the club ComboBox values anymore if the club's league is not the same as in the league ComboBox.

How can I change that.

 

[RuralGuy]

I believe I now understand your dilemma. Try placing the following code in the Current event of your form.

Private Sub Form_Current()
If Not Me.NewRecord Then
   Me.club_a.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club;" 
   Me.club_b.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club;"
End If
End Sub

 

[jacks]

I believe I now understand your dilemma. Try placing the following code in the Current event of your form.

Private Sub Form_Current()
If Not Me.NewRecord Then
   Me.club_a.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club;" 
   Me.club_b.RowSource = "SELECT club.[club-id], club.naam, club.[league-id] FROM club;"
End If
End Sub

It works ! Thank you so much. This thing was driving me crazy.. From these lines I learn alot. thanks again. you'r great.

 

[RuralGuy]

You're welcome. Glad I could help.