Creating a very flexible Query form for use by people unfamiliar with Access

B

Bob

Hi all,

I'd like to create a form that will allow the user to create a query,
being able to control things like like which fields are in the query,
the criteria and grouping etc, but I don't want to show the normal
access create query screen. I'd like to have a pretty front-end to
it, which it will then return the recordset which I can show in a
subform or something.

Does anyone have any suggestions or is there any code out there that
has developed something like that?

Cheers.
 
S

strive4peace

code: MakeQuery
---


Hi Bob,

why do you not want to teach your users to develop queries? I find that
the more I educate users, the better they appreciate what I have
developed. The QBE (Query-By-Example) grid is the most efficient way to
build a query as you would have to handles all kinds of tricky joins in
constructing SQL if you simply offer lists of fields for them to choose.

Here is a procedure you can put into a general module to create a query
or change the SQL for an existing query:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2007 at yahoo dot com

On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

to help them determine which fields to get information from, you could
give each a screen shot of the Relationships -- make sure you stretch
out all lists and organize them so that data flows -- put the "1" side
of the relationship on the left and the "many" side on the right

Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Top