VBA dataset as form recordsource?

[Les Isaacs]

Hi All
I need to apply a dataset (created in a vba sub) as a filter for a form. The normal recordsource for the form is a query ([qry_practices]), and until now the filter was applied as another query ([qry_eoys_to_do]) - so I had:

Me.RecordSource = "SELECT * FROM [qry practices] WHERE [prac name] in (SELECT [prac Name] FROM [qry_eoys_to_do])"

That was all fine, but now I need to provide for more complex options for the filter, which I can do very well with a sub that uses various Inputbox options followed by a Case statement to creates a recordset (called rsHMRCbatch) which I then need to apply as a filter - so something like:

Me.RecordSource = "SELECT * FROM [qry_practices] WHERE [prac name] in (SELECT [prac Name] FROM [rsHMRCbatch])"

... but of course that doesn't work
I'm sure it's just a syntax issue ... and hope someone can help

Thanks in advance
Les

 

[MSAccessRookie]

Hi All
I need to apply a dataset (created in a vba sub) as a filter for a form. The normal recordsource for the form is a query ([qry_practices]), and until now the filter was applied as another query ([qry_eoys_to_do]) - so I had:

Me.RecordSource = "SELECT * FROM [qry practices] WHERE [prac name] in (SELECT [prac Name] FROM [qry_eoys_to_do])"

That was all fine, but now I need to provide for more complex options for the filter, which I can do very well with a sub that uses various Inputbox options followed by a Case statement to creates a recordset (called rsHMRCbatch) which I then need to apply as a filter - so something like:

Me.RecordSource = "SELECT * FROM [qry_practices] WHERE [prac name] in (SELECT [prac Name] FROM [rsHMRCbatch])"

... but of course that doesn't work
I'm sure it's just a syntax issue ... and hope someone can help

Thanks in advance
Les

I don't think you can do what you want, at least not the way that you are thinking that you can. An alternative might be to create a VBA Function of Type String that takes [Prac Name] as a parameter and returns either [Prac Name] or "" based on whether [Prac Name] is to be included as a part of the Results.

You could implement it in the following manner:

SELECT * FROM [qry practices] WHERE [prac name]=theFunct([Prac Name])

Would something like this serve your purposes?

-- Rookie

 

[Les Isaacs]

Hi Rookie

Thanks for your reply. I think what you suggest might well suit my purpose, but I can't quite see how to do it
Ultimately I need the form to be restricted to the records that meet the filter criteria - i.e. where [prac name] exists in the recordset rsHMRCbatch that is created in the sub. How would I do that?

Thanks again for your help.
Les

 

[boblarson]

Is the recordset you are building so complex that it can't be alternatively created by a query and appended to a temporary table? Sometimes we can try to get too fancy with things and can suffer issues like this which may not be necessary and overkill.

 

[Les Isaacs]

Hi Bob
I absolutely agree - there have been times when I've really gone off on a tangent and completely overlooked the more simple and obvious alternative :banghead:
The issues in this case are that I need to:

1. allow the user to provide various parameters at runtime, and then cater for the numerous combinations of those parameters
2. make it easy for other developers in the future to maintain the filter design

I did start to amend the existing filter query ([qry_eoys_to_do]) but it started to look pretty horrible - and difficult for me to follow, never mind a future developer

So that's it: any ideas?
Thanks again
Les

 

[Beetle]

Why not provide your users a form with as many text or combo boxes as necessary to allow the users to enter or select whatever criteria they need. Then, build a criteria string in code based on the data in those controls. You can either apply this criteria string as a filter to the form's record source, or, if dealing with a large amount of data you can rewrite the whole SQL string in code and reset the Record Source using the new SQL.

As it is it sounds like you're popping up a series of multiple Input Boxes, one after another, for the users to enter data. Seems like it would be simpler if they just had a form where they could enter whatever criteria necessary then run the search. You can find an example of this type of dynamic search form here.

 

[Les Isaacs]

Hi Rookie
I've been thinking more about your suggestion, and am still trying to get my head round it

I'm not in the office now, but is it a case of writing the function, say EOYPractice (strPractice), that opens the filter recordset that I now have (rsHMRCbatch) and then returns [prac name] if strPractice exists in rsHMRCbatch and "" otherwise? Then I can set

SELECT * FROM [qry practices] WHERE [prac name]=EOYPractice ([Prac Name])

Is that it ... and if so, is "" definitely the right thing to return if strPractice isn't in rsHMRCbatch?

Thanks again
Les

 

[Les Isaacs]

Hi Sean
Thanks for your reply.
The new form is probably the way to go, and the link you gave is excellent: problem is I need to have this done today , and will have to either implement Rookie's suggestion - which I think I can do quickly, or stick with the existing query solution and do the form when I have time .
Thanks again
Les

 

[Les Isaacs]

Hi All

It's done

In fact I ended up amending the initial recordsource for the form, using a couple of new functions. The suggestions received here were very helpful (partly for prompting me to go back to basics ), so many thanks to all of you.

Les