Opening forms (using VBA)

[ChrisW]

Hi,

Apologies in advance if this should have been posted to a VBA group
instead.

I've got a relatively simple database, with all options available from
the Welcome form. There is just 1 other form. All of the controls on
the open form open this form using the DoCmd.OpenForm method, and
using the FilterName argument using queries in the database. However,
one of the controls on the welcome form is a combobox, on which one of
the queries is run (the selected option forms the WHERE clause). This
selects results from tables which have a many:many relationship, so I
then run another query selecting just the information I need from the
appropriate table (which is the table the results form is based on - I
have a subform on the results form to deal with the relationship).
When I open the results form using the combobox, it opens the results
form showing *all* the records, not just the ones relating to the
selected option.

I assume that Access doesn't do things logically, and opens the form,
then runs the query (but by that time it's obviously too late to limit
the records in the form!). How do I get round this problem?!

I hope this makes sense..!

Thanks in advance,
Chris

 

[Golfinray]

Try using a filter on the combo. Whatever you want the user to select, make
that the choices of the combo. Then on the afterupdate event go to code
builder and put:
Me.filter = "[yourtableorquery] = """ & Me.yourcomboname & """"
Me.filteron = true

 

[ChrisW]

The problem isn't in the combo - that works fine. It's just that I
can't get the form to only show the records I want because I've got a
many:many relationship. The other controls on the welcome form work
because they are only querying the main table, not the related tables.

Try using a filter on the combo. Whatever you want the user to select, make
that the choices of the combo. Then on the afterupdate event go to code
builder and put:
Me.filter = "[yourtableorquery] = """ & Me.yourcomboname & """"
Me.filteron = true

Hi,

Apologies in advance if this should have been posted to a VBA group
instead.

I've got a relatively simple database, with all options available from
the Welcome form. There is just 1 other form. All of the controls on
the open form open this form using the DoCmd.OpenForm method, and
using the FilterName argument using queries in the database. However,
one of the controls on the welcome form is a combobox, on which one of
the queries is run (the selected option forms the WHERE clause). This
selects results from tables which have a many:many relationship, so I
then run another query selecting just the information I need from the
appropriate table (which is the table the results form is based on - I
have a subform on the results form to deal with the relationship).
When I open the results form using the combobox, it opens the results
form showing *all* the records, not just the ones relating to the
selected option.

I assume that Access doesn't do things logically, and opens the form,
then runs the query (but by that time it's obviously too late to limit
the records in the form!). How do I get round this problem?!

I hope this makes sense..!

Thanks in advance,
Chris

 

[sakamotoyukihiro]

The problem isn't in the combo - that works fine. It's just that I
can't get the form to only show the records I want because I've got a
many:many relationship. The other controls on the welcome form work
because they are only querying the main table, not the related tables.

Try using a filter on the combo. Whatever you want the user to select, make
that the choices of the combo. Then on the afterupdate event go to code
builder and put:
Me.filter = "[yourtableorquery] = """ & Me.yourcomboname & """"
Me.filteron = true

Hi,

Apologies in advance if this should have been posted to a VBA group
instead.

I've got a relatively simple database, with all options available from
the Welcome form. There is just 1 other form. All of the controls on
the open form open this form using the DoCmd.OpenForm method, and
using the FilterName argument using queries in the database. However,
one of the controls on the welcome form is a combobox, on which one of
the queries is run (the selected option forms the WHERE clause). This
selects results from tables which have a many:many relationship, so I
then run another query selecting just the information I need from the
appropriate table (which is the table the results form is based on - I
have a subform on the results form to deal with the relationship).
When I open the results form using the combobox, it opens the results
form showing *all* the records, not just the ones relating to the
selected option.

I assume that Access doesn't do things logically, and opens the form,
then runs the query (but by that time it's obviously too late to limit
the records in the form!). How do I get round this problem?!

I hope this makes sense..!

Thanks in advance,
Chris