J
Justin83716
I was asked to create a form that would search / filter data from various
different tables and display the results. I have done this, however now I
find out that they would like to export the data to Excel as well as generate
reports or possibly print the results. From what I understand this cannot be
done unless I based my filter on a query, which I did not. Can I change my
code (pasted below) to look at a query, which I will create, and then be able
to perform the above actions (export, print, etc)? I'm too new to access and
coding to understand if what I'm thinking of doing is way more trouble than
it's worth, or easily accomplished. Any suggestions or help would be greatly
appreciated.
************************
Option Compare Database
Private Sub cmdClear_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearchDatabase"
End Sub
Private Sub cmdSearch_Click()
'Const cRequiredFieldError As String = "No criteria has been selected."
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim StrError As String
strWhere = "1=1 "
'If Record ID (tblEmployeeProjectDetails)
If Nz(Me.cboRecordID) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Assignment Number] = " & Me.cboRecordID & ""
End If
'If Last Name (tblEmployeeData)
If Nz(Me.cboLastName) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Last Name] = '" & Me.cboLastName & "'"
End If
'If Employee Number (tblEmployeeProjectDetails)
If Nz(Me.cboEmployeeNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Emp #] = " & Me.cboEmployeeNumber & ""
End If
'If Actuals (tblEmployeeProjectDetails)
If Nz(chkActuals, 0) = True Then
strWhere = strWhere & " AND Actuals = True"
End If
'If Assignment Letter (tblEmployeeProjectDetails)
If Nz(chkAssignmentLetter, 0) = True Then
strWhere = strWhere & " AND AssignmentLetter = True"
End If
'If Weekly Allowance (tblEmployeeProjectDetails)
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow & " AND " & " [Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
End If
'If Project Number (tblEmployeeProjectDetails)
If Nz(Me.cboProjectNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Prj #] = " & Me.cboProjectNumber & ""
End If
'If Project State (tblProjectData)
If Nz(Me.cboProjectState) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "ST = '" & Me.cboProjectState & "'"
End If
'If Business Unit (tblProjectData)
If Nz(Me.cboBusinessUnit) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Business Unit] = '" & Me.cboBusinessUnit &
"'"
End If
'If Set Of Books (tblProjectData)
If Nz(Me.cboSetOfBooks) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "BOOKS = '" & Me.cboSetOfBooks & "'"
End If
'If Tax Status (tblEmployeeProjectDetails)
If Nz(Me.cboTaxStatus) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "TaxStatus = '" & Me.cboTaxStatus & "'"
End If
'If Project Manager (tblEmployeeProjectDetails)
If Nz(Me.cboProjectManager) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[MANAGER] = " & Me.cboProjectManager & ""
End If
'If Projected End Date From(tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateFrom) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] >= " &
GetDateFilter(Me.txtProjectedEndDateFrom)
ElseIf Nz(Me.txtProjectedEndDateFrom) <> "" Then
StrError = cInvalidDateError
End If
'If Projected End Date To (tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateTo) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] <= " &
GetDateFilter(Me.txtProjectedEndDateTo)
ElseIf Nz(Me.txtProjectedEndDateTo) <> "" Then
StrError = cInvalidDateError
End If
If StrError <> "" Then
MsgBox StrError
Else
'DoCmd.OpenForm "fsubRecordSearch", acFormsDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in dd/mmm/YYYY format
GetDateFilter = "#" & Format(dtDate, "dd/mmm/YYYY") & "#"
End Function
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
cmdSearch_Click
End If
End Sub
Thanks!
different tables and display the results. I have done this, however now I
find out that they would like to export the data to Excel as well as generate
reports or possibly print the results. From what I understand this cannot be
done unless I based my filter on a query, which I did not. Can I change my
code (pasted below) to look at a query, which I will create, and then be able
to perform the above actions (export, print, etc)? I'm too new to access and
coding to understand if what I'm thinking of doing is way more trouble than
it's worth, or easily accomplished. Any suggestions or help would be greatly
appreciated.
************************
Option Compare Database
Private Sub cmdClear_Click()
DoCmd.Close
DoCmd.OpenForm "frmSearchDatabase"
End Sub
Private Sub cmdSearch_Click()
'Const cRequiredFieldError As String = "No criteria has been selected."
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim StrError As String
strWhere = "1=1 "
'If Record ID (tblEmployeeProjectDetails)
If Nz(Me.cboRecordID) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Assignment Number] = " & Me.cboRecordID & ""
End If
'If Last Name (tblEmployeeData)
If Nz(Me.cboLastName) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Last Name] = '" & Me.cboLastName & "'"
End If
'If Employee Number (tblEmployeeProjectDetails)
If Nz(Me.cboEmployeeNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Emp #] = " & Me.cboEmployeeNumber & ""
End If
'If Actuals (tblEmployeeProjectDetails)
If Nz(chkActuals, 0) = True Then
strWhere = strWhere & " AND Actuals = True"
End If
'If Assignment Letter (tblEmployeeProjectDetails)
If Nz(chkAssignmentLetter, 0) = True Then
strWhere = strWhere & " AND AssignmentLetter = True"
End If
'If Weekly Allowance (tblEmployeeProjectDetails)
If Not IsNull(Me.txtWeeklyAllowanceLow) And Not
IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow & " AND " & " [Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
ElseIf Not IsNull(Me.txtWeeklyAllowanceLow) Then
strWhere = strWhere & " AND " & "[Wk/Allow] >= " &
Me.txtWeeklyAllowanceLow
ElseIf Not IsNull(Me.txtWeeklyAllowanceHigh) Then
strWhere = strWhere & " AND " & "[Wk/Allow] <= " &
Me.txtWeeklyAllowanceHigh
End If
'If Project Number (tblEmployeeProjectDetails)
If Nz(Me.cboProjectNumber) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Prj #] = " & Me.cboProjectNumber & ""
End If
'If Project State (tblProjectData)
If Nz(Me.cboProjectState) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "ST = '" & Me.cboProjectState & "'"
End If
'If Business Unit (tblProjectData)
If Nz(Me.cboBusinessUnit) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[Business Unit] = '" & Me.cboBusinessUnit &
"'"
End If
'If Set Of Books (tblProjectData)
If Nz(Me.cboSetOfBooks) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "BOOKS = '" & Me.cboSetOfBooks & "'"
End If
'If Tax Status (tblEmployeeProjectDetails)
If Nz(Me.cboTaxStatus) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "TaxStatus = '" & Me.cboTaxStatus & "'"
End If
'If Project Manager (tblEmployeeProjectDetails)
If Nz(Me.cboProjectManager) <> "" Then
'Create Predicate
strWhere = strWhere & " AND " & "[MANAGER] = " & Me.cboProjectManager & ""
End If
'If Projected End Date From(tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateFrom) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] >= " &
GetDateFilter(Me.txtProjectedEndDateFrom)
ElseIf Nz(Me.txtProjectedEndDateFrom) <> "" Then
StrError = cInvalidDateError
End If
'If Projected End Date To (tblEmployeeProjectDetails)
If IsDate(Me.txtProjectedEndDateTo) Then
'Add it to Predicate - Exact
strWhere = strWhere & " AND " & "[PROJECTED END DATE] <= " &
GetDateFilter(Me.txtProjectedEndDateTo)
ElseIf Nz(Me.txtProjectedEndDateTo) <> "" Then
StrError = cInvalidDateError
End If
If StrError <> "" Then
MsgBox StrError
Else
'DoCmd.OpenForm "fsubRecordSearch", acFormsDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Debug.Print strWhere
Me.fsubRecordSearch.Form.Filter = strWhere
Me.fsubRecordSearch.Form.FilterOn = True
End If
End Sub
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in dd/mmm/YYYY format
GetDateFilter = "#" & Format(dtDate, "dd/mmm/YYYY") & "#"
End Function
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
KeyCode = 0
cmdSearch_Click
End If
End Sub
Thanks!