Disable the Nav Bar - with a twist

[Ineedcoffee]

Hi,

Ok, long story short. I need to be able to disable the Navigation Bar (so users can not mess about) however still allow users to create and run query's.

I thought of a way of creating a query button that would open a form that would list all of the available user created query's. However I have no idea where to begin on something like this.

Any help would be greatly appreciated.


Ineedcoffee

 

[vbaInet]

What you're attempting to do is creating your own Navigation Pane which is basically reinventing the wheel. Well, to put it simply, unless you know what you're doing it's a pretty huge task.

Nevertheless, I will give you some pointers.

1. Hide the nav pane in the Startup Options or do it in code, former preferred.
2. To get the list of Queries you can put this as the row source of a listbox:

SELECT MsysObjects.Name 
FROM MsysObjects
WHERE (((Left([Name],1))<>"~") AND ((MsysObjects.Type)=5))
ORDER BY MsysObjects.Name;

3. Use DoCmd.DeleteObject acQuery, "QueryName" to delete a query from the db
4. You probably need to trap for errors when a user wants to rename a query
5. How can a user rename the query? I will leave that one for you to source out.
6. When a user creates a new query, you should ask for an alias name which is what you would set the Description of the querydef to so that that name displays in the listbox. Obviously this would require you ignore point 2.

There's more to this list but I think I've given you enough.

Happy coding!

 

[Simon_MT]

VbaInet,

What I would like to do is customise the Navigation Bar with links into a Web using
functions within Modules, is that possible.

Simon

 

[vbaInet]

Hey Simon, I am of the opinion that customising the nav pane only goes as far as creating categories for Access objects, hiding/unhiding the object etc... basically what is in this link:

http://office.microsoft.com/en-us/a...-pane-HA010234560.aspx?pid=CH100645691033#BM4

If you're thinking of having hyperlinks on there, I can only think of replacing the nav pane with a subform that will list the necessary objects in categories plus list the links under a Useful Links category. Or save yourself the hassle and display your links in a form using a listbox (which you're probably doing now).

 

[gemma-the-husky]

why not give users an mde, and maybe lock down the database window

wouldnt that limit what they can do sufficiently?

 

[Simon_MT]

I want the Navigation Panel but I would like to customise it, the other alternative would be to create a Free standing (Unbound) SubForm. The disadvantage of the SubForm is that you would need to insert it into every Form.

Simon

 

[Ineedcoffee]

Hi vbaInet,

Thats great, I will have a look at doing this later this afternoon. The only thing I can see wrong with what you have said is that the code used will list all query's on the database not just the user created ones.

I will have a look at seeing if I can only call those that are in the Unassigned Category of the Nav Bar. Really not sure if I can do this but will be fun trying.

Gemma, the mde is a good way however the user would not be able to create a query. This is why I have had to stay clear of it.

Thanks again,

Ineedcoffee

 

[vbaInet]

Hi vbaInet,

Thats great, I will have a look at doing this later this afternoon. The only thing I can see wrong with what you have said is that the code used will list all query's on the database not just the user created ones.

Yes that's another hurdle. But here's how you overcome it. Follow what I've mentioned in point 6 and use a recordset to populate the listbox with the query names. For each of your own queries, in the Description property put a distinct text string like "--built-in--" for example. Loop through the collection of query defs and find all those querydefs that DO NOT have that string in it's Description property.

 

[Ineedcoffee]

Yes that's another hurdle. But here's how you overcome it. Follow what I've mentioned in point 6 and use a recordset to populate the listbox with the query names. For each of your own queries, in the Description property put a distinct text string like "--built-in--" for example. Loop through the collection of query defs and find all those querydefs that DO NOT have that string in it's Description property.

Was just what I was thinking. Was trying to find a way of making the built in database query's differ from those user created.

Another thing is that every new update I do with the front end will wipe out users query's. I was thinking that I could have the users create the query on the back end, then using what you have said, in the Description box I could store their UserID. This would then load up only those query's done by the user that is logged in. Also when the front end is updated, the query is stored on the backend.

Unfortunately I have meetings all day so can't really get my teeth in to it as yet but will keep you up to date on progress. Once done will prob post a copy of the database.

Thanks,

Ineedcoffee