Assigned Button to Open Query Window

[StacyStacy]

How do I program a button that opens the normal Query, Forms & Report windows? I have set up a switchboard to control what the users see. I have also hid the tables due to finding out that the users directly input data into the tables.

I also found a thread that is similar to the resolution that I am looking for, but is not exactly what I am looking for. Here's the link for that thread:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=43102&highlight=button+that+opens+the+query+window

Any suggestions?
Thanks.:

 

[JKpeus]

I believe that you are refering to the DoCmd.OpenQuery DoCmd.OpenForm and DoCmd.OpenReport methods.

Do a search for "DoCmd" in the Access help file.

HTH,
Jason

 

[StacyStacy]

To be more specific, I need to "write" the code to open only query, form and reports window.

Thanks

 

[jfgambit]

Stacy:

Take a look at the Northwind sample database that is included with your copy of Access (if it was not installed at the time of load then you can add it from the installation disk or have your IS department add it to your system). It has a number of examples of opening forms, queries and reports from command buttons, as well as examples of opening these items using filters.

 

[StacyStacy]

Similar. I want a button to open the query window, the forms window and the reports window.

I don't want to create a list of queries, reports nor forms. My goal is to hide the Table 'Window' and disable the toolbar functions.

Thanks

 

[jfgambit]

You mean you want to open the Query, Forms and Reports in Design view? So that Queries, forms and Reports can be built by the Users?

 

[StacyStacy]

Yes and ... whereby they can see everything that was created as well. Just like in the Northwinds db.

 

[jbg]

I don't know of any way to just hide the tables window. You may want to think about hiding the entire databse window, and creat a few combo boxes on your forms that list al of the queries/reports/etc, along with a command button to open the selected object in design view. To have a combo box (cmbQueries) list all queries, use the following in its row source:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Not Like "~*" And (MsysObjects.Name) Not Like "MSys*") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;

then have a command button with the following code:


Dim stDocName As String

If IsNull(Me!cmbQueries) Then
Call Message("Please select a query to edit.")
Else
stDocName = Me!cmbQueries
DoCmd.OpenQuery stDocName, AcFormView.acDesign, AcOpenDataMode.acEdit
End If

This can be done for queries/reports/etc.

Just an option...

 

[Autoeng]

Stacy:

I hope you don't mind me putting words into your mouth but I'm going to try to clarify your problem so that you can hopefully get a solution. I hope I get the description right. Don't hesitate to point out that I don't have a clue as to what I am talking about though.

I've been working with Stacy on other issues related to this and perhaps I can spotlight the problem a little better.

Stacy's users need design access but she wants to control that as much as she can. For example, The users need to create queries on the fly and although Stacy can create base queries for them she won't be able to cover all of the bases. Once she releases the db she doesn't want to have to deal with a lot of maintenance of creating new base queries for the users to use. Therefore, she is going to let them create their own queries, forms and reports.

Now she found out that her users were inputting records into the tables directly instead of using the forms. We hid the tool and menu bars and changed the tables to hidden. By hiding the tool and menu bars the right mouse click was disabled so they can't get into design view. Stacy doesn't want them to either as that would mean that they could get into the standard forms, roports and tables and screw them up.

What she wants to do is create buttons that take them to design view for forms, reports and queries but with the tables hidden that doesn't help. My suggestion was to make base queries that just duplicate the recordset of the table and have the users use them for building the forms, reports and queries.

What other ways are there to get what she needs?

 

[StacyStacy]

Well said Autoeng! Thank you.

Although, I do want to create a button that will reside on the switchboard to show all of created queries and give the option of creating a new query as well.

In addition, I want to create a button that will reside on the swithcboard to show all of the forms in addition to giving them the ability to create a new form as well.

Basically, I will allow them access to the database menu ONLY TO SEE THE QUERIERS OR FORMS OR REPORTS, not the tables, macros, etc, based on which button was pushed (GO TO QUERIES or GO TO FORMS or GO TO REPORTS)

Thanks.

 

[Autoeng]

Is it possible to open a new query / form / report in design view with the OpenObject command?

Like DoCmd.OpenQuery " ", acViewDesign,

If anyone knows if this is possible or the correct syntax I'm sure that Stacy would appreciate a reply.

 

[jbg]

I believe this works, but i haven't tried it in a while...

DoCmd.RunCommand acCmdNewObjectQuery

 

[Autoeng]

Perfect jbg! I knew someone knew the answer.

 

[Mark Wild]

I've had a similar problem to Stacey recently. The way I got round it was to have the normal BE/FE set up with security to allow inputs, using forms etc but no access to the objects.

I then created the "userDevelop" db which I set up another workgroup for. In this, users can write queries, reports etc on the tables (linked from the BE). However, I have removed their ability to create/update/delete the data.

So far this has caused me no problems and allows me to control the data but let users "play".

 

[StacyStacy]

Thank you! I do appreciate your help.

 

[StacyStacy]

There's one more thing ... I would like the ability to see all forms, and reports created in addition to having the ability to create a new report. Does anyone know how to accomplish this task?

Thanks.