Help!!! Need to Filter Records

[mforema]

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:

Private Sub Command257_Click()
DoCmd.OpenTable "A - General Details_Old Data", acViewNormal, acReadOnly
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....

[NeoPa]

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.

[mforema]

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!

[kepston]

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:

Private Sub Command257_Click()
DoCmd.OpenTable "A - General Details_Old Data", acViewNormal, acReadOnly
End Sub

Put:

Private Sub Command257_Click()
DoCmd.OpenForm "A - General Details_Old Data Form", acNormal,,"[Column Name]=" & Me.your_field_name_with_data_you_want_to_show, acFormReadOnly
End Sub

Note the double comma (skipping the optional FilterName ie Query name)
Hope that helps.

[NeoPa]

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 :)

[mforema]

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:

Private Sub Command257_Click()
DoCmd.OpenTable "A - General Details_Old Data", acViewNormal, acReadOnly
End Sub

Put:

Private Sub Command257_Click()
DoCmd.OpenForm "A - General Details_Old Data Form", acNormal,,"[Column Name]=" & Me.your_field_name_with_data_you_want_to_show, acFormReadOnly
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!

[NeoPa]

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 :)