By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,318 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Setting recordset to Sub Sub Form

BHo15
100+
P: 135
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...
Expand|Select|Wrap|Line Numbers
  1. 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?
Mar 17 '18 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. ([FieldA]=X) OR ([FieldA]=Y) OR ([FieldA]=Z)
is functionally equivalent to :
Expand|Select|Wrap|Line Numbers
  1. ([FieldA] In(X,Y,Z))
The latter format allows far more comparisons before it fails to fit into the property.

Share this Question
Share on Google+
10 Replies


BHo15
100+
P: 135
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("ActionID") & " 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.
Mar 17 '18 #2

NeoPa
Expert Mod 15k+
P: 31,419
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 :
Expand|Select|Wrap|Line Numbers
  1. ([FieldA]=X) OR ([FieldA]=Y) OR ([FieldA]=Z)
is functionally equivalent to :
Expand|Select|Wrap|Line Numbers
  1. ([FieldA] In(X,Y,Z))
The latter format allows far more comparisons before it fails to fit into the property.
Mar 17 '18 #3

BHo15
100+
P: 135
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.
Mar 18 '18 #4

NeoPa
Expert Mod 15k+
P: 31,419
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.
Mar 18 '18 #5

BHo15
100+
P: 135
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.
Mar 19 '18 #6

NeoPa
Expert Mod 15k+
P: 31,419
Always a pleasure. I hope you manage to pick up a few tricks while you're here :-)
Mar 19 '18 #7

BHo15
100+
P: 135
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.
Mar 19 '18 #8

Frinavale
Expert Mod 5K+
P: 9,731
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.Filter Property of the original record set to get a version of it filtered on your search word?
Mar 20 '18 #9

BHo15
100+
P: 135
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.
Mar 20 '18 #10

NeoPa
Expert Mod 15k+
P: 31,419
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.
Mar 21 '18 #11

Post your reply

Sign in to post your reply or Sign up for a free account.