Using combobox to filter form.

[andagain]

HI,

I want my form (bound to my main table of clients) to only show particular records depending on which of four options my user selects in the combobox Cboclientselect (on the same form).

The combo box row source is supplied by Tblclientselect which has 2 cols: typeofclientID (autono., long integer), and typeofclient (text). - I have entered the 4 values of typeofclient (all, active, completer, leaver) in this table. (these 4 values relate to the field Exitstatus, which can have values of either completer or leaver - so ie. I want my form to show 1) All clients in the DB, 2) all clients where exitstatus isnull, 3) all clients with exitstatus of 'completer' and 4) all clients with exitstatus 'leaver'.

From previous posts I gather I need to use:

Private Sub cboclientselect_AfterUpdate()
Me.FilterOn = False
Me.FilterOn = True
End Sub

 

[KenReay]

Better still, make the query to which your form is bound have a criteria of Forms!YourFormName!YourComboName on the ClientType column

in the afterupdate event of the combo, put

Me.Requery

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[andagain]

Sorry Ken, don't really understand.
Can you tell me in general terms the steps I need to take to use the method you say?

(My form is bound to my main table (not a query). I just want to be able to display certain records in my form depending on whether the exitstatus field of a record is leaver, completer or left empty, which are the options I want my user to choose from in a combobox, plus show all records..)

Confused...

Andrew.

 

[KenReay]

Hi

Ok

Remove the All option from the table tblClientSelect

Make the rowsource of the seletion combo box SQL something like:

SELECT TypeOfClient As A, TypeOfClient FROM tblClientSelect
UNION
SELECT "*" As A, "<All>" As B FROM tblClientSelect
ORDER BY TypeOfClient

set the number of columns to 2, and the column widths to 0;3

In the onload event of the form put code like so

If MyComboBox.ListCount > 0 Then
MyComboBox = MyComboBox.Column(0,0)
end if

now click on the builder button to the right of the rocordsource of the form, and make a query, include all of the columns from you Client table, and in the criteria box under the ClientType column, put Like Forms!YourFormName!YourComboName

In the after update event of the combo box put code

Me.Requery

Not I have had to use form names and control names beginning Your.. becuase I do not know your form and control names, you need to sustitute your own form and control names



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[andagain]

Hi Ken,

Have finally got around to having a go at this. I think I am nearly there...I have followed your instructions, though I think my initial explaination of what I wanted was rather unclear.

If can just explain what I have now:

I want to use a Cbox (CboClientselect) to show records according to what value they have in the field ExitStatus (of which row source is a Value List: "Completer";"Early Leaver") in my form ClientData (bound to TblClientData).

I have made the rowsource of Combobx CboClientSelect:

SELECT ExitStatus As A, ExitStatus FROM TblClientData
UNION
SELECT "*" As A, "<All>" As B FROM TblClientData
ORDER BY ExitStatus;

Cols 2, col width 0;3

I have this in the On Load event of my form:

Private Sub Form_Load()
If CboClientselect.ListCount > 0 Then
  CboClientselect = CboClientselect.Column(0, 0)
End If
End Sub

And my form's record source is now set to a query containing all fields from my main table, with the criteria of ExitStatus set to:

[Forms]![ClientData]![CboClientselect]

I have Me.Requery in the afterupdate of my CboClientselect.

So, this kind of appears to work in that my combobox displays the values <All>, Early Leaver, Completer....and selecting Early Leaver or Completer shows records with those values in ExitStatus, but selecting <All> doesn't show any records when it I want it to show all of them....?....

Also, can I get the CboClientselect to show another value 'Active' that when selected will show all records that have field ExitStatus left empty?

And another thing to do is:
I have an unbound listbox on my form (LboClientlist) that displays my client names. The record source of this is:

SELECT TblClientData.FamilyName, TblClientData.FirstName, TblClientData.ClientID
FROM TblClientData
ORDER BY TblClientData.FamilyName;

How do modify this query to only show clients as reflected in the choice made in the CboClientselect????

Sorry for the long post. I am not that clued up on this kind of stuff in Access. Hope you can help Ken!

Thanks

Andrew.

 

[KenReay]

Hi

"And my form's record source is now set to a query containing all fields from my main table, with the criteria of ExitStatus set to:

[Forms]![ClientData]![CboClientselect]"

make it

LIKE [Forms]![ClientData]![CboClientselect]

Explanation it will never be equal to "*" (the value of the combo when All is selected), but the wildcard Like * is saying effectively show me all

OK?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK