I have a combo box on my main form that locates an employee record, then two subforms show current contract details (single form) and contract history (datasheet view). Combo box works fine. (See ComboBox Code below.)
This combo box simply goes to the record of the individual typed into the box or selected from the drop-down list – leaving all records accessible; e.g., bottom nav. bar displays Record 125 of 2987, etc.
On the main form I also have a button (just below the combo box above) which opens a listbox (see LIST BOX SPECIFICATIONS below) to more thoroughly search for an employee which is able to be sorted by different criteria; e.g., Active, Last Name, First Name, etc. This listbox also works fine. However, it does not leave all employee records accessible (like the combo box does using a record set) – it displays only the record(s) for the person selected – thereby disabling further use of the combo box – due to: "[qryMainScreenSelectRecord.EMPLOYEE_NUMBER]=" & "" & Me.lstSearch.Column(0) & "") – e.g., bottom nav. bar displays Record 1 of 1. I’ve tried creating a record set for the listbox, tried using DoCmd.GoToRecord, tried using DoCmd.FindRecord, etc. At this point, I can’t remember what I’ve tried or haven’t tried.
In any event, the short solution I’m looking for is to simply have the listbox, once it opens the Main form and finds the record the user selects, leave the remainder of the records available – so the combo box will still work. Any suggestions of where I need to add an “on close” requery, a record set setup, or any other code?
The longer solution (and preferred) would be to have one form, two boxes– the combo box would also filter the listbox (by last name only) as the user types in letters of the last name.
Any help will be greatly appreciated-
colleen
ComboBox CODE:
Event Handler After Update Code:
Private Sub Form_Current()
Me.ComboSelectEmployee.Value = Me.Employee_Number
End Sub
Private Sub ComboSelectEmployee_AfterUpdate()
On Error GoTo ErrorHandler
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Employee_Number]= " & Str(Nz(Me![ComboSelectEmployee].Value, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
CleanUpAndExit:
Exit Sub
ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
Resume CleanUpAndExit
End Sub
LIST BOX SPECIFICATIONS:
LISTBOX Row Source:
SELECT qryMainScreenSelectRecord.EMPLOYEE_NUMBER, qryMainScreenSelectRecord.FACULTY_Active, qryMainScreenSelectRecord.Last_Name, qryMainScreenSelectRecord.First_Name, qryMainScreenSelectRecord.Preferred_Name FROM qryMainScreenSelectRecord ORDER BY qryMainScreenSelectRecord.FACULTY_Active, Last_Name;
Event Handlers AfterUpdate and OnDoubleClick
Option Compare Database
Option Explicit
Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String
'Clear captions from command buttons
ClearCaptions
'Set row source for list box
strSQL = "SELECT DISTINCTROW EMPLOYEE_NUMBER, FACULTY_Active, Last_Name, First_Name, Preferred_Name "
strSQL = strSQL & "FROM qryMainScreenSelectRecord "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!lstSearch.RowSource = strSQL
Me!lstSearch.Requery
End Function
Private Sub ClearCaptions()
'Clear captions of asc and desc symbols
Me!cmdOrderFactiveDesc.Caption = "Order by Active Status"
Me!cmdOrderFActive.Caption = "Order by Active Status"
Me!cmdOrderLNameDesc.Caption = "Order by Last Name"
Me!cmdOrderLName.Caption = "Order by Last Name"
Me!cmdOrderFNameDesc.Caption = "Order by First Name"
Me!cmdOrderFName.Caption = "Order by First Name"
Me!cmdOrderPNameDesc.Caption = "Order by Preferred Name"
Me!cmdOrderPName.Caption = "Order by Preferred Name"
End Sub
Private Sub cmdOrderFActive_Click()
'Set Active Status order in ascending order and apply captions
Dim response As Integer
response = basOrderby("FACULTY_Active", "asc")
Me!cmdOrderFactiveDesc.Visible = True
Me!cmdOrderFactiveDesc.Caption = "v Active Status v"
Me!cmdOrderFactiveDesc.SetFocus
Me!cmdOrderFActive.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderFActiveDesc_Click()
'Set Active Status order in descending order and apply captions
Dim response As Integer
response = basOrderby("FACULTY_Active", "DESC")
Me!cmdOrderFActive.Visible = True
Me!cmdOrderFActive.Caption = "^ Active Status ^"
Me!cmdOrderFActive.SetFocus
Me!cmdOrderFactiveDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderLName_Click()
'Set Last Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Last_Name", "asc")
Me!cmdOrderLNameDesc.Visible = True
Me!cmdOrderLNameDesc.Caption = "v Last Name v"
Me!cmdOrderLNameDesc.SetFocus
Me!cmdOrderLName.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderLNameDesc_Click()
'Set Last Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Last_Name", "DESC")
Me!cmdOrderLName.Visible = True
Me!cmdOrderLName.Caption = "^ Last Name ^"
Me!cmdOrderLName.SetFocus
Me!cmdOrderLNameDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderFName_Click()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("First_Name", "asc")
Me!cmdOrderFNameDesc.Visible = True
Me!cmdOrderFNameDesc.Caption = "v First Name v"
Me!cmdOrderFNameDesc.SetFocus
Me!cmdOrderFName.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderFNameDesc_Click()
'Set First Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("First_Name", "DESC")
Me!cmdOrderFName.Visible = True
Me!cmdOrderFName.Caption = "^ First Name ^"
Me!cmdOrderFName.SetFocus
Me!cmdOrderFNameDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderPName_Click()
'Set Preferred Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Preferred_Name", "asc")
Me!cmdOrderPNameDesc.Visible = True
Me!cmdOrderPNameDesc.Caption = "v Preferred Name v"
Me!cmdOrderPNameDesc.SetFocus
Me!cmdOrderPName.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderPNameDesc_Click()
'Set Preferred Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Preferred_Name", "DESC")
Me!cmdOrderPName.Visible = True
Me!cmdOrderPName.Caption = "^ Preferred Name ^"
Me!cmdOrderPName.SetFocus
Me!cmdOrderPNameDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enabled = True
End Sub
Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearch) Then
ShowRecord_Click
End If
End Sub
Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box
DoCmd.OpenForm "frmMainFacultyData", , , _
"[qryMainScreenSelectRecord.EMPLOYEE_NUMBER]=" & "" & Me.lstSearch.Column(0) & ""
'Close the dialog box
DoCmd.Close acForm, "frmListBoxSearch"
End Sub
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
'Cancel and close the form
DoCmd.Close
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click
End Sub
This combo box simply goes to the record of the individual typed into the box or selected from the drop-down list – leaving all records accessible; e.g., bottom nav. bar displays Record 125 of 2987, etc.
On the main form I also have a button (just below the combo box above) which opens a listbox (see LIST BOX SPECIFICATIONS below) to more thoroughly search for an employee which is able to be sorted by different criteria; e.g., Active, Last Name, First Name, etc. This listbox also works fine. However, it does not leave all employee records accessible (like the combo box does using a record set) – it displays only the record(s) for the person selected – thereby disabling further use of the combo box – due to: "[qryMainScreenSelectRecord.EMPLOYEE_NUMBER]=" & "" & Me.lstSearch.Column(0) & "") – e.g., bottom nav. bar displays Record 1 of 1. I’ve tried creating a record set for the listbox, tried using DoCmd.GoToRecord, tried using DoCmd.FindRecord, etc. At this point, I can’t remember what I’ve tried or haven’t tried.
In any event, the short solution I’m looking for is to simply have the listbox, once it opens the Main form and finds the record the user selects, leave the remainder of the records available – so the combo box will still work. Any suggestions of where I need to add an “on close” requery, a record set setup, or any other code?
The longer solution (and preferred) would be to have one form, two boxes– the combo box would also filter the listbox (by last name only) as the user types in letters of the last name.
Any help will be greatly appreciated-
colleen
ComboBox CODE:
Event Handler After Update Code:
Private Sub Form_Current()
Me.ComboSelectEmployee.Value = Me.Employee_Number
End Sub
Private Sub ComboSelectEmployee_AfterUpdate()
On Error GoTo ErrorHandler
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Employee_Number]= " & Str(Nz(Me![ComboSelectEmployee].Value, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
CleanUpAndExit:
Exit Sub
ErrorHandler:
Call MsgBox("An error was encountered" & vbCrLf & _
vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Error Number: " & Err.Number, , "Error")
Resume CleanUpAndExit
End Sub
LIST BOX SPECIFICATIONS:
LISTBOX Row Source:
SELECT qryMainScreenSelectRecord.EMPLOYEE_NUMBER, qryMainScreenSelectRecord.FACULTY_Active, qryMainScreenSelectRecord.Last_Name, qryMainScreenSelectRecord.First_Name, qryMainScreenSelectRecord.Preferred_Name FROM qryMainScreenSelectRecord ORDER BY qryMainScreenSelectRecord.FACULTY_Active, Last_Name;
Event Handlers AfterUpdate and OnDoubleClick
Option Compare Database
Option Explicit
Private Function basOrderby(col As String, xorder As String) As Integer
Dim strSQL As String
'Clear captions from command buttons
ClearCaptions
'Set row source for list box
strSQL = "SELECT DISTINCTROW EMPLOYEE_NUMBER, FACULTY_Active, Last_Name, First_Name, Preferred_Name "
strSQL = strSQL & "FROM qryMainScreenSelectRecord "
strSQL = strSQL & "ORDER BY " & col & " " & xorder
Me!lstSearch.RowSource = strSQL
Me!lstSearch.Requery
End Function
Private Sub ClearCaptions()
'Clear captions of asc and desc symbols
Me!cmdOrderFactiveDesc.Caption = "Order by Active Status"
Me!cmdOrderFActive.Caption = "Order by Active Status"
Me!cmdOrderLNameDesc.Caption = "Order by Last Name"
Me!cmdOrderLName.Caption = "Order by Last Name"
Me!cmdOrderFNameDesc.Caption = "Order by First Name"
Me!cmdOrderFName.Caption = "Order by First Name"
Me!cmdOrderPNameDesc.Caption = "Order by Preferred Name"
Me!cmdOrderPName.Caption = "Order by Preferred Name"
End Sub
Private Sub cmdOrderFActive_Click()
'Set Active Status order in ascending order and apply captions
Dim response As Integer
response = basOrderby("FACULTY_Active", "asc")
Me!cmdOrderFactiveDesc.Visible = True
Me!cmdOrderFactiveDesc.Caption = "v Active Status v"
Me!cmdOrderFactiveDesc.SetFocus
Me!cmdOrderFActive.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderFActiveDesc_Click()
'Set Active Status order in descending order and apply captions
Dim response As Integer
response = basOrderby("FACULTY_Active", "DESC")
Me!cmdOrderFActive.Visible = True
Me!cmdOrderFActive.Caption = "^ Active Status ^"
Me!cmdOrderFActive.SetFocus
Me!cmdOrderFactiveDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderLName_Click()
'Set Last Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Last_Name", "asc")
Me!cmdOrderLNameDesc.Visible = True
Me!cmdOrderLNameDesc.Caption = "v Last Name v"
Me!cmdOrderLNameDesc.SetFocus
Me!cmdOrderLName.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderLNameDesc_Click()
'Set Last Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Last_Name", "DESC")
Me!cmdOrderLName.Visible = True
Me!cmdOrderLName.Caption = "^ Last Name ^"
Me!cmdOrderLName.SetFocus
Me!cmdOrderLNameDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderFName_Click()
'Set First Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("First_Name", "asc")
Me!cmdOrderFNameDesc.Visible = True
Me!cmdOrderFNameDesc.Caption = "v First Name v"
Me!cmdOrderFNameDesc.SetFocus
Me!cmdOrderFName.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderFNameDesc_Click()
'Set First Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("First_Name", "DESC")
Me!cmdOrderFName.Visible = True
Me!cmdOrderFName.Caption = "^ First Name ^"
Me!cmdOrderFName.SetFocus
Me!cmdOrderFNameDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderPName_Click()
'Set Preferred Name order in ascending order and apply captions
Dim response As Integer
response = basOrderby("Preferred_Name", "asc")
Me!cmdOrderPNameDesc.Visible = True
Me!cmdOrderPNameDesc.Caption = "v Preferred Name v"
Me!cmdOrderPNameDesc.SetFocus
Me!cmdOrderPName.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub cmdOrderPNameDesc_Click()
'Set Preferred Name order in descending order and apply captions
Dim response As Integer
response = basOrderby("Preferred_Name", "DESC")
Me!cmdOrderPName.Visible = True
Me!cmdOrderPName.Caption = "^ Preferred Name ^"
Me!cmdOrderPName.SetFocus
Me!cmdOrderPNameDesc.Visible = False
Me!lstSearch.SetFocus
End Sub
Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
ShowRecord.Enabled = True
End Sub
Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
If Not IsNull(lstSearch) Then
ShowRecord_Click
End If
End Sub
Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box
DoCmd.OpenForm "frmMainFacultyData", , , _
"[qryMainScreenSelectRecord.EMPLOYEE_NUMBER]=" & "" & Me.lstSearch.Column(0) & ""
'Close the dialog box
DoCmd.Close acForm, "frmListBoxSearch"
End Sub
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
'Cancel and close the form
DoCmd.Close
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click
End Sub
