Setting recordset to Sub Sub Form

[BHo15]

twinnyfo... I would LOVE it if you would jump in on this one.

As you will recall to my prior question, I had two issues... One was setting the recordset to a 2nd subform to a parent form, and the other was getting the 1st subform to filter off of the 2nd subform. We solved the 2nd question, but as it turns out, I am still unable to set the recordset for that 2nd subform.

This is what I used...

Set rs = Forms!frm_Action_Holder.Actions_DS.Form.frm_Responses.Form.RecordsetClone

I got Run-time error 2455
You entered an expression that has an invalid reference to the property Form/Report.

Thoughts?

[BHo15]

Here is an update. I came up with a different solution, and that was to use the table as the recordset instead of the recordsetclone. That worked, but...

What I tried to do was to loop through the recordset, and every time it found a record with the search word in it, then I would grab that ActionID and put it into a string "ActionID = " & rs.Fields("Acti onID") & " OR "

So, when it finished looping through, I would end up with this in my strFilter...
ActionID = 5321 OR ActionID = 5916 OR ActionID = 6133 OR ActionID = 6629...

Once it was all finished, I would strip off the final " OR "

All well and good, but apparently Access does not like REALLY long filter strings. I kept getting errors when I attempted to apply the filter (something about the property being too long).

Of course I finish typing this, I realize that we are now onto a COMPLETELY different question. Sigh. If you need to do your admin work, and move it to a different question, be my guest.

Thanks.

[NeoPa]

No worries about the moderating for now.

What may help is to know that an alternative format in a filter (or WHERE clause) in SQL is :

([FieldA]=X) OR ([FieldA]=Y) OR ([FieldA]=Z)

is functionally equivalent to :

([FieldA] In(X,Y,Z))

The latter format allows far more comparisons before it fails to fit into the property.

[BHo15]

Wow... First twinnyfo, and now NeoPa. What a deal!!!

I like the alternate SQL setup. I’ve never used that. That should give quite a bit more space. I’ll give it a shot.

Thanks.

[NeoPa]

I have no link to anything but filtering can also use the Like keyword. This enables you to compare strings using wildcard and other special characters. This works in VBA as well as in SQL. The In() function can also be used with a SELECT query and the results of that query are used as the contents of the In().

I hope some of these ideas lead you to explore in some new areas.

[BHo15]

I tried the In() today on another DB, and it worked great. Now to try it on the DB in question tomorrow. With that one, when we were using the ActionID = 5267, etc, etc., we got the SQL statement up to 1850 characters before the filter failed. So this method should certainly save the use of a lot of characters, so we’ll just have to see if it is enough. But all in all, your solution is going to be the best one for what I’m trying to do. Thanks for offering it.

[NeoPa]

Always a pleasure. I hope you manage to pick up a few tricks while you're here :-)

[BHo15]

I definitely will. I was a regular user of Bytes 3-5 years ago, but thankfully have been fairly "self-sufficient" since then. But now I have hit all the problems all at once. I appreciate you all helping me out.

[Frinavale]

It seems like a lot of work to loop through a record set to look for rows that contain a search word.

So, I just have a quick question (and I don't even know if it pertains to your current application but..), did you try using the Recordset.Filte r Property of the original record set to get a version of it filtered on your search word?

[BHo15]

Good point. But the issue was that we were trying to find a search word in a sub sub form, and then grab the foreign keys from those records so that we could filter the sub form above it. Hope that makes sense.

[NeoPa]

That's probably over-complicating for now Frinny. I suspect it's also not a helpful approach for this particular issue, but wouldn't swear to that.

Please don't see this as a blow-off. Keep jumping in where you feel you may help. Probably not here on this one if I understand correctly though.