trying to filter recordset HELP

[powellm1976]

I need help filtering a table so I can use the .Filter. I need to filter for Fundsnum = 99 and can't figure out how to do it. I'm enclosing the code I'm working with and any comments / suggestions would be great

The long Criteria is what I was initially using till I figure out I had to use seek. The criteria in that string is what I eventually need the recordset to be filtered to.

Private Sub Command261_Click()
Dim TitleXXTotal As Double
Dim IOLTATotal As Double
Dim LSCTotal As Double
Dim StateTotal As Double
Dim AJATotal As Double
Dim CurrentRecord As Long
Dim x As Integer
Dim Criteria As String
Dim tblLinkedPath As String
Dim db As DAO.Database
Dim tblDf As DAO.TableDef
Dim intCount As Integer
Dim intcounter As Integer
Set collTableVals1 = New Collection
Set db = CurrentDb()
Set tblDf = db.TableDefs("_CostAllocationShiftAmount")
intCount = tblDf.Fields.Count - 1
For intcounter = 0 To intCount
If tblDf.Fields(intcounter).Name = "myID" Then
GoTo Jumpo:
End If
Next intcounter
CurrentDb.Execute "ALTER TABLE _CostAllocationShiftAmount ADD COLUMN myID AUTOINCREMENT CONSTRAINT PrimaryKey PRIMARY KEY;"
Jumpo:
Criteria = "SELECT [_CostAllocationShiftAmount].CASENUM, [_CostAllocationShiftAmount].myID, [_CostAllocationShiftAmount].[New Funding Source], [_CostAllocationShiftAmount].FUNDSNUM, [_CostAllocationShiftAmount].Amount FROM _CostAllocationShiftAmount WHERE ((([_CostAllocationShiftAmount].[New Funding Source]) = """" Or ([_CostAllocationShiftAmount].[New Funding Source]) Is Null) And (([_CostAllocationShiftAmount].FUNDSNUM) = 99)) ORDER BY [_CostAllocationShiftAmount].Amount;"
tblLinkedPath = CurrentDb.Name
Set Mydb = DBEngine(0).OpenDatabase(tblLinkedPath)
Set MySet = Mydb.OpenRecordset("_CostAllocationShiftAmount")
'Set Mydb = DBEngine.Workspaces(0).Databases(0)
'Set MySet = Mydb.OpenRecordset(Criteria, DB_OPEN_DYNASET)
If MySet.EOF And MySet.BOF Then
MsgBox "There are not matches for that time frame."
Exit Sub
Else
'MySet.MoveFirst
Do While Not MySet.EOF And TitleXXTotal <= (Me!Text229)
CurrentRecord = Rnd(1) * CInt(MySet.RecordCount)
With MySet


Criteria = MySet.RecordCount
.Index = "PrimaryKey"
.Seek "=", Val(CurrentRecord)
End With
If (TitleXXTotal + MySet!AMOUNT) <= (Me!Text229 * 1.03) Then
TitleXXTotal = TitleXXTotal + MySet!AMOUNT
strTemp = MySet!CASENUM & "TITLEXX"
collTableVals1.Add strTemp
MySet.Edit
MySet![New Funding Source] = 1
MySet.Update
End If
MySet.MoveNext
Loop
'Me!Text235 = (Me!Text229 - TitleXXTotal)
'Me!Text251 = (Me!Text235 / Me!Text229)
MySet.Close
End If
End Sub

Thanks,

Mark