Here is the code for my database.
:banghead:
Option Compare Database
Option Explicit
Dim FiltStr As String
Private Sub cboDept_AfterUpdate()
Me.cboTech.Requery
BuildFiltStr
End Sub
Private Sub cboDept_GotFocus()
Me.AllowEdits = True
If FiltStr = "" Then
Me.cboDept.RowSource = "SELECT DISTINCT PLCTracker.TDepartment FROM PLCTracker GROUP BY PLCTracker.TDepartment;"
Else
Me.cboDept.RowSource = "SELECT DISTINCT PLCTracker.TDepartment FROM PLCTracker GROUP BY PLCTracker.TDepartment WHERE " & FiltStr & ";"
End If
Me.cboDept.Dropdown
End Sub
Private Sub cboDept_LostFocus()
Me.AllowEdits = False
End Sub
Private Sub cboTech_AfterUpdate()
Me.cboDept.Requery
BuildFiltStr
End Sub
Private Sub cboTech_GotFocus()
Me.AllowEdits = True
If FiltStr = "" Then
Me.cboTech.RowSource = "SELECT DISTINCT PLCTracker.TTechnician FROM PLCTracker;"
Else
Me.cboTech.RowSource = "SELECT DISTINCT PLCTracker.TTechnician FROM PLCTracker WHERE " & FiltStr & ";"
End If
Me.cboTech.Dropdown
End Sub
Private Sub cboTech_LostFocus()
Me.AllowEdits = False
End Sub
Private Sub cmdActive_Click()
ProjActive = 1
cmdEdit.Enabled = True
cmdDelete.Enabled = True
Me.ProjNoList.Requery
End Sub
Private Sub cmdAdd_Click()
Me.AllowAdditions = True
DoCmd.Close acForm, "PLCProject1"
DoCmd.OpenForm "FrmProjectTemp"
End Sub
Private Sub cmdClear_Click()
Me.Requery
Me.Refresh
DateFrom = #1/1/2017#
DateTo = Date
Me.cboTech = ""
Me.cboDept = ""
Me.txtKeyword = ""
BuildFiltStr
ProjNoSearch = ""
'txtmessage.Caption = ""
Me.ProjNoList.Requery
'Me.DateHrList.Requery
'Me.TechList.Requery
ProjectNo.Enabled = False
TDateSubmitted.Enabled = False
TDateEntered.Enabled = False
TTechnician.Enabled = False
TRequestor.Enabled = False
TDepartment.Enabled = False
TManager.Enabled = False
TContactNo.Enabled = False
TDueDate.Enabled = False
TTestDate.Enabled = False
TProdDate.Enabled = False
TCompletedDate.Enabled = False
TStatus.Enabled = False
TProjectApprove.Enabled = False
TAFENo.Enabled = False
'TProjectAttach.Enabled = False
TAreaAffected.Enabled = False
TProjectOverview.Enabled = False
TProjectDetails.Enabled = False
TNotes.Enabled = False
TApplicationName.Enabled = False
TProcessorName.Enabled = False
'dateSearch.Enabled = False
'cmdSearching.Enabled = False
'DateHrList.Enabled = False
'frm_hours.Enabled = False
'cmdAdd.Enabled = True
'cmdUpdate.Enabled = False
'cmdDelete.Enabled = False
'cmdPrint.Enabled = True
DoCmd.RunCommand acCmdRecordsGoToFirst
End Sub
Private Sub cmdEdit_Click()
Me.Refresh
Me.Requery
[Forms]![frmPLCProject1]![ProjNoList].Requery
ProjectNo.Enabled = True
TDateSubmitted.Enabled = True
TDateEntered.Enabled = True
TTechnician.Enabled = True
TRequestor.Enabled = True
TDepartment.Enabled = True
TManager.Enabled = True
TContactNo.Enabled = True
TDueDate.Enabled = True
TTestDate.Enabled = True
TProdDate.Enabled = True
TCompletedDate.Enabled = True
TStatus.Enabled = True
TProjectApprove.Enabled = True
TAFENo.Enabled = True
'TProjectAttach.Enabled = True
TAreaAffected.Enabled = True
TProjectOverview.Enabled = True
TProjectDetails.Enabled = True
TNotes.Enabled = True
TApplicationName.Enabled = True
TProcessorName.Enabled = True
End Sub
Private Sub cmdinactive_Click()
' ProjActive = 0
cmdEdit.Enabled = False
cmdDelete.Enabled = False
Me.ProjNoList.Requery
End Sub
Private Sub cmdSearch_Click()
If IsNull(ProjNoSearch) = False Then
Me.Recordset.FindFirst "[ProjectNo]= " & ProjNoSearch
Me!ProjNoSearch = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me!ProjNoSearch = Null
End If
End If
DoCmd.Requery "ProjNoList"
End Sub
Private Sub cmdUpdate_Click()
Dim ID As Long
ID = Me!ProjectID
DoCmd.Close acForm, "frmPLCProject1"
DoCmd.OpenForm "FrmprojectTemp", OpenArgs:=ProjectID
End Sub
Private Sub cmdSearch_Enter()
Me.Refresh
DoCmd.Requery "ProjNoList"
End Sub
Private Sub Form_Load()
Me.Refresh
Me.Requery
Me.AllowEdits = True
[Forms]![frmPLCProject1]![ProjNoList].Requery
DateFrom = #1/1/2017#
DateTo = Date
txtKeyword = ""
'txtActiveMessage = ""
Me.ProjNoList.Requery
'Me.DateHrList.Requery
ProjectNo.Enabled = False
TDateSubmitted.Enabled = False
TDateEntered.Enabled = False
TTechnician.Enabled = False
TRequestor.Enabled = False
TDepartment.Enabled = False
TManager.Enabled = False
TContactNo.Enabled = False
TDueDate.Enabled = False
TTestDate.Enabled = False
TProdDate.Enabled = False
TCompletedDate.Enabled = False
TStatus.Enabled = False
TProjectApprove.Enabled = False
TAFENo.Enabled = False
' TProjectAttach.enable = False
TAreaAffected.Enabled = False
TProjectOverview.Enabled = False
TProjectDetails.Enabled = False
TNotes.Enabled = False
TApplicationName.Enabled = False
TProcessorName.Enabled = False
' dateSearch.Enabled = False
'cmdSearching.Enabled = False
'DateHrList.Enabled = False
'frm_hours.Enabled = False
cmdAdd.Enabled = True
cmdEdit.Enabled = True
cmdDelete.Enabled = False
DeptStr = Nz(Me!cboDept, "")
TechStr = Nz(Me!cboTech, "")
KeywordStr = Nz(Me!txtKeyword, "")
DoCmd.RunCommand acCmdRecordsGoToFirst
End Sub
Private Sub Form_Open(Cancel As Integer)
Me.Refresh
Me.AllowEdits = True
[Forms]![frmPLCProject1]![ProjNoList].Requery
DateFrom = #1/1/2017#
DateTo = Date
'txtActiveMessage = ""
Me.ProjNoList.Requery
'Me.DateHrList.Requery
ProjectNo.Enabled = False
TDateSubmitted.Enabled = False
TDateEntered.Enabled = False
TTechnician.Enabled = False
TRequestor.Enabled = False
TDepartment.Enabled = False
TManager.Enabled = False
TContactNo.Enabled = False
TDueDate.Enabled = False
TTestDate.Enabled = False
TProdDate.Enabled = False
TCompletedDate.Enabled = False
TStatus.Enabled = False
TProjectApprove.Enabled = False
TAFENo.Enabled = False
' TProjectAttached.Value = False
TAreaAffected.Enabled = False
TProjectOverview.Enabled = False
TProjectDetails.Enabled = False
TNotes.Enabled = False
TApplicationName.Enabled = False
TProcessorName.Enabled = False
' dateSearch.Enabled = False
'cmdSearching.Enabled = False
'DateHrList.Enabled = False
'frm_hours.Enabled = False
cmdAdd.Enabled = True
cmdEdit.Enabled = True
cmdDelete.Enabled = False
DeptStr = Nz(Me!cboDept, "")
TechStr = Nz(Me!cboTech, "")
KeywordStr = Nz(Me!txtKeyword, "")
DoCmd.RunCommand acCmdRecordsGoToFirst
End Sub
Private Sub ProjNoList_Click()
Me.Refresh
Me.Requery
ProjectNo.Enabled = False
TTechnician.Enabled = True
TDateSubmitted.Enabled = True
TDateEntered.Enabled = True
TRequestor.Enabled = True
TDepartment.Enabled = True
TManager.Enabled = True
TContactNo.Enabled = True
TDueDate.Enabled = False
TTestDate.Enabled = True
TProdDate.Enabled = True
TCompletedDate.Enabled = True
TApplicationName.Enabled = True
TProcessorName.Enabled = True
TStatus.Enabled = True
TProjectApprove.Enabled = False
TAreaAffected.Enabled = True
TProjectOverview.Enabled = True
TNotes.Enabled = True
ProjNoSearch.Enabled = True
cmdSearch.Enabled = True
ProjNoList.Enabled = True
Dim rs As DAO.Recordset
If Not IsNull(Me.ProjNoList) Then
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[ProjectNo] = " & Me.ProjNoList
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
Private Sub ProjNoSearch_AfterUpdate()
If (ProjNoSearch & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[ProjectNo]=" & ProjNoSearch
If rs.NoMatch Then
' MsgBox "Sorry, no such record " & ProjectNoSearch & " was found.",
'vbOKOnly vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close
ProjNoSearch = Null
Me.ProjNoList.Requery
End Sub
Private Sub ProjNoSearch_Enter()
Me.ProjNoSearch = ""
Me.Refresh
DoCmd.Requery "ProjNoList"
'Me.cmdSaving.Enabled = True
End Sub
Private Sub TAFENo_Click()
Me.Refresh
End Sub
Private Sub TApplicationName_Click()
Me.Refresh
End Sub
Private Sub TContactNo_Click()
Me.Refresh
End Sub
Private Sub TDepartment_Click()
Me.Refresh
End Sub
Private Sub TManager_Click()
Me.Refresh
End Sub
Private Sub TProcessorName_Click()
Me.Refresh
End Sub
Private Sub TProjectApprove_Click()
Me.Refresh
End Sub
Private Sub TRequestor_Click()
Me.Refresh
End Sub
Private Sub TStatus_Click()
Me.Refresh
End Sub
Private Sub TTechnician_Click()
Me.Refresh
End Sub
Sub BuildFiltStr()
FiltStr = ""
If Me!cboDept <> "" Then
FiltStr = "[TDepartment] = '" & Me!cboDept & "'"
End If
If Me!cboTech <> "" Then
If FiltStr = "" Then
FiltStr = "[TTechnician] = '" & Me!cboTech & "'"
Else
FiltStr = FiltStr & " AND [TTechnician] = '" & Me!cboTech & "'"
End If
End If
' If the Filtersa are blank turn off the filters
If FiltStr = "" Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = FiltStr ' If filter has a filter string then turn the filter on.
Me.FilterOn = True
End If
DeptStr = Nz(Me!cboDept, "")
TechStr = Nz(Me!cboTech, "")
KeywordStr = Nz(Me!txtKeyword, "")
End Sub
Private Sub txtKeyword_Change()
Me.Form.Filter = "[ProjectNo] = '" & Replace(Me.txtKeyword.Text, "", "") & ""
Me.FilterOn = True
Else
Me.Form.Filter = "[ProjectNo] Like '*" & Replace(Me.txtKeyword.Text, "", "") & "*'"
Me.Form.FilterOn = True
End If
Me.txtKeyword.SetFocus
Me.txtKeyword.SelStart = Len(Me.txtKeyword.Text)
If Me.CCSearchFilter <> "" Then
Me.CCSearchFilter = ""
End If
ErrHandler:
Resume Next
End
End Sub
Private Sub txtKeyword_GotFocus()
Me.AllowEdits = True
End Sub
Private Sub txtKeyword_LostFocus()
Me.AllowEdits = False
End Sub
