Access open form using various SQL queries

Murray Brown
Murray Brown used Ask the Experts™

I want to create a multi purpose form that I can load using any SQL query

Is this possible?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Can you elaborate a bit on this
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
You can do this, but the question is why? A form should be for a specific purpose, and therefore your bound datasource should be know before hand.

I did this with an old application I wrote for one of my aerospace companies. They wanted to do something like ad-hoc queries but didn't want to expose the query interface to users. I wrote a small form where the user could select the "View" they wanted to use (queries that I created for all potential combinations of requests), and provided a way for them to add filters (a list of all the fields in the selected View, with a textbox for directly entering the values to filter on). I then returned the data into a datasheet subform. It took a LOT of effort to do, and after tracking usage on it for about a year, I found out that very, very few people ever used it. When I asked them, they always said "We just go to the page where we want to find the data and look it up there".
John TsioumprisSoftware & Systems Engineer
Based on @Scott's comment it depends on what exactly you need/want.
If its for vieweing then its easy...either a continuous form with a lot unbound set the RecordSource/ControlSource on the fly and off you go...if you want to enter the "dark side" then you would go the ActiveX world...ListView or FlexGrid are super for such kind of work...
Now if its for input and validation ...and...and....then it depends on the complexity can still go the 1st way but this time besides the Controlsource you need to apply events to the controls...this is done with Event sinking which will make the "proper" reactions according to the situations....and this can go a lot of way...which extra trickery...
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
The other thing you can do is place a subform on the form, and using Scott's technique of selecting the query or table you want to view, from a list, could simply set the sourceobject of the subform to the query or table name.  This automatically sets it up as a datasheet and is extremely easy to implement.

Having said that, I, like so many others, wonder what the use case is for doing this.  I rarely display tables to user, but queries might be a different matter.
Murray Developer



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial