Browsing on table names and checking if query exists

[Stoffel24]

Hi

I have 2 issues. Firstly, I would like to prompt the user to choose the name of a table from a drop down list. What is the best way of doing this? Currently I simply prompt them for a name from an inputbox which I know is not great at all! I haven't used a form. Would I be able to use a combobox on a form or some such technique. I have used VBA for Excel but Access is quite new to me.

My second issue regards determining if a query already exists. Basically, I would like to prompt the user for a table name and then create a query using this name. However, if the query has been created already, and you try to create it again, you get an error (query already exists). I was thinking of deleting it and recreating it every time someone tries to open this query to get the new values.

Thanks a lot

 

[jebry]

Hi!

You can get a list of the tables in a database by setting the rowsource of the combo box or list box to the following:

Select Name From MSysObjects Where Type = 1 And Left(Name, 4) <> &quot;MSys&quot;

Similarly, you can get a list of queries using:

Select Name From MSysObjects Where Type = 5 And Left(Name, 4) <> &quot;~sq_&quot;

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Stoffel24]

Thanks Jeff
I used the query and it works perfectly. I am afraid you are gonna have to help me a bit more here. How do I actually get these values into a list box? Must I create a form with a listbox on it or can I simply use an inputbox of sorts or can a list box be &quot;called&quot; as you would an inputbox?

Thanks

 

[jebry]

Hi!

You use the query as the row source of the list box, which you must put on a form. You will find the row source property on the Data tab of the list boxes property sheet. The row source type must say Table/Query.

If you need more let me know.

hth


Jeff Bridgham
bridgham@purdue.edu