Open form with dynamic record source (query)

[memaxt]

Hi there,

I have a query "qry_tmp" that changes depending on the sql statement, i'm looking to open the query in a popup form and display the results in Datasheet view.

But as the qry_temp changes the fields change as they may be less / more columns depending on my sql statement,

Is it possible to open the form with qry_temp as the record source that can adjust the form next time it opens to have the relevant number columns?

Many thanks
Max

 

[MarkK]

This is possible but takes intermediate to advanced coding skills. You can add a series of textboxes to a form in design view. Then, write code to enumerate the fields in the Form.Recordset object when the form opens, and set the Textbox.ControlSource properties of the previously added controls to the names of the fields in the Recordset. This way you can dynamically bind the form to the whatever field names appear in the Form.Recordset.

Makes sense? But you gotta know a thing or two. What might be easier is just open the query in datasheet view, like . . .

DoCmd.OpenQuery "qryYourQueryName"

Is that good enough, or do you need to have a form underneath?

 

[Pyro]

Create a subform on your popup that completely fills the bounds of the popup form. Subforms can have a query set as the source object, and will just display the query as a datasheet. See example.

(apologies if the example is a little messy - i have a grumpy one year old sitting on my lap demanding to show me how to use the keyboard )

 

[Pyro]

Note that in my example, there is only one query, and the two buttons manipulate the SQL within it and update the source object of the sfrm.

 

[memaxt]

Thank you both for your replies!

I've opted to use Pyro's sample database that worked an absolute treat! Many thanks!!!

 

[jdraw]

memaxt,

Further to advice from others, a good set of tutorials regarding vba and SQL is at Martin Green's site. See the Dynamic Reports for ideas.

Good luck.

After posting: Did not see your post #5 before posting. The links are good references for future.