How to create a form based on a query results in vba 1

[MISMonkey]

I have a list of querys (all different) and a utility that allows a user to pick a query from the list. I want the user to be able to view the results for the query they have seleceted whilst maintaining control over the screen. I don't want them to be able to edit the query for example. Because you dont have pop up or modal setting for a query I'm thinking that I need to create a datasheet form on the fly based on the specified query. Struggling to find a starting point to do this. Most FAQs and threads seem to cover creating a template form first but this is no good in this case because each query is different.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[MajP]

Open it acReadOnly?

 

[MISMonkey]

Cheers MajP.

Maybe I should be clearer. The user selects a pre written query from a Main form. I want the Main form to remain open. The Main form is pop-up and modal so keeps the focus. when a user has selected a query I want them to be able to view the results - if I open the query (even as read only) the query window sits behind the Main form. I want to build a form on the fly so that I can then set the properties of the form to be popup and modal and sit on top of the Main form.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[jmasm]

How to add a form to a query step by step
1 - Create a blank form.
2 - Open the form in mode view.
3 - Form disign tools select design then Property sheet.
4 - In Property sheet select Data Record Source.
5 - Select the query or table that you wish.

In a form create a button then create this vba code:

- Docmd.openForm "name of your form"

Good luck!!!

 

[MISMonkey]

Thanks - if you could supply the vba for steps 1 to 5 then you would have answered my question!

Simplest way to think of this would be that I want to programmatically be able to recreate the actions that invoking the form wizard would give you (i.e you give it a query as the record source and tell it you want it to be a datasheet style and hey presto there is a form that looks like a spreadsheet displayed).

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[soccercook44]

Can you have pre-existing forms that open upon the users choice? If so, set up your forms using the different querys as your datasource and you should be good to go. Just have the forms open when the user selects the correct query from your list.

 

[MISMonkey]

Thanks soccercook44,

I'm trying to keep the ongoing development down and the flexibility up. Your way would require the creation and storage of hundreds of forms potentially (as this dbase grows). I write a query or report and place it in this central dbase for end users to then run (adds consistency in the department and doesn't require a massive team to support and distribute new reports). I'd choose this as a last resort but a resort non the less so thankyou.

I love deadlines. I like the whooshing sound they make as they fly by. (Douglas Adams)

 

[jmasm]

You need the form for what ?

 

[dhookom]

I think you are all making this too difficult. You can create a main form with a subform control (sfrmControl) on it. Add a combo box to the main form for users to select a table name. Add code to the after update event of the combo box:

Private Sub cboTables_AfterUpdate()
    Me.sfrmControl.SourceObject = "Table." & Me.cboTables
End Sub

I would probably create a table of my select queries (row source of combo box) and change the code to:

Private Sub cboTables_AfterUpdate()
    Me.sfrmControl.SourceObject = "Query." & Me.cboTables
End Sub

I would avoid basing the Row Source of the combo box on the Msysobjects table since it might contain action queries or stuff you don't want in the combo box.


Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Just put a combo box in the header of the main form with the query names in it. Make the rest of the main form a large subform that can grow. Then simply

Private Sub cmboQueries_AfterUpdate()
Me.subFrmQueries.SourceObject = "Query." & Me.cmboQueries
Me.subFrmQueries.Locked = True
End Sub

 

[MajP]

Sorry, I was not trying to recreate what Duane said. I missed his post it says the same thing. Here is a demo using Northwind

http://www.4shared.com/document/MMx7j0H8/Select_Queries.html

 

[dhookom]

I'll recreate what MajP said ;-) with another demo.

Duane
Hook'D on Access
MS Access MVP