By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Help!!! Need to Filter Records

P: 72
Hey Everybody,
I have a command button on a form that opens a table when clicked. The primary key in the table is [Column Name]. Of course, the table displays all of the records that are in it. However, I only want the table to show one of the records. The one record that shows will depend on its corresponding [Column Name]. Originally, the form shows information for each [Column Name]. So, when the user clicks the command button, I want the table information for the [Column Name] that is on the form to pop up. I want to filter out the extraneous records that do not match the current [Column Name] on the form.

I hope this makes sense. My code for the button is found below:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command257_Click()
  2. DoCmd.OpenTable "A - General Details_Old Data", acViewNormal, acReadOnly
  3. End Sub
What can I change to make it only select the record that corresponds to the [Column Name] on the form.

Thanks! I hope it makes sense....
Jun 12 '07 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,494
In that case you need to open a query instead of a table.
The query must have been previously saved with your selection of course.
Alternatively, for a single record, do it with a form using the filter option on open.
Jun 12 '07 #2

P: 72
In that case you need to open a query instead of a table.
The query must have been previously saved with your selection of course.
Alternatively, for a single record, do it with a form using the filter option on open.
OK. So, how do I set-up the query to open a specific record based on the [Column Name] on the form. Of course, the [Column Name] will vary as the user scrolls thru the form. How exactly do I set the criteria under [Column Name] in the query? I don't want to specify a specific [Column Name]; I want it to vary based on the current [Column Name] on the form.

Does that make sense? I'm a newbie with Access and VBA. Thanks!
Jun 13 '07 #3

Expert
P: 97
As NeoPa says
Originally Posted by NeoPa
Alternatively, for a single record, do it with a form using the filter option on open.
Create a form based on the table, e.g. 'A - General Details_Old Data Form'
Then for your command button OnClick event instead of:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command257_Click()
  2. DoCmd.OpenTable "A - General Details_Old Data", acViewNormal, acReadOnly
  3. End Sub
Put:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command257_Click()
  2. DoCmd.OpenForm "A - General Details_Old Data Form", acNormal,,"[Column Name]=" & Me.your_field_name_with_data_you_want_to_show, acFormReadOnly
  3. End Sub
Note the double comma (skipping the optional FilterName ie Query name)
Hope that helps.
Jun 13 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
That does make sense, and I didn't get this all properly before.
In this situation your best bet is to display the information you want on a separate form. Use the wizard(s) to help you create the form from the table, then specify the record when you open the form (in the WhereCondition parameter of the DoCmd.OpenForm() call).
Good luck :)
Jun 13 '07 #5

P: 72
As NeoPa says


Create a form based on the table, e.g. 'A - General Details_Old Data Form'
Then for your command button OnClick event instead of:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command257_Click()
  2. DoCmd.OpenTable "A - General Details_Old Data", acViewNormal, acReadOnly
  3. End Sub
Put:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command257_Click()
  2. DoCmd.OpenForm "A - General Details_Old Data Form", acNormal,,"[Column Name]=" & Me.your_field_name_with_data_you_want_to_show, acFormReadOnly
  3. End Sub
Note the double comma (skipping the optional FilterName ie Query name)
Hope that helps.
Hey Thanks NeoPa,
I will definitely try out your form option. I've already successfully created a Query that has the general criteria as follows: Forms![Form Name]![Control Name]. It works! It opens up a table that only shows the [Column Name] currently in the form.

However, your option would be better for the users. A form would look better and be more user-friendly than a table or query. So, I will run the idea by my boss. Thanks for your help!
Jun 13 '07 #6

NeoPa
Expert Mod 15k+
P: 31,494
Not a problem.
Another benefit of this approach is that it is more standard, and getting help on standard things is always much easier than getting help on the more esoteric concepts.
Good luck :)
Jun 13 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.