How do I filter a datasheet using VBA?

G

Guest

Greetings.

I'm trying to write a VBA procedure to apply a filter to a form when it is
opened by a user. I only want to filter on one field. In this case, PROJ_ID =
6000. Sounds simple. The coding I tried to use, unsuccessfully, was:


Private Sub Form_Open(Cancel As Integer)

Me.OnFilter = "[PROJ_ID]='6000'"

End Sub


I've also tried...

Private Sub Form_Open(Cancel As Integer)

Me.Filter = "[PROJ_ID]='6000'"

End Sub


Instead, I still see all projects listed in the datasheet. What am I doing
wrong and what code should I use to filter out just the one project?

Sincerely,
Dirk

North/South Consultants Inc.
Winnipeg, Canada
 
G

Guest

Hi Ofer.

It didnt' work. If anything, it bumped me into debug mode and crashed Access
(I'm using Access 2003).

After regaining conciousness ... I tried manually filtering the project ID
of 6000. In the properties box for the form, the Filter property shows:

(([qry Reports].PROJ_ID=6000))

So I'm not sure whether to include the (()) or what to do. Any other
suggestions?


Dirk
Winnipeg, Canada


Ofer said:
Try this

Me.Filter = "[TableName].[FieldInTableName]='6000'"
Me.FilterOn = True



rupertsland said:
Greetings.

I'm trying to write a VBA procedure to apply a filter to a form when it is
opened by a user. I only want to filter on one field. In this case, PROJ_ID =
6000. Sounds simple. The coding I tried to use, unsuccessfully, was:


Private Sub Form_Open(Cancel As Integer)

Me.OnFilter = "[PROJ_ID]='6000'"

End Sub


I've also tried...

Private Sub Form_Open(Cancel As Integer)

Me.Filter = "[PROJ_ID]='6000'"

End Sub


Instead, I still see all projects listed in the datasheet. What am I doing
wrong and what code should I use to filter out just the one project?

Sincerely,
Dirk

North/South Consultants Inc.
Winnipeg, Canada
Click to expand...
Click to expand...
 
G

Guest

So did you write on the on open event of the form the code:

Me.Filter = "(([qry Reports].PROJ_ID=6000))"
Me.FilterOn = True


rupertsland said:
Hi Ofer.

It didnt' work. If anything, it bumped me into debug mode and crashed Access
(I'm using Access 2003).

After regaining conciousness ... I tried manually filtering the project ID
of 6000. In the properties box for the form, the Filter property shows:

(([qry Reports].PROJ_ID=6000))

So I'm not sure whether to include the (()) or what to do. Any other
suggestions?


Dirk
Winnipeg, Canada


Ofer said:
Try this

Me.Filter = "[TableName].[FieldInTableName]='6000'"
Me.FilterOn = True



rupertsland said:
Greetings.

I'm trying to write a VBA procedure to apply a filter to a form when it is
opened by a user. I only want to filter on one field. In this case, PROJ_ID =
6000. Sounds simple. The coding I tried to use, unsuccessfully, was:


Private Sub Form_Open(Cancel As Integer)

Me.OnFilter = "[PROJ_ID]='6000'"

End Sub


I've also tried...

Private Sub Form_Open(Cancel As Integer)

Me.Filter = "[PROJ_ID]='6000'"

End Sub


Instead, I still see all projects listed in the datasheet. What am I doing
wrong and what code should I use to filter out just the one project?

Sincerely,
Dirk

North/South Consultants Inc.
Winnipeg, Canada
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ofer,

Thank you ... this worked beautifully!

Cheers,
Dirk
Winnipeg, Canada


Ofer said:
So did you write on the on open event of the form the code:

Me.Filter = "(([qry Reports].PROJ_ID=6000))"
Me.FilterOn = True


rupertsland said:
Hi Ofer.

It didnt' work. If anything, it bumped me into debug mode and crashed Access
(I'm using Access 2003).

After regaining conciousness ... I tried manually filtering the project ID
of 6000. In the properties box for the form, the Filter property shows:

(([qry Reports].PROJ_ID=6000))

So I'm not sure whether to include the (()) or what to do. Any other
suggestions?


Dirk
Winnipeg, Canada


Ofer said:
Try this

Me.Filter = "[TableName].[FieldInTableName]='6000'"
Me.FilterOn = True



:

Greetings.

I'm trying to write a VBA procedure to apply a filter to a form when it is
opened by a user. I only want to filter on one field. In this case, PROJ_ID =
6000. Sounds simple. The coding I tried to use, unsuccessfully, was:


Private Sub Form_Open(Cancel As Integer)

Me.OnFilter = "[PROJ_ID]='6000'"

End Sub


I've also tried...

Private Sub Form_Open(Cancel As Integer)

Me.Filter = "[PROJ_ID]='6000'"

End Sub


Instead, I still see all projects listed in the datasheet. What am I doing
wrong and what code should I use to filter out just the one project?

Sincerely,
Dirk

North/South Consultants Inc.
Winnipeg, Canada
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter a popup form 2
Add a Label or Textbox with VBA 4
Get the sum of Filtered form 0
MS Access VBA Parse data between two forms. 2
Message Box to Display a count of Records 3
How can I filter by Dates on a Form? 6
Problem with Filters 6
Allowing form combo box to show all records 3

Top