Dynamically filter form - filter property too long

[cpberg1]

Hey all,
Working though one of my first attempts at making something happen with VBA, Access 2003. It's going pretty good so far but I've run into a small snag. I've got sample code for dynamically setting the filter property of a form using several unbound combos found online and adapted for use in my DB. The code below for 1st combo is the generic code.

My goal is to add a field to the unbound combo with a UNION join and then use VBA to set the filter to something other than 1 single field. The code in red for the second combo is my attempt at modifiying the CFILastNameField filter to include all the CFI's who aren't current. I'm pulling their CFIID along with the appropriate text for the filter property of the form (shown in 2nd combo code).

I have the UNION join working to display the alternate choice "departed" in the combo.

I believe I have the code working properly however,
My problem is that the filter property get's too long for Access to accept.

Is it possible to modify the filter property of a form in VBA and allow for a longer string? Other ideas?

The specific error I get is FIAllocation Function Error
2176 - the setting for this property is too long.

Thanks!

Private Function FIAllocationSearch()
On Error GoTo Error_FIAllocationSearch
Dim db As Database, FilterClause, strOutput As String, D As Long, strSQL As String, rsInactiveCFI As DAO.Recordset
 
'Hold whether we Use AND or OR in our Filter Criteria
D = Me.DirectionGrp.Value
 
'1st Combo - LastName - Assuming the Table Field 'LastName' is of a Text DataType
If Nz(Me.cmbLastNameSelect) > 0 Then
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[STD21LastName]='" & Me.cmbLastNameSelect.Value & "'"
End If
 
'2nd Combo - CFIName- Assuming the Table Field 'CFIName' is of a Text DataType
If Nz(Me.cmbCFILastNameSelect) > 0 Then
 
[COLOR=red] If FilterClause = (Departed) Then[/COLOR]
[COLOR=red]Set db = CurrentDb()[/COLOR]
[COLOR=red] strSQL = "SELECT CFI11Basic.CFIID From CFI11Basic WHERE (((CFI11Basic.CFI11MasterDisplay)=False));"[/COLOR]
[COLOR=red] Set rsInactiveCFI = db.OpenRecordset(strSQL)[/COLOR]
[COLOR=red]    Do Until rsInactiveCFI.EOF[/COLOR]
[COLOR=red]    strOutput = strOutput & "[CFIID] = " & rsInactiveCFI.Fields("CFIID") & " OR "[/COLOR]
[COLOR=red]    rsInactiveCFI.MoveNext[/COLOR]
[COLOR=red] Loop[/COLOR]
 
[COLOR=red] FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")[/COLOR]
[COLOR=red] FilterClause = FilterClause & strOutput[/COLOR]
[COLOR=red] End If[/COLOR]
If FilterClause <> "" Then FilterClause = FilterClause & IIf(D = 1, " AND ", " OR ")
FilterClause = FilterClause & "[CFIID]= " & Me.cmbCFILastNameSelect.Value
End If
 
 
If FilterClause = "" Then
Forms("FIAllocation")("FIallocation subform").Form.FilterOn = False
Else
'Fill this Form wide variable so that it can be used for
'the Report.
CurrentFilter = FilterClause: FilterClause = ""
'Place our created Filter Criteria into the Filter property of SubForm.
Forms("FIAllocation")("FIAllocation subform").Form.Filter = CurrentFilter
'Turn on the Filter
Forms("FIAllocation")("FIAllocation subform").Form.FilterOn = True
End If
Set rsInactiveCFI = Nothing
Set db = Nothing
Exit_FIAllocationSearch:
Exit Function
Error_FIAllocationSearch:
MsgBox "FIAllocation Function Error" & vbCr & vbCr & _
Err.Number & " - " & Err.Description, vbExclamation, _
"FI Allocation Error"
Resume Exit_FIAllocationSearch
End Function

For reference my strSQL returns about 150 CFIID's 1 to 3 digits each.

 

[spikepl]

Can I ask what this db does? The reason I am asking is because I am guessing that it relates to managing flight training. If that is the case, then I know some ppl tha thave developed a pretty comprehensive web-based system to cover CPL training , incl managing student allocation, curriculum, instructor allocation, management of history, logging of time, access also via iphone (done or underway) .the whole enchillada. The system is in use, but they either are offering it to others right now or will be, shortly. (Sadly, I do not get any cut of the sales). If this is of any interest then give a yell.

 

[cpberg1]

always interested in looking at something new. Can they do a demo? Value for the $$ always a big concern in the industry

Thanks for the heads up.

 

[cpberg1]

Can PM me details? Thanks spike!

 

[spikepl]

In fact they do ab-initio to ATPL with MCC. The system was developed by a flight academy, because they couldn't find anything on the market, but their primary business is not software, so they of course want to offset the costs.

I'll get back to you via PM as soon I extract some contact details from them - hopefully Monday.

 

[spikepl]

Done. (and the system requires at least 10 characters).