Custom Query form

[Ben via AccessMonster.com]

I have a form that has a button which is tied to a query. I want the user to
pick which items from the query that they want. And then run the custom
query.

In native query design, I can pull up the query, uncheck the fields I do not
want and run the query. But how do I give the user flexibility to customize
the query the way they want by pick the fields?

I hope i am explaining this right. on the form is:

[ ] customer ID
[ ] customer name
[ ] customer city
[ ] customer product

The user will check customer name and customer product only. I want to run a
customer query that will only show customer name and customer product. All
depending upon what the user checks. and then run the query to display the
results.

Thanks,
Ben

 

[Jack Leach]

I use search forms and build a new SQL statement on the fly when values are
changed. I prefer private variables to hold key data required for the sql,
and on the change or afterupdate events of the controls I will run a function
that builds the sql.


(aircode)
Option Compare Database
Option Explicit

Private pbShowCustomerID As Boolean
Private pbShowCustomerName As Boolean
Private pbShowCustomerCity As Boolean
Private pbShowCustomerProduct As Boolean

Private Sub chkCustID_Change()
pbShowCustomerID = Iif(Me.chkCustID, True, False)
psSetSQL
End Sub

Private Sub chkCustName_Change()
pbShowCustomerName = Iif(chkCustName, True, False)
psSetSQL
End Sub

[etc etc]

Private Sub psSetSQL()
Dim SQLString As String

SQLString = "SELECT"

SQLString = Iif(pbShowCustomerID, _
SQLString & " CustomerID,", _
SQLString)
SQLString = Iif(pbShowCustomerName, _
SQLString & " CustomerName,", _
SQLString
[etc etc]

'Remove the last "," from the string...
SQLString = Left(SQLString, Len(SQLString) - 1)

SQLString = SQLString & " FROM tablename WHERE condition"

<yourrecordsource> = SQLString

End Sub




The disadvantage: on the fly SQL such as this is not saved or optimized by
Jet, therefore very large recordsets may be slow. But I routinely do this
with Where clauses with up to ten different text and boolean type controls on
a search form and it works well.

This may be possible with by modifying a querydef in the same manner, but I
think the time it would take to save the querydef, let Jet plan it, and then
execute it might actually be slower for on the fly stuff.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

Also, if you try this, you will need some code in Open to preset the default
variables and run the sub:

Private Sub Form_Open()
pbShowCustomerID = True
pbShowCustomerName = True
pbShowCustomerCity = True
pbShowCustomerProduct = True
'add your checkbox settings as well...
psSetQSL
End Sub

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I use search forms and build a new SQL statement on the fly when values are
changed. I prefer private variables to hold key data required for the sql,
and on the change or afterupdate events of the controls I will run a function
that builds the sql.


(aircode)
Option Compare Database
Option Explicit

Private pbShowCustomerID As Boolean
Private pbShowCustomerName As Boolean
Private pbShowCustomerCity As Boolean
Private pbShowCustomerProduct As Boolean

Private Sub chkCustID_Change()
pbShowCustomerID = Iif(Me.chkCustID, True, False)
psSetSQL
End Sub

Private Sub chkCustName_Change()
pbShowCustomerName = Iif(chkCustName, True, False)
psSetSQL
End Sub

[etc etc]

Private Sub psSetSQL()
Dim SQLString As String

SQLString = "SELECT"

SQLString = Iif(pbShowCustomerID, _
SQLString & " CustomerID,", _
SQLString)
SQLString = Iif(pbShowCustomerName, _
SQLString & " CustomerName,", _
SQLString
[etc etc]

'Remove the last "," from the string...
SQLString = Left(SQLString, Len(SQLString) - 1)

SQLString = SQLString & " FROM tablename WHERE condition"

<yourrecordsource> = SQLString

End Sub




The disadvantage: on the fly SQL such as this is not saved or optimized by
Jet, therefore very large recordsets may be slow. But I routinely do this
with Where clauses with up to ten different text and boolean type controls on
a search form and it works well.

This may be possible with by modifying a querydef in the same manner, but I
think the time it would take to save the querydef, let Jet plan it, and then
execute it might actually be slower for on the fly stuff.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I have a form that has a button which is tied to a query. I want the user to
pick which items from the query that they want. And then run the custom
query.

In native query design, I can pull up the query, uncheck the fields I do not
want and run the query. But how do I give the user flexibility to customize
the query the way they want by pick the fields?

I hope i am explaining this right. on the form is:

[ ] customer ID
[ ] customer name
[ ] customer city
[ ] customer product

The user will check customer name and customer product only. I want to run a
customer query that will only show customer name and customer product. All
depending upon what the user checks. and then run the query to display the
results.

Thanks,
Ben