Running Query off Form

K

Kou Vang

Is it possible to run a query off data from a form? To take the information
from a Form and perform the query, especially when it comes to the criteria
of a query.
 
J

John Vinson

Is it possible to run a query off data from a form? To take the information
from a Form and perform the query, especially when it comes to the criteria
of a query.
Click to expand...

Certainly, and a very useful technique it is!

Use a criterion such as

[Forms]![YourFormName]![Controlname]

on the criteria line. The Form must be open when you run the query
(or, better, run the Report or open the Form upon which the query is
based); and the Control you're using as a criterion should be unbound,
i.e. it should have nothing in its Control Source property.

John W. Vinson[MVP]
 
S

Sprinks

Hi, Kou.

If I understand you to mean that you wish to use a form to permit a user to
enter one or more criteria to filter a recordset, and then display the
resulting query on a form, then the technique is to build a filter string
from the users selections, and then use the string as the WHERE clause in the
OpenForm method.

The way I do it is to provide combo boxes for the choices, and then loop
through them, building the filter string and writing it to an invisible form
control. When the user presses a command button, a form is opened with the
matching records. Since the loop loops through the *controls*, but the
criteria needs to know the associated *fields*, I use a naming
convention--each control is the name of its associated field plus a
three-character prefix. You can then use the Right string function to return
the field name. Call WriteFilterString in the AfterUpdate event of each
criteria control:

Call WriteFilterString

' WriteFilterString code
Private Sub WriteFilterString()
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls
Select Case ctl.ControlType

Case acComboBox
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "
End If

‘ other cases here, if needed

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

‘ OpenForm method call—attached to command button
Dim strDocName As String
Dim strFilter As String

strDocName = "YourFormName"
strFilter = ""

' If no criteria selected, preview entire report
If IsNull(Me!txtFilterString) Then
DoCmd.OpenReport strDocName, acViewPreview
Else
strFilter = Me!txtFilterString
DoCmd.OpenReport strDocName, acViewPreview, , strFilter
End If

Hope that helps.
Sprinks
 
Top