Complex Dropdown Menu Filtering

[MaliciousMike]

Hello,
I have a form which gets it's records from qryCompleted.
The form shows all records with selected fields in a list.
Each title of the fields is a drop down box which need to update everytime any of the other drop down boxes are updated.

For example, if cmbCompanyName was updated, then all other drop down boxes need to have "select [field] From qry Where [company name] = cmbcompany name".

If cmbcompanyname and cmbregionname is updated, all other boxes need to have "select [field] From qry Where [company name] = cmbcompany name and [region name]=cmbregionname".

and so on and so on.

There's 6 fields that need to have filters on, which i quickly calculated at about "A shitload" of 'if-then-else' statements.

Is there an easier way?

 

[MaliciousMike]

bump.

Sorry.

 

[Kempes]

I have implemented something similar within my db.

Driven from the query you need to set all fields criteria to

Like * & .........your criteria.

EG,

Like * & [Forms]![Myform]![Combo1]

Then on the form for each combo box, event procedure on got focus for each.

Combo1 = ""
Combo1.requery

Event procedure, After Update

Me.Requery

If you need any further help, let me know.

Cheers
Kempes

 

[MaliciousMike]

Hmm..

I don't seem to be getting much luck.

I need all records to show up before filtering.

I've attached a screenshot of the form. Please note that all text in the combo boxes are actually labels in the background, so there is no "default value" set in any combo box.

Private Sub cmbCompanyName_AfterUpdate()
    lblCompanyName.Visible = False
    Me.Requery
End Sub
Private Sub cmbCompanyName_GotFocus()
    cmbCompanyName = ""
    cmbCompanyName.Requery
End Sub

^^ the code i've used for each combo box.

my record source for the form is "SELECT * FROM qryExisting;"

any suggestions?

 

[Kempes]

Could you post a trimmed down version of your db?

 

[MaliciousMike]

Please note... i'm a young developer and it's all test data.
Some records are not for the easily offended

Cheers Kempes

 

[MaliciousMike]

Bump.

Sorry again but i have a deadline soon and i can't figure this out.

 

[Kempes]

Hi,

Our internet was down all afternoon yesterday.

I'll take a quick look at your db.

 

[Kempes]

This now works. The more data you put in, the more impressive it looks.

I've renamed your form to frmcompleted2.

Check the query and check the coding in the form. FrmCompleted.

I've removed loads of coding cos you don't need it.

You will need to put your labels back in but don't have a default value in the combos else it will mess up the filter. You can get the same result by adding a label over the combo box and setting it to visible = false when the box is clicked.

kempes

 

[MaliciousMike]

Legend.

Thanks for your help!

 

[Kempes]

Glad to help.