Question: create a query threw a module

[Guest_imported]

Is it possible to have a module call the query wizard to run? Next if not what would be the code to create a query from any of the existing table. I want the users to beable to pick which table and fields to put in this query also beable to put in their own criterias in and have access to it at any time when they click on a button. I know they could just use the database query section but I rather they not because they keep changing the other queries that are set for the forms or reports. Then they messing up the database and wounder why it's not working. so I do not want them have access to the query section or form design. If you can help me with restricting them from those sections that would be great too. I hope I explained this right

Thank you

Tig2001

 

[BoxHead]

Tig,

This is a lot of work.

If you're serious, you could create a group of listboxes.

The first listbox (Lb1) would show your tables by iterating through the tableDefs collection. You could do it on the open event.

Dim MyTableList as String, intI as Integer
MyTableList = ""

For intI = 0 to CurrentDb.TableDefs.Count - 1

If Left(CurrentDb.TableDefs.Item(intI).Name, 4) <> &quot;mysys&quot; Then
If MyTableList = &quot;&quot; Then
MyTableList = CurrentDb.TableDefs.Item(intI).Name
Else
My TableList = MyTableList & &quot;;&quot; & CurrentDb.TableDefs.Item(intI).Name
End If
End If
Next intI

Lb1.RowSourceType = &quot;Value List&quot;
Lb1.RowSource = MyTableList
Lb1.Requery
Lb1 = &quot;&quot;

On the Click event of this first listbox (when someone selects a table name), you could determine the rowsource of your second listbox (Lb2). Set Lb2's rowsource type to 'Field List' when you create it and leave the rowsource blank. On the click event of Lb1, run

Lb2.RowSource = Lb1
Lb2.Requery
Lb2 = Null

A third listbox (Lb3) would have its rowsource type set to 'Table/Query' and the click event of the second listbox would determine its value. On Lb2's click event, run

Dim strSql as string

strSql = &quot;SELECT DISTINCT [&quot; & Lb2 & &quot;] FROM [&quot; & Lb1 & &quot;] ORDER BY [&quot; & Lb2 & &quot;];&quot;

Lb3.RowSource = strSql
Lb3.Requery

This should certainly get you going. You'll have a form with three listboxes: first shows table names, second shows names of fields in selected table, third shows field values based on second's selected field name.


John

Use what you have,
Learn what you can,
Create what you need.

 

[BoxHead]

Tig,

Or..........

I was thinking about this and realized that there's a much easier way to accomplish what you need to do.

In the database window, right-click on the query names you want to protect. Select 'Properties' and tick the checkbox for hidden. They won't show up when your users go to modify them.

You might want to copy/paste the queries in the database window before you hide them. Give them new names that will still be recognizable to your users so they can modify the copies without messing up the ones you depend on. If they're in the habit of doing this, they'll probably continue.

To unhide the queries, go to TOOLS > OPTIONS and select the &quot;Show Hidden Objects&quot; checkbox.

Sorry for the misdirection.




John

Use what you have,
Learn what you can,
Create what you need.

 

[Guest_imported]

Thanks I'll try both and see which work best for these users. I'll let you know If I have any problems with it.

Tig