Subform recordsource for searching (1 Viewer)

[rosenn]

I have an unbound form with several controls that I use as criteria for making a custom user query. I would like each combination of options to allow me to assemble an sql statement, then use that to open up a recordset and set the object source of a subform to that recordset. My only problem is I am having a tough time making a subform based on a variable recordset, or sql that I create as a string.

I have been able to leave the subform unbound and use a variable (and a little function) to vary the subform source - but that means I have to create a seperate subform for each query I could possibly have (and create a corresponding query as well). That would clearly work but it seems so 'brute force-ish' if you know what I mean.

Any suggestions?

 

[dcx693]

If the main form has fields that you use to compile a SQL criteria string, just assign that to be the recordsource of the subform.

Use a series of statements that visit each control and create a piece of a SQL string with each. Add it to the next SQL form the next control, etc,..., then assign it to the record source of the subform like this:
Me.subForm.Form.RecordSource=strSQLString.

Only when you need to change the fields displayed or something like that would you normally need to swap out the subform source object. For example, I have a customer and an inventory choice on my form. Based on which one the user selects, I change underlying subform object. Use code like this to handle that situation: Me.subForm.SourceObject=InventorySubForm
That inventory subform displays different fields than the customer subform. Otherwise, if your data only pertains to one subject, you don't need to handle this situation.

 

[rosenn]

I think I do need to do both

I think I know what you mean. I can keep the object source as the same subform and just re-query its recordset with new sql by assigning the me.subform.forms.recordsource = sqlstring. I will try, but the first time I did it that did not work - I will give it a go in the am and if it doesn't work I will post my lines of code.