Option Compare Database
Private Function GetWhere() As String
Dim strTemp As String
'Set Null Arguments for each criteria (Added to SQL WHERE clause "on the fly")
If Not IsNull(Me!cmb_RecordName) Then
strTemp = strTemp & " AND tbl_Records.RecordName = " & Chr(34) & Me!cmb_RecordName & Chr(34)
End If
If Not IsNull(Me!cmb_RecordDistinction) Then
strTemp = strTemp & " AND tbl_Records.RecordDistinction = " & Chr(34) & Me!cmb_RecordDistinction & Chr(34)
End If
If Not IsNull(Me!cmb_Title) Then
strTemp = strTemp & " AND tbl_Records.Title = " & Chr(34) & Me!cmb_Title & Chr(34)
End If
If Not IsNull(Me!cmb_Author) Then
strTemp = strTemp & " AND tbl_Records.Author = " & Chr(34) & Me!cmb_Author & Chr(34)
End If
If Not IsNull(Me!cmb_ProjectManager) Then
strTemp = strTemp & " AND tbl_Records.ProjectManager = " & Chr(34) & Me!cmb_ProjectManager & Chr(34)
End If
If Not IsNull(Me!cmb_SiteName) Then
strTemp = strTemp & " AND tbl_Records.[Site Name] = " & Chr(34) & Me!cmb_SiteName & Chr(34)
End If
If Not IsNull(Me!cmb_ChargeCode) Then
strTemp = strTemp & " AND tbl_Records.ChargeCode = " & Chr(34) & Me!cmb_ChargeCode & Chr(34)
End If
If Not IsNull(Me!cmb_ContractNumber) Then
strTemp = strTemp & " AND tbl_Records.PrimeContractNumber = " & Chr(34) & Me!cmb_ContractNumber & Chr(34)
End If
If Not IsNull(Me!cmb_TaskOrder) Then
strTemp = strTemp & " AND tbl_Records.TaskOrder = " & Chr(34) & Me!cmb_TaskOrder & Chr(34)
End If
If Not IsNull(Me!txt_UPDateStart) Then
If Not IsNull(Me!txt_UPDateEnd) Then
strTemp = strTemp & " AND tbl_Records.UploadDate BETWEEN #" & Me!txt_UPDateStart & "# AND #" & Me!txt_UPDateEnd & "#"
End If
End If
If Not IsNull(Me!txt_RECDateStartMo) Then
If Not IsNull(Me!txt_RECDateStartYr) Then
If Not IsNull(Me!txt_RECDateEndMo) Then
If Not IsNull(Me!txt_RECDateEndYr) Then
'Create Date Strings
Dim StartDate As Variant
StartDate = CDate(Me!txt_RECDateStartMo & "/" & "01" & "/" & Me!txt_RECDateStartYr)
Dim EndDate As Variant
EndDate = CDate(Me!txt_RECDateEndMo & "/" & "01" & "/" & Me!txt_RECDateEndYr)
'Write SQL add-in
strTemp = strTemp & " AND tbl_Records.RecMoYr BETWEEN #" & StartDate & "# AND #" & EndDate & "#"
End If
End If
End If
End If
If Not IsNull(Me!txt_KeyWord) Then
'Set up strings necessary for search
Dim intIdx As Integer
Dim strWork As String
Dim strSearch As String
'Generate search criteria
strSearch = txt_KeyWord
Do
intIdx = InStr(1, strSearch, ",")
If intIdx > 0 Then
strWork = Trim(Left(strSearch, intIdx - 1))
strSearch = Mid(strSearch, intIdx + 1)
Else
strWork = Trim(strSearch)
strSearch = ""
End If
'Add to SQL statement
strTemp = strTemp & " AND tbl_Records.Description LIKE '*" & strWork & "*'"
Loop While strSearch > ""
End If
'Set string to add
strTemp = Mid(strTemp, 6)
'Make sure not all null
If Len(strTemp) > 0 Then
GetWhere = "WHERE " & strTemp
End If
End Function
Private Sub cmb_Author_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName, " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="Red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_ChargeCode_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_ContractNumber_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_ProjectManager_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_RecordDistinction_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_RecordName_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_SiteName_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_TaskOrder_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub cmb_Title_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub txt_KeyWord_LostFocus()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End Sub
Private Sub txt_RECDateStartMo_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
If Not IsNull(Me!txt_RECDateStartYr) Then
If Not IsNull(Me!txt_RECDateEndMo) Then
If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End If
End If
End If
End If
End Sub
Private Sub txt_RECDateStartYr_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
If Not IsNull(Me!txt_RECDateStartYr) Then
If Not IsNull(Me!txt_RECDateEndMo) Then
If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End If
End If
End If
End If
End Sub
Private Sub txt_RECDateEndMo_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
If Not IsNull(Me!txt_RECDateStartYr) Then
If Not IsNull(Me!txt_RECDateEndMo) Then
If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End If
End If
End If
End If
End Sub
Private Sub txt_RECDateEndYr_LostFocus()
If Not IsNull(Me!txt_RECDateStartMo) Then
If Not IsNull(Me!txt_RECDateStartYr) Then
If Not IsNull(Me!txt_RECDateEndMo) Then
If Not IsNull(Me!txt_RECDateEndYr) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End If
End If
End If
End If
End Sub
Private Sub txt_UPDateStart_LostFocus()
If Not IsNull(Me!txt_UPDateStart) Then
If Not IsNull(Me!txt_UPDateEnd) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End If
End If
End Sub
Private Sub txt_UPDateEnd_LostFocus()
If Not IsNull(Me!txt_UPDateStart) Then
If Not IsNull(Me!txt_UPDateEnd) Then
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode, tbl_Records.PrimeContractNumber, " & _
"tbl_Records.TaskOrder, tbl_Records.UploadDate, tbl_Records.RecMoYr, tbl_Records.Description, tbl_Records.OrigFileLoc, " & _
"tbl_Records.OrigFileName " & _
"FROM tbl_Records "
Dim Finder As String
Finder = strSQL & GetWhere()
[COLOR="red"]'Set subform equal to results of query
Forms.frm_RevFinder.subfrm_REVSelector.Form.RecordSource = Finder
Me.subfrm_REVSelector.Requery[/COLOR]
End If
End If
End Sub