changing a combobox list from a command button

[BigToe]

Hi folks,
can anyone help me with an easy one:

- access 2000
I have a combo box with client names. the client names change based on two other combo boxes which do requeries. Unfortunately, I can't get the combo box with the client names to show all clients. I've tried using a command button as well as a "all clients" selection in one of the other combo boxes but I'm not saavy enough to code it properly. Any help would be appreciated.
thanks!

[JConsulting]

Hi folks,
can anyone help me with an easy one:

- access 2000
I have a combo box with client names. the client names change based on two other combo boxes which do requeries. Unfortunately, I can't get the combo box with the client names to show all clients. I've tried using a command button as well as a "all clients" selection in one of the other combo boxes but I'm not saavy enough to code it properly. Any help would be appreciated.
thanks!

at what time do you want to show ALL clients?

If you have cascading combo boxes, and you want to be able to show all clients vs the filtered combo effect, then IF the clients combo box starts out with ALL clients, the first sub below will do it...
other wise the second option - create a query with all the clients.
With the push of a button, or click on a label, you can set your client box by using something like this

[code=vb]

private sub mycontrol_onclick()
me.mycombo.rowsource = me.mycombo.rowsource
end sub

private sub mycontrol_onclick()
me.mycombo.rowsource = "yourqueryname"
end sub

[code]

not hard right?
J

[BigToe]

I am enlightend. thanks!

hold it *screeech*

i created a command which activates the querry showing all clients (using the second code mentioned), but now the drop down combo boxes don't requery anymore when selecting another criteria. Now all clients are listed the third combo box and the first two combo boxes have no filtering effect. any thoughts?

[JConsulting]

I am enlightend. thanks!

hold it *screeech*

i created a command which activates the querry showing all clients (using the second code mentioned), but now the drop down combo boxes don't requery anymore when selecting another criteria. Now all clients are listed the third combo box and the first two combo boxes have no filtering effect. any thoughts?

can you paste in the after update events for your combo boxes?

[BigToe]

Recall Client Stats is the name of the form.
Clientstemp is the name of the sub form
so far, I have the 3 combo boxes in the header, and the subform in the body of the form.
cboClients contains the list of all clients.
cboSC is one combo box containing a filter criteria
cboWaitlist is the second combo box with another filter criteria

(i was trying to post a print screen of the form but couldn't figure it out)

Something odd I also noticed is that when selecting a client from the list, where that client shares the same last name with others, the first client among those with the same last name will always be displayed; i.e. selecting the third or fourth 'Smith' from the list always brings up the first 'Smith'. I'm not sure if that's relevant or fodder for another post...

In researching this process i realize there's more than one way to achieve the desired result. i'm willing to start from scratch and learn a more efficient way if need be.

thanks for taking the time!!

Private Sub cboClients_AfterUpdate()
    'Moves to client Name text box and
    'finds the record of whatever name is selected in the combo box
 
    DoCmd.ShowAllRecords
    Me!Clientstemp.SetFocus
    DoCmd.FindRecord Me!cboClients
 
    'just moves the highlighted cursor away from the Last Name after selection is made
    DoCmd.GoToControl "middle name"
 
    'Set value of combo box equal to an empty string
    Me!cboClients.Value = ""
 
End Sub
 
Private Sub cboSC_AfterUpdate()
 
    DoCmd.Requery "cboClients"
 
        cboWaitlist = ""
 
cboSC_AfterUpdate_Exit:
    Exit Sub
 
End Sub
 
Private Sub cboWaitlist_AfterUpdate()
 
    DoCmd.Requery "cboClients"
 
    cboSC = ""
 
cboWaitlist_AfterUpdate_Exit:
    Exit Sub
 
End Sub
 
 
 
Private Sub cmdAllClients_Click()
 
    Me.cboClients.RowSource = "all clients"
 
cmdallclients_click_exit:
    Exit Sub
 
End Sub

[BigToe]

another piece to the puzzle:

it would seem my Row Source for cboClients has changed to the 'All Clients' querry - perhaps after creating the 'all clients' command button. it would seem my SQL statement has to include 3 querries for the 3 different search criteria:
clients by 'service coordinator' (5 service coordinators)
clients by 'waitlist' (4 regions)
clients by all

I would also like to add 'all waitlisted' clients

I will splunk further on...