Filter a form by combo box selection

A

Andy Roberts

I have a continuous form based on a query which pulls TenderID and
TenderSite from tblTenders and ClientName (linked by ClientID) from
tblClients, so I get...

2134 London Client1
2163 Liverpool Client 2
2222 Cardiff Client 1
2345 Derby Client 3

no problem so far.

I want a txt box in the form header above the Tender ID so I can just type a
tender number and the form will filter the results to only show that tender.

I also want a cbo box above the client so I can select a client from the
dropdown (which isn't a problem as this works) and the form will filter
showing only those tenders for that selected client.

Pulling together the txt box and cbo box is fine, but the code required for
the after_update events is driving me insane!

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
 
K

Ken Snell \(MVP\)

Change the form's RecordSource query so that it has a WHERE clause in it
like this:

WHERE ClientID = Forms!NameOfYourForm!ComboBoxName
OR Forms!NameOfYourForm!ComboBoxName Is Null

Then use this code for the combo box's AfterUpdate query:

Private Sub ComboBoxName_AfterUpdate()
Me.Requery
End Sub
 
A

Andy Roberts

Thanks Ken

I have this as the Row Source for the cbo..
SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM
[qryFindClient] ORDER BY [ClientName];

The Record Source for the form is currently set to qryT3TenderList

I've tried to add the following, but get an error...
WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null

What should the record source actually say?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
 
K

Ken Snell \(MVP\)

The WHERE clause looks correct in syntax.... what error are you getting?

Can you post the entire SQL statement for the qryT3TenderList query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Andy Roberts said:
Thanks Ken

I have this as the Row Source for the cbo..
SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM
[qryFindClient] ORDER BY [ClientName];

The Record Source for the form is currently set to qryT3TenderList

I've tried to add the following, but get an error...
WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null

What should the record source actually say?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Ken Snell (MVP) said:
Change the form's RecordSource query so that it has a WHERE clause in it
like this:

WHERE ClientID = Forms!NameOfYourForm!ComboBoxName
OR Forms!NameOfYourForm!ComboBoxName Is Null

Then use this code for the combo box's AfterUpdate query:

Private Sub ComboBoxName_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
Click to expand...
Click to expand...
 
A

Andy Roberts

qryT3TenderList WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null


I know this is wrong!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Ken Snell (MVP) said:
The WHERE clause looks correct in syntax.... what error are you getting?

Can you post the entire SQL statement for the qryT3TenderList query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Andy Roberts said:
Thanks Ken

I have this as the Row Source for the cbo..
SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM
[qryFindClient] ORDER BY [ClientName];

The Record Source for the form is currently set to qryT3TenderList

I've tried to add the following, but get an error...
WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null

What should the record source actually say?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Ken Snell (MVP) said:
Change the form's RecordSource query so that it has a WHERE clause in it
like this:

WHERE ClientID = Forms!NameOfYourForm!ComboBoxName
OR Forms!NameOfYourForm!ComboBoxName Is Null

Then use this code for the combo box's AfterUpdate query:

Private Sub ComboBoxName_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a continuous form based on a query which pulls TenderID and
TenderSite from tblTenders and ClientName (linked by ClientID) from
tblClients, so I get...

2134 London Client1
2163 Liverpool Client 2
2222 Cardiff Client 1
2345 Derby Client 3

no problem so far.

I want a txt box in the form header above the Tender ID so I can just
type a tender number and the form will filter the results to only show
that tender.

I also want a cbo box above the client so I can select a client from
the dropdown (which isn't a problem as this works) and the form will
filter showing only those tenders for that selected client.

Pulling together the txt box and cbo box is fine, but the code required
for the after_update events is driving me insane!

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Yes, it's wrong. You need to add the WHERE clause to the query itself.

Open the qryT3TenderList query in design view. Under the ClientID field,
type this expression in the WHERE: box:
Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null


Save and close the query.

Change your form's RecordSource back to qryT3TenderList and then save the
form.

Now try it.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Andy Roberts said:
qryT3TenderList WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null


I know this is wrong!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Ken Snell (MVP) said:
The WHERE clause looks correct in syntax.... what error are you getting?

Can you post the entire SQL statement for the qryT3TenderList query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Andy Roberts said:
Thanks Ken

I have this as the Row Source for the cbo..
SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM
[qryFindClient] ORDER BY [ClientName];

The Record Source for the form is currently set to qryT3TenderList

I've tried to add the following, but get an error...
WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null

What should the record source actually say?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Change the form's RecordSource query so that it has a WHERE clause in
it like this:

WHERE ClientID = Forms!NameOfYourForm!ComboBoxName
OR Forms!NameOfYourForm!ComboBoxName Is Null

Then use this code for the combo box's AfterUpdate query:

Private Sub ComboBoxName_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a continuous form based on a query which pulls TenderID and
TenderSite from tblTenders and ClientName (linked by ClientID) from
tblClients, so I get...

2134 London Client1
2163 Liverpool Client 2
2222 Cardiff Client 1
2345 Derby Client 3

no problem so far.

I want a txt box in the form header above the Tender ID so I can just
type a tender number and the form will filter the results to only show
that tender.

I also want a cbo box above the client so I can select a client from
the dropdown (which isn't a problem as this works) and the form will
filter showing only those tenders for that selected client.

Pulling together the txt box and cbo box is fine, but the code
required for the after_update events is driving me insane!

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Click to expand...
Click to expand...
Click to expand...
 
A

Andy Roberts

Ken

Thanks for your help. This seems to have sorted it.

Can I force the cbo to be clear when the form loads or add a button to clear
the cbo (ie remove the filter)

Can the same thing be done with a txt box. e.g. I have a list of job numbers
and can I type a number into an unbound txt box to filter based on that
number?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Ken Snell (MVP) said:
Yes, it's wrong. You need to add the WHERE clause to the query itself.

Open the qryT3TenderList query in design view. Under the ClientID field,
type this expression in the WHERE: box:
Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null


Save and close the query.

Change your form's RecordSource back to qryT3TenderList and then save the
form.

Now try it.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Andy Roberts said:
qryT3TenderList WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null


I know this is wrong!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Ken Snell (MVP) said:
The WHERE clause looks correct in syntax.... what error are you getting?

Can you post the entire SQL statement for the qryT3TenderList query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thanks Ken

I have this as the Row Source for the cbo..
SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM
[qryFindClient] ORDER BY [ClientName];

The Record Source for the form is currently set to qryT3TenderList

I've tried to add the following, but get an error...
WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null

What should the record source actually say?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Change the form's RecordSource query so that it has a WHERE clause in
it like this:

WHERE ClientID = Forms!NameOfYourForm!ComboBoxName
OR Forms!NameOfYourForm!ComboBoxName Is Null

Then use this code for the combo box's AfterUpdate query:

Private Sub ComboBoxName_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a continuous form based on a query which pulls TenderID and
TenderSite from tblTenders and ClientName (linked by ClientID) from
tblClients, so I get...

2134 London Client1
2163 Liverpool Client 2
2222 Cardiff Client 1
2345 Derby Client 3

no problem so far.

I want a txt box in the form header above the Tender ID so I can just
type a tender number and the form will filter the results to only
show that tender.

I also want a cbo box above the client so I can select a client from
the dropdown (which isn't a problem as this works) and the form will
filter showing only those tenders for that selected client.

Pulling together the txt box and cbo box is fine, but the code
required for the after_update events is driving me insane!

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

When the form opens, an unbound combo box control will have no value (i.e.,
it'll be cleared) unless you run code / macro to assign a value to it.
However, you can use the form's Load event to set the combo box to Null and
then requery the form's RecordSource.

Private Sub Form_Load()
Me.cboFilterClient.Value = Null
Me.Requery
End Sub


You also can add a button that would clear the combo box, and use its Click
event to run this code:

Private Sub CommandButtonName_Click()
Me.cboFilterClient.Value = Null
Me.Requery
End Sub


Regarding the use of a textbox, the answer is yes. You can use it the same
way the combo box is being used. Just have your query reference it instead
of the combo box if you want to use the text box in place of the combo box.
If you want to use both, the query gets a bit more tricky, but it's doable.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Andy Roberts said:
Ken

Thanks for your help. This seems to have sorted it.

Can I force the cbo to be clear when the form loads or add a button to
clear the cbo (ie remove the filter)

Can the same thing be done with a txt box. e.g. I have a list of job
numbers and can I type a number into an unbound txt box to filter based on
that number?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Ken Snell (MVP) said:
Yes, it's wrong. You need to add the WHERE clause to the query itself.

Open the qryT3TenderList query in design view. Under the ClientID field,
type this expression in the WHERE: box:
Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null


Save and close the query.

Change your form's RecordSource back to qryT3TenderList and then save the
form.

Now try it.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Andy Roberts said:
qryT3TenderList WHERE ClientID = Forms!frmT3TenderList!cboFilterClient
OR Forms!frmT3TenderList!cboFilterClient Is Null


I know this is wrong!
--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
The WHERE clause looks correct in syntax.... what error are you
getting?

Can you post the entire SQL statement for the qryT3TenderList query?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Thanks Ken

I have this as the Row Source for the cbo..
SELECT [qryFindClient].[ClientID], [qryFindClient].[ClientName] FROM
[qryFindClient] ORDER BY [ClientName];

The Record Source for the form is currently set to qryT3TenderList

I've tried to add the following, but get an error...
WHERE ClientID = Forms!frmT3TenderList!cboFilterClient OR
Forms!frmT3TenderList!cboFilterClient Is Null

What should the record source actually say?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Change the form's RecordSource query so that it has a WHERE clause in
it like this:

WHERE ClientID = Forms!NameOfYourForm!ComboBoxName
OR Forms!NameOfYourForm!ComboBoxName Is Null

Then use this code for the combo box's AfterUpdate query:

Private Sub ComboBoxName_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a continuous form based on a query which pulls TenderID and
TenderSite from tblTenders and ClientName (linked by ClientID) from
tblClients, so I get...

2134 London Client1
2163 Liverpool Client 2
2222 Cardiff Client 1
2345 Derby Client 3

no problem so far.

I want a txt box in the form header above the Tender ID so I can
just type a tender number and the form will filter the results to
only show that tender.

I also want a cbo box above the client so I can select a client from
the dropdown (which isn't a problem as this works) and the form will
filter showing only those tenders for that selected client.

Pulling together the txt box and cbo box is fine, but the code
required for the after_update events is driving me insane!

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autonumber / Incrementing Value Boo Boo 1
Filtering by Dates 4
Creating a Search using a combo box and then creating a report. 4
Unbound Refresh Problem 4
Show / Hide Form 7
Running a query using a button - problem 3
Form Objects Disappear When Filter Returns No Records 4
Subform If Function 2

Top