Have A Form Open a Query

[jillian.calderon]

Hi,

I want to do the following, but I want to do it with a query:

DoCmd.OpenReport "CircuitDetails", acViewPreview, , strWhere

Would I do this?:
DoCmd.OpenQuery "CircuitDetails", acViewPreview, , strWhere

How would I open a query from a button?

Thanks!
Jill
---------------------------------------------------------------------------------------------------
Here's the rest of the code. It's a search:

Private Sub cmdSearch_Click()
'Jillian (361)249-3018
Dim strCircuitTypesAndCosts As String 'Name of report to open.
Dim strCircuitID As String 'Name of the Circuit ID field.
Dim strCostCenter As String 'Name of the Cost Center field.
Dim strWoWnum As String 'Name of the WoW# field
Dim strRegion As String 'Name of the Region field.
Dim strBuilding As String 'Name of the Aloc field.
'Dim strZloc As String 'Name of the Zloc field.
Dim strType As String 'Name of the Circuit Type field.
Dim strVendor As String 'Name of the Vendor field.
Dim strWhere As String 'Where condition for OpenReport.
'Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "CircuitDetails"
strField = "SaleDate"

strWhere = ""

If Not IsNull(Me.txtCircuitID) Then
txtCircuitID.SetFocus
strCircuitID = txtCircuitID.Text
strWhere = strWhere & " circuitID = " & strCircuitID & " AND "
End If


If Not IsNull(Me.txtCostCenterNum) Then
txtCostCenterNum.SetFocus
strCostCenter = txtCostCenterNum.Text
strWhere = strWhere & " costCenter LIKE """ & strCostCenter &
""" AND "
End If

If Not IsNull(Me.txtWoWnum) Then
txtWoWnum.SetFocus
strWoWnum = txtWoWnum.Text
strWhere = strWhere & " WoWnum LIKE """ & strWoWnum & """ AND "
End If

If Not IsNull(Me.txtRegion) Then
txtRegion.SetFocus
strRegion = txtRegion.Text
strWhere = strWhere & " regionName LIKE """ & strRegion & """
AND "
End If

If Not IsNull(Me.txtBuilding) Then
txtBuilding.SetFocus
strBuilding = txtBuilding.Text
strWhere = strWhere & " buildingName LIKE """ & strBuilding &
""" AND "
End If


'If Not IsNull(Me.txtZloc) Then
' txtZloc.SetFocus
' strZloc = txtZloc.Text
' strWhere = strWhere & " zmarkID = " & strZloc & " AND "
'End If

If Not IsNull(Me.txtType) Then
txtType.SetFocus
strType = txtType.Text
strWhere = strWhere & " serviceBandwidthName LIKE """ & strType
& """ AND "
End If

If Not IsNull(Me.txtVendor) Then
txtVendor.SetFocus
strVendor = txtVendor.Text
strWhere = strWhere & " vendorName LIKE """ & strVendor & """
AND "
End If

If Len(strWhere) > 4 Then
strWhere = Mid(strWhere, 1, Len(strWhere) - 4)
End If
'MsgBox strWhere
DoCmd.OpenReport "CircuitDetails", acViewPreview, , strWhere



'If IsNull(Me.Text6) Then
' If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
' strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
'End If

'Else
' If IsNull(Me.txtEndDate) Then 'Start date, but no End.
' strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
' Else 'Both start and end dates.
' strWhere = strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
' & " And " & Format(Me.txtEndDate, conDateFormat)
' End If
'End If

Debug.Print strWhere 'For debugging purposes
only.
'DoCmd.OpenReport strReport, acViewPreview, , strWhere



End Sub

 

[John Vinson]

How would I open a query from a button?

Why would you want to?

Query datasheets are of VERY limited value. Could you explain what you
are trying to accomplish, from the user's point of view? Do you just
want to see the data that is now going to the report onscreen? If so,
you can either open the report in Print Preview mode, or instead open
a Form. It's not necessary to "open the query" to do so.

John W. Vinson[MVP]

 

[Guest]

Jill:

The OpenQuery method does not include a WhereCondition argument. If you
just want to see the data as a datasheet rather than a report the simplest
way would be to create a form, frmCircuitDetails say, based on the query and
set the form's DefaultView property to Datasheet. You can then open the from
with:

DoCmd.OpenForm "CircuitDetails", WhereCondition:=strWhere

To open the query directly you could amend the SQL property of the querydef
object in the code before opening it. This would involve building a string
expression for the complete SQL statement to include the strWhere variable's
value as the query's WHERE clause e.g.

Dim dbs as DAO.Database, qdf As DAO.Querydef

Set dbs = CurrentDb
Set qdf = dbs.Querydefs("CircuitDetails")

qdf.SQL = SELECT * FROM MyTable WHERE " & strWhere
DoCmd.OpenQuery "CircuitDetails"

Ken Sheridan
Stafford, England