Query with hidden field drops the field.

[Jen]

I filter records by sending values from an unbound textbox like this:

stLinkCriteria = "forklaring Like '*" &
[Forms]![startform]![txt_bladdra_per_fritext] & "*'"

The problem is that the fields "forklaring" is in a subform of the form that
should show the filtered records. There can be many "forklaring" fields in
the subform embedded on the main form as they are in datasheet view. I can't
have "forklaring" in the mainforms query because then I get duplicate
records no matter if I have "Select distinctrow" in the main forms query.

If I put the field "forklaring" in the mainforms query and uncheck "Show" in
the Querygrid then everything works fine, I can seek values from the
"forklaring" and doesn't get any duplicates of the records shown in the main
form. However when I restart the database the "forklaring" with the
unchecked "Show" has disappeared from the query alltogether, thus prompting
for "forklaring" when I try to filter the records again.

Tried also to bind a subquery to the main query with the "forklaring" fields
but with no success.

Jen.

 

[Ray]

Hi Jen,

The problem is that you are not referencing your subform
in your criteria. You are referencing your main form.

Instead of:

stLinkCriteria = "forklaring Like '*" &
[Forms]![startform]![txt_bladdra_per_fritext] & "*'"

Try this:

stLinkCriteria = "forklaring Like '*" &
[Forms]![startform]!
[NameOfSubFormObjectOnYourMainForm].Form!
[txt_bladdra_per_fritext] & "*'"

The name of the subform object is the name you gave the
object on you main form. Usually it's the name of the
subform itself. Also, I may have my exclamation mark or
period wrong I don't remember the syntax off hand right
now. But you get the idea.

Ray

 

[Jen]

Hello Ray and thanks for your respond. However I think I explained the
situation poorly (I like to think that it's because of my mother-tung is
swedish, not english .

The field "forklaring" is in a subform in a (main)form that the
stLinkCriteria.. opens. "txt_bladdra_per_fritext" is on the form from where
I open this (main)form with the subform containing the values being
filtered. I try to show this in "graphic":

-------------------------------------- --------------------------
----------------
The form containing the textbox | ----> | (main)form containing
the subform
sending the parameter in stlinkcriteria | | with the field
"forklaring" being filtered
-------------------------------------- ---------------------------