Filter

[hlieu]

Anybody out there know how to use filter method to filter the records in the database??

Here is my situation: I am trying to filter out the certain records in the Access table1 by using two forms. Form1 contains a text box and a command button, form2 contains all the text boxes that are already bound to table1. The object is to filter the record(s) based on the entry in the form1's text box and use it to populate form2.

Again form2 is presently containing ALL the records in table1 as it is bound to it. How do we get it to be totally unbound and then get it to populate ONLY the records that filtered and then rebind itself after our needs are finished.

I am using ADODC as a conection to the Access database.

Thanks.

[Elias]

What you want to do is retrieve a recordset using ADO. I have listed several examples in a previous question. The topic was Add/Edit/Delete/Save - using ADO. I think this is the URL: http://www.vb-world.net/forums/showthread.php?threadid=11832

Don't be confused by the large amount of code. Just look at the form_load subroutine. Then remember that to access the record you are currently pointing to, you use the following code. ie rstMain!Name, rstMain!Age, etc...

To traverse the recordset, use the .movenext, .moveprevious, .movefirst, .movelast methods.

ie rstMain.movefirst -> will take you to the first record. ( Assuming that records exist. ) To check for beginning or end of file check the rstMain.eof/rstMain.bof property ( True or False ) Hope that helps!

By the way, to answer your first question about filtering. Once you have a recordset, you can use the .filter method. The syntax is as follows: rstMain.filter "Name='Elias'"
Where Name is the field in the current record. To use wildcards use the following: rstMain.filter "Name='Elias*'"

-Elias

Edited by Elias on 03-17-2000 at 12:14 PM

[hlieu]

This is a code that I have, and it is giving me an error
(Invalid use of property)Dim Temp As String

Dim Temp as String
Dim rst As Recordset
Dim db As Database

Temp = txtYear3
Set db = OpenDatabase("c:\report card\temposchool.mdb")
Set rst = db.OpenRecordset("Grades", dbOpenSnapshot)

If rst.EOF = False Then

rst.Filter "StudentNam ='temp'"

End If


NOTE: txtYear3 is a text Box
StudentNam as a field in the table
table Grades

Can you what is wrong, please help

Thanks

[Clunietp]

You are using a constant, not a variable in your Filter statement:

Change this:

rst.Filter "StudentNam ='temp'"

To this:

rst.Filter "StudentNam ='" & temp & "'"

[hlieu]

I changed

From this:

rst.Filter "StudentNam ='temp'"

To this:

rst.Filter "StudentNam ='" & temp & "'"

and is still giving me an error(Invalid use of property) and it highlighted at the rst.Filter

[Elias]

Try this!

rst.Filter = "StudentNam ='" & temp & "'"

Invalid use of property seems to me that the filter is a string, and therefore; you need an equals sign.

If not:
Are you trying to store date's in temp? Post your code, this might help us figure out what's wrong.

-Elias

[Edited by Elias on 03-20-2000 at 01:28 PM]

[hlieu]

I am not trying to store any date in temp, txtYear3 is just a name of the text box that will take the input(StudentNam). What I am trying to do is to be able to filter out a certain student name in the database.

After I make the change, the error is gone, but I still cannot get the filtered records to populate to the form.



Option Explicit
Private mrst As Recordset

Private Sub DataGrades_Reposition()
Static mFirst As Boolean
If mFirst = False Then
Set mrst = DataGrades.Recordset
mFirst = True
End If
End Sub

Private Sub datPrimaryRS_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
StatusBar1.SimpleText = "Total Record(s): " + Str$(datPrimaryRS.Recordset.RecordCount) + " Record number: " + Str$(datPrimaryRS.Recordset.AbsolutePosition)
End Sub

Private Sub Form_Load()
Set grdDataGrid.DataSource = datPrimaryRS.Recordset("ChildCMD").UnderlyingValue
DataGrades.DatabaseName = "C:\Report Card\temposchool.mdb"
CrystalReport1.ReportFileName = "C:\Report Card\grammar.rpt"

End Sub

Private Sub Form_Unload(Cancel As Integer)
Screen.MousePointer = vbDefault
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub imgExit_Click()
frmMain.Show
frmGrammar.Hide
End Sub

Private Sub imgFind_Click()
frmGrammarFind.Show
End Sub

Private Sub imgLeft_Click()
On Error GoTo ErrHandling2
datPrimaryRS.Recordset.MovePrevious
linNoNext.Visible = False

Exit Sub

ErrHandling2:
If Err = 3021 Then ' No Current Record
linNoPrevious.Visible = True ' Cross out previous button
Else
linNoPrevious.Visible = False
End If
Resume Next
End Sub

Private Sub imgReport_Click()
CrystalReport1.PrintReport
'CrystalReport1.Action = 1
End Sub

Private Sub imgRight_Click()
On Error GoTo ErrHandling1
datPrimaryRS.Recordset.MoveNext
linNoPrevious.Visible = False
Exit Sub

ErrHandling1:
If Err = 3021 Then ' No Current Record
linNoNext.Visible = True ' Cross out next button
Else
linNoNext.Visible = False
End If
Resume Next
End Sub

Private Sub mnexit_Click()
End
End Sub

Private Sub mnuAdd_Click()

On Error GoTo AddErr
datPrimaryRS.Recordset.AddNew
txtFields(11).SetFocus
Exit Sub
AddErr:
MsgBox Err.Description

End Sub

Private Sub mnuDelete_Click()
Dim Temp As Integer

Temp = MsgBox("You are about to delete a record ", _
vbYesNo, " Report Card ! ")
If Temp = vbYes Then

With datPrimaryRS.Recordset
.Delete
.MoveNext
If .EOF Then
.MoveLast

End If
End With
Exit Sub

Else
Exit Sub
End If
End Sub

Private Sub mnuFirst_Click()
If datPrimaryRS.Recordset.BOF Then
MsgBox "This is the first record on the list.", _
vbInformation, "Report Card !"
Exit Sub
Else
datPrimaryRS.Recordset.MoveFirst
End If

End Sub

Private Sub mnuLast_Click()

If datPrimaryRS.Recordset.EOF Then
MsgBox "This is the last record on the list.", _
vbInformation, "Report Card !"
Exit Sub
Else
datPrimaryRS.Recordset.MoveLast
End If

End Sub

Private Sub mnumain_Click()
frmMain.Show
frmGrammar.Hide
End Sub

Private Sub mnuNext_Click()

' If datPrimaryRS.Recordset.EOF Then
' MsgBox "This is the last record on the list.", _
' vbInformation, "Report Card !"
' Exit Sub
' Else
'datPrimaryRS.Recordset.MoveNext
'End If

On Error GoTo ErrHandling1
datPrimaryRS.Recordset.MoveNext
'linNoPrevious.Visible = False
Exit Sub

ErrHandling1:
'If Err = 3021 Then ' No Current Record
'linNoNext.Visible = True ' Cross out next button
'Else
'linNoNext.Visible = False
'End If
Resume Next

End Sub

Private Sub mnuPrevious_Click()
If datPrimaryRS.Recordset.BOF Then
MsgBox "This is the first record on the list.", _
vbInformation, "Report Card !"
Exit Sub
Else
datPrimaryRS.Recordset.MovePrevious
End If

End Sub

Private Sub txtHome_Change()
Dim Temp1 As String
Dim Temp2 As Integer
Dim db As Database
Dim rst As Recordset

datPrimaryRS.Recordset.MoveFirst
Set db = OpenDatabase("c:\Report Card\temposchool.mdb")
Set rst = db.OpenRecordset("SELECT HomeroTea, SchoolYear " & _
"FROM Grades", dbOpenSnapshot)
If rst.NoMatch = False Then
datPrimaryRS.Recordset.Find ("HomeroTea = " & "'" & txtHome.Text & "'")
datPrimaryRS.Recordset.Find ("SchoolYear = " & "'" & txtYear1.Text & "'")
End If

End Sub

Private Sub txtStud_Change()
Dim Temp1 As String
Dim Temp2 As Integer
Dim db As Database
Dim rst As Recordset

datPrimaryRS.Recordset.MoveFirst
Set db = OpenDatabase("c:\Report Card\temposchool.mdb")
Set rst = db.OpenRecordset("SELECT StudentNam, SchoolYear " & _
"FROM Grades", dbOpenSnapshot)
If rst.NoMatch = False Then
datPrimaryRS.Recordset.Find ("StudentNam = " & "'" & txtStud.Text & "'")
datPrimaryRS.Recordset.Find ("SchoolYear = " & "'" & txtYear1.Text & "'")
End If

End Sub



Private Sub txtYear3_Change()
Dim Temp As String
Dim rst As Recordset
Dim db As Database

Temp = txtYear3
Set db = OpenDatabase("c:\report card\temposchool.mdb")
Set rst = db.OpenRecordset("Grades", dbOpenSnapshot)

If rst.EOF = False Then
rst.Filter = "StudentNam = '" & Temp & "'"

End If

End Sub