Access 2007 VBA & SQL - Update a Subform pointed at a dynamically created query

1
votes

Abstract: I'm using VB to recreate a query each time a user selects one of 3 options from a drop down menu, which appends the WHERE clause If they've selected anything from the combo boxes. I then am attempting to get the information displayed on the form to refresh thereby filtering what is displayed in the table based on user input.

1) Dynamically created query using VB.

Private Sub BuildQuery()
' This sub routine will redefine the subQryAllJobsQuery based on input from
' the user on the Management tab.

Dim strQryName As String
Dim strSql As String            ' Main SQL SELECT statement
Dim strWhere As String          ' Optional WHERE clause
Dim qryDef As DAO.QueryDef
Dim dbs As DAO.Database

strQryName = "qryAllOpenJobs"
strSql = "SELECT * FROM tblOpenJobs"
Set dbs = CurrentDb

' In case the query already exists we should deleted it
' so that we can rebuild it.  The ObjectExists() function
' calls a public function in GlobalVariables module.
If ObjectExists("Query", strQryName) Then
    DoCmd.DeleteObject acQuery, strQryName
End If

' Check to see if anything was selected from the Shift
' Drop down menu.  If so, begin the where clause.
If Not IsNull(Me.cboShift.Value) Then
    strWhere = "WHERE tblOpenJobs.[Shift] = '" & Me.cboShift.Value & "'"
End If

' Check to see if anything was selected from the Department
' drop down menu.  If so, append or begin the where clause.
If Not IsNull(Me.cboDepartment.Value) Then
    If IsNull(strWhere) Then
        strWhere = strWhere & " AND tblOpenJobs.[Department] = '" & Me.cboDepartment.Value & "'"
    Else
        strWhere = "WHERE tblOpenJobs.[Department] = '" & Me.cboDepartment.Value & "'"
    End If
End If

' Check to see if anything was selected from the Date
' field.  If so, append or begin the Where clause.
If Not IsNull(Me.txtDate.Value) Then
    If Not IsNull(strWhere) Then
        strWhere = strWhere & " AND tblOpenJobs.[Date] = '" & Me.txtDate.Value & "'"
    Else
        strWhere = "WHERE tblOpenJobs.[Date] = '" & Me.txtDate.Value & "'"
    End If
End If

' Concatenate the Select and the Where clause together
' unless all three parameters are null, in which case return
' just the plain select statement.
If IsNull(Me.cboShift.Value) And IsNull(Me.cboDepartment.Value) And IsNull(Me.txtDate.Value) Then
    Set qryDef = dbs.CreateQueryDef(strQryName, strSql)
Else
    strSql = strSql & " " & strWhere
    Set qryDef = dbs.CreateQueryDef(strQryName, strSql)
End If

End Sub

2) Main Form where the user selects items from combo boxes.

picture of the main form and sub form http://i48.tinypic.com/25pjw2a.png

3) Subform pointed at the query created in step 1.

Chain of events: 1) User selects item from drop down list on the main form. 2) Old query is deleted, new query is generated (same name). 3) Subform pointed at query does not update, but if you open the query by itself the correct results are displayed.

Name of the Query: qryAllOpenJobs name of the subform: subQryAllOpenJobs Also, the Row Source of subQryAllOpenJobs = qryAllOpenJobs Name of the main form: frmManagement

3
sqlms-accessms-access-2007vba
Have you requeried the subform after you change the saved QueryDef? It's usually not a good idea to edit QueryDefs (except where you have to) and it looks to me like you should just be able to set the subforms Recordsource directly (which automatically requeries). – David-W-Fenton
I have attempted to use the .requery procedure on the subform but it does not update, and I think you're right... its because I'm screwing with the QueryDef. I've been looking for examples of how recordsource manipulation works because it sounds like it would be what I need... but I haven't found any solid examples online to help me wrap my head around it. – Lucretius
Have you tried just setting the form's recordsource to the new SQL string? No need to muck about with the form's .Recordset property at all (I've never done it in the 10 years it's been available in Access). – David-W-Fenton
Yea I'm figuring that out now, I know SQL I just don't know much about Access (which is kinda backwards from the way most people learn). I've fixed my problem with a more focused study of the RecordSource property and its working wonderfully. – Lucretius

3 Answers

2
votes

I think you have your logic on the Department drop down check backwards.

You have it checking if strWhere is null, then if it is, you concatenate strWhere with the value of cboDepartment.

You should be doing what you are for Date.

' Check to see if anything was selected from the Department
' drop down menu.  If so, append or begin the where clause.
If Not IsNull(Me.cboDepartment.Value) Then
    If Not IsNull(strWhere) Then
        strWhere = strWhere & " AND tblOpenJobs.[Department] = '" & Me.cboDepartment.Value & "'"
    Else
        strWhere = "WHERE tblOpenJobs.[Department] = '" & Me.cboDepartment.Value & "'"
    End If
End If

You may also want to do:

If Nz(strWhere,"") = "" then

Instead of just doing IsNull so that you catch the zero-length string in addition to a null variable.

As for setting the recordsource, use something along the lines of

Me.sfrmJobs.Form.RecordSource = strSQL

where sfrmJobs is the name of your subform.

2
votes

An empty string is not the same thing as Null. When you declare a String variable such as this:

Dim strWhere As String

strWhere is initialized as an empty string (or "zero length string"). That value is sometimes referred to as a null string, and there is even a VBA constant, vbNullString, which represents the empty string. However, regardless of which name you use, the empty string variable is not Null. Furthermore a VBA String variable can never be Null. For example, this code will cause error 94, 'Invalid use of Null':

Dim strWhere As String
strWhere = Null

The reason I am emphasizing this point is because your code tests whether strWhere is Null. That is a logic flaw because strWhere will never be Null. For example, I don't believe this condition can ever be True:

If IsNull(strWhere) Then

If you want a test to determine when strWhere has not had a value assigned to it (it's still an empty string), use the Len function:

If Len(strWhere) = 0 Then

Here is a different approach for BuildQuery. It assumes the data type for your [Date] field is String (as your original code suggests). If [Date] is actually Date/Time data type, this code will not work. Also, please note that Date is a reserved word (see Problem names and reserved words in Access). I enclosed the field name in square brackets to avoid ambiguity. If it were my own database, I would change the field name instead. 

Private Sub BuildQuery()
'* Update subform RecordSource based on input from *'
'* the user on the Management tab. *'

Dim strSql As String        ' Main SQL SELECT statement '
Dim strWhere As String      ' Optional WHERE clause '
Dim i As Integer
Dim strControl As String
Dim strField As String

strSql = "SELECT * FROM tblOpenJobs AS oj"

strWhere = vbNullString
For i = 1 To 3
    Select Case i
    Case 1
        strControl = "cboShift"
        strField = "Shift"
    Case 2
        strControl = "cboDepartment"
        strField = "Department"
    Case 3
        strControl = "txtDate"
        strField = "[Date]"
    End Select
    If Not IsNull(Me.Controls(strControl).Value) Then
        strWhere = strWhere & _
            IIf(Len(strWhere) > 0, " AND ", "") & _
            "oj." & strField & " = '" & _
            Me.Controls(strControl).Value & "'"
    End If
Next i

If Len(strWhere) > 0 Then
    strSql = strSql & " WHERE " & strWhere
End If
'* use the name of the subform CONTROL for sfrmJobs *'
'* (may not be the name of the subform) *'
Me.sfrmJobs.Form.RecordSource = strSql

End Sub
1
votes

My solution is below in three parts. (1) Build Query, (2) Main Form, (3) Subform. `Private Sub OpenJobsQuery() ' This sub will construct the query on the front page for the user ' based on who they are and what they select from the combo boxes above ' the table for filtering by redefining the rowsource of the subform ' subQryOpenJobs

Dim strSql As String            ' Main SQL SELECT statement
Dim strWhere As String          ' Where clause containing user specified parameters.

strSql = "SELECT * FROM tblOpenJobs"
strWhere = ""

' Check to see if anything was selected from the Shift
' combo box.  If so, begin the Where clause.
If Not IsNull(Me.cboOpenJobShift.Value) Then
    strWhere = "WHERE tblOpenJobs.[Shift] = '" & Me.cboOpenJobShift.Value & "'"
End If

' Check to see if anything was selected from the Department
' combo box.  If so, append or begin the where clause.
If Not IsNull(Me.cboOpenJobDepartment.Value) Then
    If strWhere = "" Then
        strWhere = "WHERE tblOpenJobs.[Department] = '" & Me.cboOpenJobDepartment.Value & "'"
    Else
        strWhere = strWhere & " AND tblOpenJobs.[Department] = '" & Me.cboOpenJobDepartment.Value & "'"
    End If
End If

' Check to see if anything was selected from the Date
' field.  If so, append or begin the Where clause.
If Not IsNull(Me.cboOpenJobDate.Value) Then
    If strWhere = "" Then
        strWhere = "WHERE tblOpenJobs.[JobDate] = #" & Me.cboOpenJobDate.Value & "#"
    Else
        strWhere = strWhere & " AND tblOpenJobs.[JobDate] = #" & Me.cboOpenJobDate.Value & "#"
    End If
Else
    ' If nothing was entered in the date field, make sure the user
    ' only sees future jobs.
    If strWhere = "" Then
        strWhere = "WHERE tblOpenJobs.[JobDate] > #" & FormatDateTime(Date, vbShortDate) & "#"
    Else
        strWhere = strWhere & " AND tblOpenJobs.[JobDate] > #" & FormatDateTime(Date, vbShortDate) & "#"
    End If
End If

' Always include as part of the where clause, a section that
' will narrow the results based on who the user is
If strWhere = "" Then
    strWhere = "WHERE tblOpenJobs.[OpenJobID] Not In " & _
               "(SELECT tblSignUps.[OpenJobID] FROM tblSignUps WHERE tblSignUps.[EUID] = '" & strEUID & "');"

Else
    strWhere = strWhere & " AND tblOpenJobs.[OpenJobID] Not In " & _
               "(SELECT tblSignUps.[OpenJobID] FROM tblSignUps WHERE tblSignUps.[EUID] = '" & strEUID & "');"
End If

' Concatenate the Select and the Where clause together
strSql = strSql & " " & strWhere

' Set the recordsource of the subform to the SQL query generated
' and refresh the form.
Me.subQryOpenJobs.Form.RecordSource = strSql

' In addition, synchronize the JobID's in the Edit Job box to match those
' filtered by this Build Query.
Me.cboSelectJOBID.RowSource = "SELECT tblOpenJobs.[OpenJobID] FROM tblOpenJobs" & " " & strWhere

Me.Refresh

End Sub`

(2) Main Form. http://j.imagehost.org/view/0385/Form. and (3) the subform is populated as shown in the BuildQuery() sub to construct the query based on what the user selects from the drop down filters and the input boxes on the right of the form. The data in the table itself is inaccessible to the user, this is just for them to reference.