StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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</p> <pre><code>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 </code></pre> <p>End Sub`</p> <p>(2) Main Form. <a href="http://j.imagehost.org/view/0385/Form" rel="nofollow noreferrer">http://j.imagehost.org/view/0385/Form</a>. 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload