If the form is bound to the query how can you be "creating it"? Do you mean you
are just changing the SQL?
Yes. Just changing the SQL.
Have you tried using the MasterLink and ChildLink properties? They should allow
all of this without any code at all.
I can't use those because the main form is unbound. (It's just a place
holder basically to hold the tabbed pages.)
By the way, here's the full code behind the command button. It involves
calling a function (QueryExists) which I didn't post. I could easily
remove this function and relevent code because the query always exists.
Again, all of this code works perfectly except the
' Requery the subform
Forms![frmProjects]![fsubProjectList].Form.Requery
### BEGIN CODE ###
Private Sub cmdOK_Click()
' Pointer to error handler
On Error GoTo cmdOK_Click_err
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strProjectType As String
Dim strProjectStatus As String
Dim strProjectOwner As String
Dim strSQL As String
' Identify the database and assign it to the variable
Set db = CurrentDb
' Check for the existence of the query, create it if not found,
' and assign it to the variable
If Not QueryExists("qryProjectList") Then
Set qdf = db.CreateQueryDef("qryProjectList")
Else
Set qdf = db.QueryDefs("qryProjectList")
End If
' Get the values from the combo boxes
If IsNull(Me.cboProjectType.Value) Then
strProjectType = " Like '*' "
Else
strProjectType = "='" & Me.cboProjectType.Value & "' "
End If
If IsNull(Me.cboProjectStatus.Value) Then
strProjectStatus = " Like '*' "
Else
strProjectStatus = "='" & Me.cboProjectStatus.Value & "' "
End If
If IsNull(Me.cboProjectOwner.Value) Then
strProjectOwner = " Like '*' "
Else
strProjectOwner = "='" & Me.cboProjectOwner.Value & "' "
End If
' Build the SQL string
strSQL = "SELECT tblProjects.* " & _
"FROM tblProjects " & _
"WHERE tblProjects.ProjectTypeID" & strProjectType & _
"AND tblProjects.ProjectStatus" & strProjectStatus & _
"AND tblProjects.ProjectOwner" & strProjectOwner & _
"ORDER BY tblProjects.ProjectTypeID;"
' Pass the SQL string to the query
qdf.SQL = strSQL
' Turn off screen updating
DoCmd.Echo False
' Check the state of the query and close it if it is open
If Application.SysCmd(acSysCmdGetObjectState, acQuery,
"qryProjectList") = acObjStateOpen Then
DoCmd.Close acQuery, "qryProjectList"
End If
' Requery the subform
Forms![frmProjects]![fsubProjectList].Form.Requery
cmdOK_Click_exit:
' Turn on screen updating
DoCmd.Echo True
' Clear the object variables
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdOK_Click_err:
' Handle Errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit
End Sub
### END CODE ###
Rick Brandt wrote:
"Kurt" <kh*******@cox.netwrote in message
news:11**********************@v61g2000cwv.googlegr oups.com...
This is an embedded subform right?
Yes.
Not one opened separately?
Correct.
If embedded are you using the MasterLink and ChildLink properties
to display the related records or does the subform's RecordSource
reference the ProjectType on the main form in its query criteria?
I'm not using the MasterLink and ChildLink properties. Instead, when
the user selects the search criteria (up to three criteria can be
selected using 3 combo boxes, one of which is ProjectType), the code
under the command button:
1) creates a query (called qryProjectList) (only does this if the query
doesn't exist),
2) gets the values from the combo boxes,
3) builds a SQL string,
4) passes the string to the query, and
5) requries the subform, which is based on this query.
Every step by the last one is working.
If the form is bound to the query how can you be "creating it"? Do you mean you
are just changing the SQL?
Have you tried using the MasterLink and ChildLink properties? They should allow
all of this without any code at all.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com