Sometimes it works, sometimes it doesn't

[DCinFRANCE]

I have a simple form, bound to a query. I'm using a combo box to select the records I want displayed and applying a filter. I built the form and it worked beautifully. Save and closed. Re-opened it and it doesn't work anymore. The underlying query is working...I have aggregate variables like DCount that reflect the correctly filtered query, but the continuous forms display in the detail is blank. More strange...if I load the query manually, and make repeated combo box selections on the form, at some point it starts working again.

The combo box is connected to a macro on event After Update:

Setvalue
Item = [Forms]![frmI-1) Training by Entity v2].[FilterOn]
Expression = True
Requery

As I said, sometimes things work, sometimes it doesn't. It is making me certifiable...I've spent a 12 hour day trying to figure this out, finding some vague things googling, but no answers. Please, for the sake of my family, I would thank you biggly for some help.

 

[JHB]

I never use macros (only Event Procedure) so I can't say if yours is correct or not.
Show the SQL-string for the query the form is bound to.
An ex. how it would look like in an Event Procedure if [TheFieldYouWantToFilerOn] it is a number type field in the table.

  Me.Filter = "[TheFieldYouWantToFilerOn]=" & Me.YourComboName
  Me.FilterOn = True

Here if it is a text type field.

  Me.Filter = "[TheFieldYouWantToFilerOn]='" & Me.YourComboName & "'"
  Me.FilterOn = True

 

[DCinFRANCE]

Hi JHB, thanks for the reply.

The Record Source is simply the name of the query (the query on its own gets all the records from the table). So,

Record Source: qryI-1) Training by Entity

I'm using a filter to get the subset of records I want displayed:

Filter: [T_ENTY] = [Forms]![frmI-1) Training by Entity v2]![cboSelEntity]

UPDATE: I might have stumbled on a clue. If, after loading the form, I click on "Toggle Filter" from the Home menu, the display is filled with all records. After that, everything works.

 

[arnelgp]

you need to create a Form with Subform.
The Form (frml-1) will have the combobox.
the form will Not be Bound.

make another Form (continuous Form)
for your Query.

Add this Form (subform) to your Main
Form (frml-1).

Use this macro on the AfterUpdate event
of the combobox:

SetFilter
	Filter Name		FormFilter
	Where Condition=	="[yourFieldToFilter]=" & [yourComboName]
	Control Name		theSubFormName (without any quote)

 

[DCinFRANCE]

Thanks for the reply, arnelgp.

Sure, I know I could as I have in many other cases. I went this route because I learned that I could, and I could avoid another subform to maintain (the list of my subforms is very long).

Anyway, thanks. I was going to go that route when I discovered the "Update" I wrote above.

I got it working by adding a line to the macro to first turn the filter off and then back on again:

SetValue
Item = [Forms]![frmI-1) Training by Entity v2].[FilterOn]
Expression = False
SetValue
Item = [Forms]![frmI-1) Training by Entity v2].[FilterOn]
Expression = True
Requery

Now it works all the time. The only slightly annoying thing is that there is a brief flash as the entire contents appears and then the filter content. However, that is slightly less annoying than the original behavior.