Blank cbo's

[RonnieODIN]

Hi

I am trying to make a query that sorts my records by three cascading cbo's in my main form. At the moment the sub form containing the query is not updated before the third box is filled out.

What I would like is the following:

• When the cbo's are blank all records must be shown.
• The records are filtered as the cbo's are filled out and not just when all are filled out.

My VBA is as follows:

Option Compare Database
Private Sub cboCurrentCity_AfterUpdate()
Me!subMaskiner.Requery
Me!Tekst15 = [Forms]![frmListeMaskiner]![cboCurrentCity]
End Sub
Private Sub cboCurrentCostumer_AfterUpdate()
Me!cboCurrentCountry = ""
Me!cboCurrentCity = ""
Me!cboCurrentCountry.Requery
Me!subMaskiner.Requery
Me!Tekst15 = [Forms]![frmListeMaskiner]![cboCurrentCostumer]
End Sub
Private Sub cboCurrentCountry_AfterUpdate()
Me!cboCurrentCity = ""
Me!cboCurrentCity.Requery
Me!subMaskiner.Requery
Me!Tekst15 = [Forms]![frmListeMaskiner]![cboCurrentCountry]
End Sub

The SQL for one of the cbo's (the other two are equivalent) in the query is as follows:

IIf([Formularer]![frmListeMaskiner]![cboCurrentCostumer]="";"";[Formularer]![frmListeMaskiner]![cboCurrentCostumer])

Hope that someone will able to help me.

 

[vbaInet]

So you don't start meddling with SQL, use the Filter property of the form to filter the subform.

* When all three cbos are Null or "", remove the filter. i.e.

If Len(Nz(cbo1.value & cbo2.value & cbo3.value, & vbnullstring)) = 0 Then
    Me.SubformName.Form.FilterOn = False
Else
    ' otherwise set the filter and turn it on.
End If

Research on the Filter and FilterOn properties.

 

[RonnieODIN]

Okay.

I must admit that I am quet new to Access - so where do I put this code?

Does the filter apply as we go and fill out the different cbos?

 

[vbaInet]

The same place where you have your current code.

Have you read up on those two properties I mentioned?

 

[RonnieODIN]

I have. The idea seems good, I have one issue though. I don't wnat to show he fields in my query that I filter on in my form. Is this procedure still possible? Perhaps including the fields but hiding them on the form?

 

[vbaInet]

The fields you are querying on need to be in the underlying record source. They don't have to be bound to any controls.

 

[RonnieODIN]

One last thing then.

I set the filter like this:

Me.subForm.Control.filter = [Forms]![frmMain]![cboX]

What am I doing wrong?

 

[vbaInet]

Please look at my syntax again. The only bit that needs changing is SubformName and by SubformName I mean the name of the subform control, not the form.

 

[RonnieODIN]

Now it works indivually for the three cbos. Choosing costumers costumers are filtered but choosing costumer country afterwards will filter country for all costumers.

My filter is:

Me!subMaskiner.Form.Filter = "[KundeLand] = [Forms]![frmListeMaskiner]![cboCurrentCountry]"

My code is placed on an AfterUpdate for each of the cbos why I only set and activate the filter here. I have a button clearing the cbos and deactivating the filter. When the form is opened no filter is applied.

Any idea of how to solve the filterissue?

 

[vbaInet]

* You need one function that will build the filter string and apply. This function will be called by all three cbos in their respective After Update events.

* To have a cascading style filter, you need to build your filter string (or criteria) using the AND keyword.

* When the form is opened the After Update event is not fired. What event do you think you should call the function?

 

[RonnieODIN]

GREAT!

I actually did try out the AND keyword, but first wrote "XXX" and "YYY" instead of "XXX and YYY".

It is now working. The last points were not problems just statements. It is all as I whish.

Thanks a lot for your time. It is VERY much appreciated.

 

[vbaInet]

Pleased to hear Ronnie. No problem!