filter a table record using VBA?

[jun90]

Hi! Anyone here knows how to filter a table record using VBA?

For example, I have a list of customers. I want to filter them to those living in the East only. How do i do it using VBA code?

Please reply.. Really need help here. Thanks!

 

[DCrake]

Create a query and apply a filter condition on the specified field. Easy.

David

 

[Khalid_Afridi]

Create a query and apply a filter condition on the specified field. Easy.

David

as simple is that

Through VBA you can also filter it, but you have to open a recordset using a select statement,connection,bla bla bla...

 

[jun90]

how do i create a query in VBA n run it den? i did this..

Dim Query As String
Query = "SELECT Clients.UserID FROM Clients WHERE Clients.Location = 'East' ORDER BY Clients.UserID;"
DoCmd.RunSQL Query

but it states a run-time error. "RunSQL action requires an argument consisting of an SQL statement"

 

[Khalid_Afridi]

how do i create a query in VBA n run it den? i did this..

if you just want to run a select query through VBA, then

Docmd.OpenQuery "YourQueryName"

don't use a reserve word "Query" in your statement:

Dim strSQL as string

strSQL = "SELECT Clients.UserID FROM Clients WHERE Clients.Location = 'East' ORDER BY Clients.UserID;"

Docmd.OpenQuery strSQL

 

[boblarson]

jun90:

And just as an FYI -

DoCmd.RunSQL is only good for ACTION queries (Update, Append, Delete). The DoCmd.OpenQuery as shown is the way to view a select query.

 

[jun90]

i did it.. but when i run the program, it states "runtime error '2001' you cancelled the previous operation"..

 

[vbaInet]

i did it.. but when i run the program, it states "runtime error '2001' you cancelled the previous operation"..

poste your sql string. you may be missing a notation

 

[jun90]

ok. i have just edited my codes.

here is it.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSelect As String
Set db = CurrentDb
Set qdf = db.QueryDefs("userFilter")

strSelect = "SELECT UserSettings.[UserID] FROM UserSettings WHERE UserSettings.[UserID]='" & LogUser & "';"
qdf.SQL = strSelect
DoCmd.SetWarnings False
DoCmd.OpenQuery "userFilter", acNormal, acEdit
DoCmd.SetWarnings True



the LogUser is a variable which i use to store a integer at the instance when the button is clicked. it is actually to track the userID of the particular user when the "Login Button" is pressed.

now i want to filter and open the query (userFilter) which only display the settings of that particular user. (i hope u get wat i mean). thanks for helping me.
and oh, when i click on the userFilter query on the Objects panel, it states data type mismatch in criteria expression.

 

[vbaInet]

i don't think you need to use a query def. try using only these:

Dim strSelect As String
strSelect = "SELECT UserSettings.[UserID] FROM UserSettings WHERE UserSettings.[UserID]='" & LogUser & "';"
DoCmd.SetWarnings False
DoCmd.OpenQuery "userFilter", acNormal, acEdit
DoCmd.SetWarnings True

it may work.

 

[jun90]

it somehow works.. but now appears the "Enter Parameter Value" for LogUser.. How do i get rid of that message box?

 

[vbaInet]

it somehow works.. but now appears the "Enter Parameter Value" for LogUser.. How do i get rid of that message box?

you were absolutely correct to use the query def. i wasn't thinking, apologies. revert to using your query def. replace your strSelect statement with this:

strSelect = "SELECT UserSettings.[UserID] FROM UserSettings WHERE UserSettings.[UserID]='" & Nz(CStr(LogUser), "") & "';"

make sure your LogUser variable is of a string data type

 

[jun90]

OMG! i got it!! i didn't define LogUser as a global variable. it works now. thanks for replying!

 

[Khalid_Afridi]

Dim strSelect As String
strSelect = "SELECT UserSettings.[UserID] FROM UserSettings WHERE UserSettings.[UserID]='" & LogUser & "';"
DoCmd.SetWarnings False
DoCmd.OpenQuery "userFilter", acNormal, acEdit
DoCmd.SetWarnings True

This should work.

No need for QueryDef, but where is your " & LogUser & " is it on the form or some variable?

 

[jun90]

This should work.

No need for QueryDef, but where is your " & LogUser & " is it on the form or some variable?

it is actually used to a temporary variable (just like 'count' in a loop). use for storing userID number.

 

[vbaInet]

This should work.

No need for QueryDef, but where is your " & LogUser & " is it on the form or some variable?

I corrected myself there Khalid. It wouldn't work without pointing the SQL string back to the query which you wish to run. The only way to do it is to use the querydefs collection.

 

[Khalid_Afridi]

try to store your variable in unbound control on a form or in a TempVars, then refer the variable in the query:

if you use the select query just to get the recordset of the selected 'LogUser' then:
Why you open another query i.e?

DoCmd.SetWarnings False
DoCmd.OpenQuery "userFilter", acNormal, acEdit
DoCmd.SetWarnings True

 

[vbaInet]

try to store your variable in unbound control on a form or in a TempVars, then refer the variable in the query:

if you use the select query just to get the recordset of the selected 'LogUser' then:
Why you open another query i.e?

I get your point but in this case the author of this thread wants to view the results of his query which is called userFilter. In any case, it's been resolved now

 

[Khalid_Afridi]

I get your point but in this case the author of this thread wants to view the results of his query which is called userFilter. In any case, it's been resolved now

Glad to hear that it has been resolved now... I was thinking for asking you to submit your db if possible, then I will go through and would resolved it according your situation.

see ya...

 

[vbaInet]

Glad to hear that it has been resolved now... I was thinking for asking you to submit your db if possible, then I will go through and would resolved it according your situation.

see ya...

Many thanks Khalid, but I am only a humble helper. I am not the person who created the thread. Cheerios!