Filter On Condition

[FARAZ QURESHI]

I have 2 Tables:

Branch: <Fields>
1. ID
2. Country
3. Branch

Sales: <Fields>
1. ID
2. Branch (Linked & looked up from the Branch Table)
3. Sales

Now the form "Sales" has the objects:
1. ID
2. Country (Unbound ComboBox With Row Source Being "SELECT DISTINCT
Branch.Country FROM Branch")
3. Branch (Combo Box)
4. Sales

I want the Branch ComboBox on the form to be showing all the Branches if no
Country is selected, however filtered cascading ComboBox pertaining to the
Country Selected if there is any selected so.

Following is the code I have tried but it results to a blank list of
Branches unless I select a Country.

Option Compare Database
Option Explicit
Private Sub Branch_GotFocus()
If Country = NULL Then
Branch.RowSource = "SELECT [Branch].[ID], [Branch].[Branch] FROM Branch"
Else
Branch.RowSource = "SELECT [Branch].[ID], [Branch].[Branch] FROM Branch
WHERE [Branch].[Country] = Country.Value"
End If
End Sub

What could be the error?

 

[Dale Fye]

Set the RowSource of the Branch combo box to:

SELECT Branch
FROM tbl_Branch
WHERE [Country] = Forms!FormName.cboCountry
OR Forms!FormName.cboCountry IS NULL

If I may make a suggestion. You need to be using a naming convention for
all of the objects in your database. There are a couple of standards
(Google on VB +object +naming +convention). Using naming conventions makes
it easier to identify whether you are refering to a table, form, textbox,
combo box, or whatever other object you are using. It is easy to do this,
and makes reading code significantly easier.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.