I'm having a tedious difficulty with a form of mine.
The General Situation:
I have a form on which a group of employees track customers who are set to graduate our program in the current (or in this case next) calendar year. Each employee (administrator) in this group has a set of customers who they are responsible for. Since we are talking about a couple hundred records every year and multiple employees handling these customers, I thought it would be nice if they could filter the record source for this form upon opening.
The Specific Problem:
I generated some VBA Code for the On Open form even that allows the user to filter the records based on the initials of the employee assigned to those customers. This is a field in the table that is the record source for the form. That part works nicely. An input box pops up, telling them to enter the initials of the administrator they want to retrieve records for or press * for all records. However, after the user enters the initials of the administrator and presses enter, another input box shows up. This unscripted input box has the title "Enter Parameter Value" and the text is just the initials the user previously entered. One must enter the initials again in order for the correct record source to show up.
In fact this gets a little more confusing if you close and reopen the form. If you do this you are asked for the parameter value both before and after the filter selection inputbox appears. Below is the code I am using for the record source filtering:
Private Sub Form_Open(Cancel As Integer)
Dim strCA As String, strSQL As String, strWHERE As String
On Error GoTo ErrHandler
strSQL = "SELECT * FROM GandHTracker"
strCA = InputBox("Enter the CA for the paricipants you wish to view" & vbCr & vbCr & _
"VP, LM, KR, SH, or JS" & vbCr & "Or press * to view all participants", "Show All/Filter")
If strCA = "" Then
Cancel = True
ElseIf strCA = "*" Then
Me.RecordSource = strSQL
strWHERE = " WHERE CA Like " & strCA & ""
Me.RecordSource = strSQL & strWHERE
MsgBox Err.Number & ": " & Err.Description