combo box update

  • Thread starter Thread starter george
  • Start date Start date
G

george

Hi,

On my clients form, frmClients, I have a combo box,
cboClients, searching my clients based on client_ClientID.

I also have a lookup field, clie_ClientStatus, taking the
values 2,3,4 for active, inactive, prospect. Through this
field I assign each client a status.

Finally I have an option group, optClientStatus, taking
the values 1,2,3, for active, inactive, prospect. I use
this option group to filter my form so that only prospects
appear, if I chose lets say 3.

The problem is that my combo box doesn't update well. When
I choose 1 in the option group everything works well. I
can select and synchronize. If I chose 2 or 3 however its
drop down list becomes empty (the form is filtered well
its only the combo box which doesn't update)

The code behind the combo box is the following

Private Sub cboClients_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.findfirst "[client_ClientID] = " & str(Nz(Me!
[cboClients], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


The code behind the option group is

Private Sub Form_AfterUpdate()

Select Case optClientStatus

Case 1
Me.Filter = "client_ClientStatus = 2"
Me.FilterOn = True
Case 2
Me.Filter = "client_ClientStatus = 3"
Me.FilterOn = True
Case 3
Me.Filter = "client_ClientStatus = 4"
Me.FilterOn = True

End Select

End Sub

Can someone please offer any suggestions at all?
Thanks George
 
Hi George

When you select a different filter, presumably you want your combo list to
change to reflect the new set of clients. So, you should redefine the
combo's RowSource when you apply the form filter - for example:

cboClients.RowSource = "Select client_ClientID, client_ClientName " _
& "from tblClients where " & Me.Filter

Also, you might like to synchronise your combo with the current record, so
add this to your Form_Current procedure:
cboClients = Me.client_ClientID
 
Back
Top