Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Data1.Recordset.Filter ????????

Can anyone tell me what this is for? And how to use it? There seems to be no specific help for this on the MSDN cds...

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
how can i have sure that this operation adds the record after the last one of the table, and not somewhere else ?

Data1.recordset Problem?
here is the code sequence.

data1.enabled = False

my question is y we disable the data1 (data control)....but after that we still can call the AddNew function?? confuse???

issit we just diable the user interface generated event...but we still can code it in statement???


Data1.recordset.findfirst Causes GPF
Hi all,

When I use

data1.recordset.FindFirst "[Date] = #3/24/99#"

to find a record in database and the table does contain a record matching it, a gpf occurs with error msg "read 0x0000:0000" (no gpf if no record matching it). However, if I use MoveFirst and MoveNext to go through the table, everything OK. Any idea?

Thank you in advance!


Im adding info into a database via textboxes with this simple code:

VB Code:

But I have a brain fart and cant determine why I get this error:

Data1.Recordset.Update Question
when i do this it will give an error:

this action was cancelled by an ascociated object

how can i solve this?

Problem With Data1.Recordset.? Properties...

There is a problem about Datacontrolís recordset.?.When I have to write Data1.Recordset then push (.) dot,there isnít open any control property in code window(with code combo).Then I write Data1.Recordset.Addnew/Update/or something else and the error occurs when program running(Compile error:Error in loading DLL).Maybe I have to do something wrong. I donít understand the problem.I have send an attachment(simple example about my problem) below.If you can operate it,please send me the answer...Thanks...

Data1.recordset!Status = True
Hy All,
I have a very simple data1.recordset (with 12 fields, but simple) I used for many weeks long.
with data1.recordset

!lastname = text1
!firstname = text2
and so on ...

end with

 Suddenly (maybe I modified anything, but I don't remember if and where) it starts to give me an error message like: "Element not included in this ensamble"; help says I have to check if field really exists and if any to see if there are two fields with same name which can be not univoc indication.
I have checked carefully all fields in Access, they exist absolutely, and no one of them have the same name.
I have another problem (maybe connected with the first): one fields is:
!Status = True
it is a Yes/No field (True/False).
It never worked. It should put an X in the Access cell to say condition True, but I never saw this X.
I think the code:
!Status = True
is not correct.
Can anyone tell me how to save a field like this (True/False) with a small example ?

Thanks in advance.


Data1.Recordset.AddNew Command
i dunno what the hell is wrong but im tryin to add some data to my database and for some stupid reason i keep getting this error message Run time error '3426' this action was canceled by an associated object

then it highlights the line


here's my code

Data1.Recordset.Fields(0) = Text1.Text
Data1.Recordset.Fields(1) = Text2.Text

I've also tried it as

Data1.recordset.fields("Property name")=text1.text

but i still get the same message has anyone any idea why, and how i can solve this?


helen xx

Run-time Error '91' (data1.recordset.addnew)
VB Code:
Run-time error '91' Object variable or With block variable not set.data1.recordset.addnew

Someone want to explain why I get this error? I have used this code before without any problems. And now out of no where

OpenDatabase(something)/open Recordset And Data1.DatabaseName
Hi guys,

i have this question i would like to know.Maybe it's a stupid question, but still, can anyone help me on this?
When should i open a database like:

dim dbclients as databasename
dim rsclients as recordset

set dbclients =open databasename(App.path &"something.mdb")
set rsclients=open recordset("one_recordset")

and when should i use a data control and do something like:

data1.databasename =App.path & "something.mdb"
data1.recordset="select * from clients "

what's the difference?

thanks in advance,

* Marta Oliveira *
   CPC_TA- Braga

Binary Search Data1.Recordset ... 1 Line Of Code Needed
HELP!! Ive tried to do a binary search on the following Access Database through Data1.recordset.
I think that only one line of code needs to be changed: how to specify a Long Variable type to equal the first record in the database.

Its highlighted in red below, I think this is all that needs to be changed, but am not sure.....

Option Explicit

Dim db As Database
Dim rs As Recordset

Private Sub Command1_Click()
Dim hit As Boolean, fTop As Long
Dim fBott As Long, fTmp As Long

If txtSearch.Text = "" Then
MsgBox "Nothing to do."
Exit Sub
End If
hit = False
fTop = Data1.Recordset.RecordCount - 1
fBott = 0
fTmp = (fTop + fBott) / 2

Do While (Not hit) And (fTop >= fBott)
Data1.Recordset. = fTmp

If Data1.Recordset("Staff Number") = txtSearch.Text Then
hit = True
ElseIf Text1.Text < Data1.Recordset("Staff Number") Then
fTop = fTmp - 1
fBott = fTmp + 1
End If
fTmp = (fTop + fBott) / 2
If Not hit Then
MsgBox "No match"
txtSurname.Text = Data1.Recordset("Surname")

End If
End Sub

&"This Action Was Cancelled By An Associated Object&"(data1.recordset.addnew)
"This action was cancelled by an associated object"

VB Code:

I cant find the error anywhere in my code, anyone have any ideas.
I only get this error when the database is fully blank, anyon ehave any ideas?

&"This Action Was Cancelled By An Associated Object&"(data1.recordset.addnew)
"This action was cancelled by an associated object"

VB Code:

What Is The Adodc Equivalent Of &"data1.recordset.edit&" ?
what is the adodc equivalent of "data1.recordset.edit" ?
please help

Filter Recordset

I am attempting to filter an ADO recordset (based on an Access table). The database field I am attempting to filter is always 30 characters long (and each character is always either Y or N). What I am attempting to do is filter all records using a SQL command where say the 5th character is a Y. Wildcards don’t seem to do the trick but I think I need to count each character in the field until I hit say the 5th character and compare it. Any help would be gratefully appreciated as to what statements are needed.

Recordset Filter
Is it possible to set a filter on a recordset so based on values in that recordset?

This works

rsCompare.Filter = "[" & rsCompare.Fields(2).Name & "] <> 0
This does not

rsCompare.Filter = "[" & rsCompare.Fields(2).Name & "] <> [" & rsCompare.Fields(3).Name & "]"
Is there a way to make it work?

Thank you



"Education is a progressive discovery of our own ignorance." - Will Durant

Filter Recordset
how do i filter a recordset using something like this.

rsItem.Filter = left(rsItem(0).name,len(text1)) = trim(text1)

or perhaps this one's better

rsItem.Filter = rsItem(0).name LIKE trim(text1) &amp; "*"

i cant properply position qoutes and single qoutes.

i intend to extract only the records that matches the value of my text box.

thank you!

Filter Recordset

I want to transmit a recordset byVal.

rstplan.Filter = "fgl_fgt_id = 1 or fgl_fgt_id = 3"
Set rstDebit = rstplan
rstplan.Filter = ""
Wen I remove filter from rstplan, rstDebit is not filtered.
What is wrong?

Recordset Filter
Can anyone tell me why this first code filters just fine, but the second one I get an error< object variable or with block variable not set?

this works fine:

Private Sub Form_Load()
Dim d As Date
d = Date
While Weekday(d) <> 6
d = d + 1
txtDate.Text = Format$(d, "mm/dd/yy")
DataGrid1.Caption = ("ITEMS ORDERED FOR: " & txtDate.Text)
dbOrderQuery.Recordset.Filter = "order_date = '" & txtDate.Text & "'"
End Sub

This does not work:

Private Sub cboDate_Click(Area As Integer)
Adodc2.Recordset.Filter = "order_date = '" & cboDate.Text & "'"
End Sub

I load the data combo box with the dates from an Access table, I want the user to be able to click the date to filter the datagrid.

Dale S

Filter Recordset
Hi can someone help me filter a recordset and order it by ControlDate would that be possible? Thanks

Ado Recordset Filter
I have a bit of a complex filter I want to put on an ado recordset.
in sql I would write it like this:
...where ((d<b and d>a) or (c<b and c>a) or (d>b and c<a))
can I use something like this as a filter for an ado recordset?
if so, how do I write it?

Recordset. Filter
Hello all,

Iīm using recordset.filter command to search for records.
Per example:

rs1.filter= adfilternone
rs1.filter = "Name like '*john*' "

and this works very well,
Now I want to do something like this

rs1.filter= adfilternone
rs1.filter = "Name like '*Fr*nk*' "

and a possible result should be Frank
but this return a error message...

Can somebody help,

Merry Christmas!

can anybody give me a quick example of how to use the filter property to, lets say find all names beginning with "s" in a field called names.

thank you.

Filter In Ado Recordset
does anyone know if "filter" on a recordset from oracle works ?


ADO Recordset Filter

I have created an ADO recordset and I am trying to run a filter on this, this is the code I have used:

rstSellingValues.Filter "ItemID = " & rstItems!ItemID

The error I receive is:

Wrong number of arguments or invalid property assignment

I have used to filter(the same way as above) in other places in my program and it works fine.

What am I doing wrong????

ADO Recordset Filter
Do we have subquery sort of stuff in Ado Recordset filter property???

Subquery for eg.:
select fieldnames from master table where fieldname in(select fieldname from child table where condition)

Filter ADO Recordset

i have a ms access table with [Code] field containing the following values:
D1, D3, D5, D6, D7, W1, W2, HP-D1, HP-D2, G-TSE, G-TSD, G-ASE, G-ASD, G-ASV

here is a part of my code:

Dim selGable As String
selGable = Left(rs3("Type"), 3)

rs2.Filter = "Code LIKE 'D*' OR Code LIKE 'W*' OR Code LIKE 'H*' OR Code LIKE '*" & selGable & "'"

Set DataGrid1.DataSource = rs2

i want to filter my recordset so it will show all records with [Code] beginning D,W, H and only G-TSE or G-TSD or G-ASE or G-ASD or G-ASV.

right now it shows all records regarding of the selGable value.

this part of code:
rs2.Filter = "Code LIKE 'D*' OR Code LIKE 'W*' OR Code LIKE 'H*'"

works OK, but if i add:
or Code LIKE '*" & selGable & "'
it doesn't.

any idea, please?

Filter ADO Recordset
i have one field "Type" in  ADO recordset (reading text field from Access table and showing it in datagrid)

this field can have values like (xxx is extension/number):
ASE xxx
ASD xxx
ASV xxx
TSE xxx
TSD xxx
TSV xxx

i have five checkboxes on form (chAS, chTS, chE, chV, chD) - as default all have value 1 (checked) and all data are displayed.
i want to set filter for ado recordset, so an user should be able to select dislayed record in datagrid by checking/unchecking these check boxes.

my code is now like:
    If chTS.Value = 0 Then
    rs3.Filter = "Type='AS*'"
    End If
    If chAS.Value = 0 Then
    rs3.Filter = "Type='TS*'"
    End If
    If chE.Value = 0 Then
    rs3.Filter = "Type='ASD*' OR Type='ASV*'"
    End If
    If chD.Value = 0 Then
    rs3.Filter = "Type='ASE*' OR Type='ASV*'"
    End If
    If chV.Value = 0 Then
    rs3.Filter = "Type='ASD*' OR Type='ASE*'"
    End If

but only first two are working.

what would be the best way to do it properly? any ideas, please?

Filter One Recordset With Another
Hope someone can help me with this

I've been trying to display a list of class categories and their details in a repeating region on my page from one recordset. Within each region (eg. a class category) I would like to list all of the equipment items associated with that particular class category.

So I have two recordsets, one called classdetails, which contains the class category detail fields and the reference (class_category_id), and one for equipment records (equipment) based on the foriegn key (classcategory).

How To Filter A Recordset
Hey Guy's

I'm trying to figure out how to filter a recordset.
I managed to SORT the GridControl/database by using this:

DE1.rsCommand1.Sort = "[Entry Fee Payed ?] asc"

which works fine, I've tried messing arround with

DE1.rsCommand1.Filter = [Entry Fee Payed ?]

but am not quiet sure how to make this work.
Any help is greatly appreciated.


Recordset Filter
hi guys !

Need some advise.....

There is text box "txtdata" whose recordsource is "Adodc1" connected to table "type" in data.mdb

Now Iam using "Adodc2" to bring data to a datagrid "datagrid1" connected to table "type" in data.mdb

Now what I want is when I press "cmddata"
1) datagrid become visible
2) I refreshes adodc2
3) It filters the table with value in "txtdata" and show that in datagrid1

so my code.....

Private Sub cmddata_click()
   On Error Resume Next
   Datagrid1.Visible = True
Adodc2.Recordset.Filter = " name = 'txtdata.Text'"
If Adodc3.Recordset.EOF = True And Adodc3.Recordset.BOF = True Then
MsgBox "Not found"
 Set Datagrid1.DataSource = Adodc2
 End If
End Sub

But it never filters on the Msgbox comes even when the Text in txtdata is correct.

Any Ideas ?



I am just wondering, how to combine both AND and OR in Recordset.Filter.

If I do:

rs.Filter = "EmpName = 'Ryu' OR EmpID='123' OR EmpSal>80000"
it works. BUT if I do:

rs.Filter = "EmpName = 'Ryu' OR EmpID='123' AND EmpSal>80000"
it throws an error:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Any ideas on how to fix this?

Thanks for any help from the GURUs in advance.


ADO Recordset Filter
I am trying to prompt the user for a year and then use that year to select records from a recordset which I then want to display in a data grid. If I use rs.Filter = "fieldname = 1999"
the grid WILL display only those fields from the Access database with a year of 1999.(this is what I expect) Becasue the year changes with each use, when I get the year entered in from the user, I store it in a variable "gvarsubyear." When I use rs.Filter = "fieldname = &gvarsubyear", the data grid shows ALL records in the file with the arrow pointing to the first record that matches the year the user entered in. How can I use a variable for the year and have only records with years matching the variable show in the Data grid?



Recordset Filter Like Problem
i have a problem concerning about the .filter like method

i want to filter the data :
left side right side
--------- ---------
PRIOR1 period1
PRIOR2 period2
.. period3
.. ... and so on up to period 12

stmp = PRIOR1

when i select the PRIOR10 using the .filter method, it's working find
but the problem comes in :::
if i select the PRIOR1 and using .filter like method (.find 'cat' like 'PRIOR1*')
then the PRIOR10 will be included...

how can i solve that !

RecordSet Filter Bizarre
This is vexing.

I have a text box that users can enter an inventory number into, click the search button and get the product or products with that inventory number stuffed in a list box. Easy right?

The trouble is, the filter works only sometimes, and without any discernable pattern. When the filter is applied to a perfectly real inventory number, it can bring back an empty recordset. Sometimes it brings back the expected record.

There aren't any single quotes or double quotes in the field to muck it up. Some have dashes and dots, but its just plain ol' everyday text.

Is there some kind of issue with Filter that I should know about?

I assume that its slower to close the recordset, reset the SQL, and then open the recordset, rather than just filter it. If i can't get this to behave, I may have to do that. ick.

Private Sub cmdInvNum_Click()
If txtInvNum.Text = "" Then
MsgBox "Please Enter an Inventory Number to search on"
Else = "InvNumber = '" & Trim(txtInvNum.Text) & "'"
Debug.Print 'this gives back what is expected
'now show the result set
ProductResults 'this takes the rs and populates the list box
End If

End Sub

Can't Remove Filter From Recordset
Since I need to do something really quick, so this is my first time to allow VB retrieves the data for me automatically by using data control (Data Grid). But got into trouble...

Here is the code:

Private Sub txtFilter_KeyPress(KeyAscii As Integer)
If KeyAscii = CARRIAGE Then 'CARRIAGE is a user-defined global constant for ASCII Code 13
If txtFilter.Text = "" Then
MsgBox "Please enter a value to filter.", vbInformation
rsCommand.Filter = cbxFilter.Text & " Like '*" & txtFilter.Text & "*'"
End If
End If
End Sub

Private Sub cmdRefresh_Click()
rsCommand.Filter = ""
End Sub

The KeyPress event will do the filtering based on the value in TextBox txtFilter, it works fine. The problem is with the Refresh button, I want the users to click it and the filter will remove. But the result is, I got 0 record in the Data Grid once the program assigns blank to the filter property.

I read through the document, even tried the constant adFilterNone, but getting 0 record again.

Now I have to do something really ugly to make it work:

rsCommand.Filter = cbxFilter.List(1) & " <> '*&#$#($##DKJKDS'"

So this trick will retrieve all records with a specified field not having the value of some junk, which will probably work for the rest of the system's life. But really don't want to do something like this.

Anyone knows why I'm getting no record when trying to remove the filter?

Multiple Filter A Recordset
Hello all,

I'm sure this has been asked several times but i can't seem to find it, so i'll ask anyway, see what comes up.

I have a fairly major large table and i have a fairly large range of options a user would need to filter it by (all selected when as it is needed i.e. not all the options are selected at any one time, it is all very flexible. one option can be chosen or all of them at once, depending on requirements.)

one of my options is obviously to build a large SQL string and run a recordset on the table to bring back the records i need, however, i know this will take a very long time.

what i would like to do is filter my recordset with one criteria, then filter it again further, then again and again for as many criteria sets as i have until i have a drawn down recordset with just those records i want that fit my criteria. I know this will be much faster on my search but i know that a recordset does not allow more than 1 filter. the only other option i have is to start dumping the data into tables and do it that way, but i am reluctant.

any help would be great.



ADO.Recordset.Filter Criteria
I am using the Recordset.Filter property of a database to filter the records that I am using to draw a listview control.

I have added a search box to my form and I want to .filter based on my search.

If the user inputs "Blackbox 1008" into the search box, I break all of the words out into an array that has (Blackbox, 1008). I also have an array of all of the fields that i wish to search on (Manufacturer, PartNumber, SerialNumber).

Logically, the filter would look like this:

(Manufacturer = 'Blackbox' OR PartNumber = 'Blackbox' OR SerialNumber = 'Blackbox') AND (Manufacturer = '1008' OR PartNumber = '1008' OR SerialNumber = '1008)

Unfortunately, the ADO Recordset.Filter property only allows OR at the lowest level of the filter. Because of this, I basically have to create every possible combination of outcomes.

I can't even begin to imagine the algorithm to do this for what seems to be such a simple task. Has anyone encountered this and determined a better approach than the one I am contemplating?

Any help is greatly appreciated!


HELP!!! - Recordset.Filter Problem

I am filtering date range using this code:
FieldName = "bday"
frmMain.AdoDCFind(0).Recordset.Filter = FieldName >= #" & DateStart & "#" <= #" & DateEnd & "#"


As well as this

FieldName = "bday"

frmMain.AdoDCFind(0).Recordset.Filter = FieldName & "BETWEEN #" & DateStart & "# AND #" & DateEnd & "#"


I get an error "Arguments are of wrong type and out of acceptable range ...."
Have I done it correctly?? Please advise Thanks!

ADO Recordset Filter Date

I am new in visual basic and I am doing this employee record system which can filter employee's employment history including its Hired Date and End Date. I get the error "filter cannot be opened" and its pointing in this lines of code:
Case "Hired Date"
FieldName = "HiredDate"
AdoDCFindEmployment.Recordset.Filter = FieldName & " LIKE '*" & txtFilterEmployment & "*'"
txtFilterEmployment - text box that containsthe date to be searched
Hired Date - an item of a list box
HiredDate - name of the column/fieldname

In my other filter option that has a varchar datatype in SQL database works, but with date/time and bit it didn't. What shall I do? I alreadry tried converting the FieldName into sting using Str$(). What I really want is for the user to have an option to search for a particular year or date an Employee was hired. Your prompt response is highly appreciated.

Please see the attached image to have an idea on what i am talking about.


Thank you,


Ado Recordset Filter - MSHFlexgrid

I use this function to filter an ado recordset:

Public Function FilterField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset

' Set a filter on the specified Recordset object and then
' open a new Recordset object.
rstTemp.Filter = strField & " = '" & strFilter & "'"
Set FilterField = rstTemp

End Function

I call the function like this:

Dim newRst As ADODB.Recordset
Set newRst = FilterField(rst, Me.comboFields.Text, Me.comboValue.Text)

Then I count the filtered records:

Dim lngRecords As Long
lngRecords = newRst.RecordCount
MsgBox lngRecords

Then I try to populate my MSHFlexgrid with the filtered data:

Set Me.MSHFlexgrid1.DataSource = newRst

The strange thing is that The recordcount and records displayed in the grid doesnt match....

The RecordCount might be 200, but the number of records in the grid is only 5

If I try to print newRst to a file all the records are there...

Anyone know what the problem might be?


About Recordset PageSize And Filter
Hi All,

Just a quick question as I don't have VS on hand now.

If I need to paging a recordset, I use the PageSize (say =10) and AbsolutePage properties to achieve.

Will the result still correct (i.e. 10 record a a page) if I apply also the Filter property in advance?

i.e. I'm not filtering in SQL, but filter it in the recordset, then apply Paging.

Million Thanks!

Speeding Up Recordset Filter
I am currently retieving a recordset set that contains sales data and all this data is displayed in a grid on screen.

At times this data needs to be filtered by product type ect. I have done this using .filter on my ADODB recordset.

rstRecordset.Filter = adFilterNone
rstRecordset.Filter = "(ProductCode='3M-6406')"

It returns the correct number of records to the grid but takes an age in doing so. Am I not doing something and is there a way to speed this up.

If there is no way to speed this up alternative ways to achieve what I want would be greatly appritiated

Many thanks in advance


Recordset Filter And DataCombo
Hi all

I hae a DataCombo in my form. I have set its property to link with the table (RecordSet).

Now when I filter that RecordSet, and Perform DataCombo.Refill or DataCombo.Refresh, then nothing happens.

Means there are none of the problemts with the Recordset filter, bu the DataCombo show all the records rather than Filtered Records.

Please help me

De1.rsAuthors.filter = ""
De1.rsAuthors.filter = "Name =' " & somename & " ' "

IN this code , u can see that I have filtered the recordset. and counter check it with msgbox (its works fine the msgbox show the total number of filtered record) but the dataCombo does not popullate with the Filtered records.....

Million thanks in Advance

Recordset Filter Issue
I have a recordset filter applied in my code where I want to filter all the records in a recordset to ones where they have a field that contains a string in some part of it.

Here is my filter:

VB Code:
rsCustomers.Filter = "COMPANY_NAME Like '%" & CStr(Criteria) & "%'"

when I try to do rsCustomers.movefirst, I always get an error of "No Current Record", however when I step through the program, it shows rsCustomers.Filter = "COMPANY_NAME Like '%eng%'"

Please help.

Recordset.Filter Problem
I am trying to set a filter to a recordset in my code, however it seems that the recordset is not getting filtered, and I am not getting any errors on the filter line of code. Here is my code... please see if you can find the mistake:

VB Code:
Set rsLOCATIONS = dbMyDB.OpenRecordset("tblCOMPANY_LOCATION", dbOpenDynaset)    Set rsCONTACTS = dbMyDB.OpenRecordset("tblCOMPANY_CONTACT", dbOpenDynaset)        rsLOCATIONS.Filter = "COMPANY_ID = COMPANYID"        MsgBox rsLOCATIONS.RecordCount

COMPANYID is a global variable that I have set earlier in the code. And the msgbox displays the full count of records in the table.

Filter Property In Recordset?
I m using dynamic recordset (ADODB) . i have added some fields in it and set the data type of all as adVariant. I was getting problem if i try to set the datatype adVarchar.

Now the main problem is with Filter property. I have added one record in it and now i want to use the filter property.
The syntax i used is
rsDyna.filter = "SettingName='MySetting'

The error i was getting is "filter can not be opened". What should be the problem? Plz if u know the soln, help me.

Filter Within Filtered Recordset
Let's say I have a recordset containing 5 records:

Key Description

Now I apply a filter as follows:

VB Code:
rs.Filter = "Description LIKE '%" & "2" & "%'"

The recordset now contains 3 records (key 2, 3, 4). Now I want to apply an additional filter on the already filter recordset:

VB Code:
rs.Filter = "Description LIKE '%" & "3" & "%'"

I would expect to only get key record 3 as a result. However it seems that my filter is being reset and I get key record 3 and 5 in my result.

I am not setting the filter to adFilterNone between filters.

Any suggestions as to why this happens.


ADO Recordset Filter Wildcard?
Can you use a wildcard in an ADO Recordset Filter?

Copyright © 2005-08, All rights reserved