Select item in combo box to show all records in subform

[rupertsland]

Hello.

I am building a report tracking database using Access 2003.

I have a main form that's tied to an underlying Projects table. On the main
form I have a subform that is tied to an underlying Reports table. Each
report record has a foreign key [PROJ_ID] that allows me to associate a
report with a project from the Projects table.

I used a wizard to create a combo box on the main form to allow me to select
a project and display a list reports for that project in the subform.

What I want to do is provide a way to display all records in the subform. Is
there a way to do this?

The following VisualBasic code (below) was generated by the wizard, but I do
not know what code to use to show all records. Or would it be easier to
create a command button on the main form that would overide the combo box and
show all records in the subform?

-----
Private Sub Combo62_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PROJ_ID] = " & Str(Nz(Me![Combo62]))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub
-----

Any help would welcome. Please note that I am not proficient in VisualBasic.


Dirk
Winnipeg, Canada

 

[Vincent Johns]

Hello.

I am building a report tracking database using Access 2003.

I have a main form that's tied to an underlying Projects table. On the main
form I have a subform that is tied to an underlying Reports table. Each
report record has a foreign key [PROJ_ID] that allows me to associate a
report with a project from the Projects table.

I assume that you mean "reports" instead of "a report", as otherwise you
could just put all the fields into the same record that describes the
project.

I used a wizard to create a combo box on the main form to allow me to select
a project and display a list reports for that project in the subform.

This is normal behavior for a subform.

What I want to do is provide a way to display all records in the subform. Is
there a way to do this?

That's easy. Display them in a totally separate Form, which you label
"subform".

Why would you want to do this? Subforms are typically used for
displaying records in one Table or Query that are associated with some
one record in another Table or Query, such as what you're already doing.

The following VisualBasic code (below) was generated by the wizard, but I do
not know what code to use to show all records. Or would it be easier to
create a command button on the main form that would overide the combo box and
show all records in the subform?

I think it would be much easier just to put them onto a separate Form,
sorted and filtered however you wish.

For the command button, you could use the Switchboard Manager to set up
command buttons on Switchboard Forms that you could use to display the
various Forms that you want to see.

[...]

Any help would welcome. Please note that I am not proficient in VisualBasic.

Dirk
Winnipeg, Canada

Not knowing how you intend to use the results, I can't say for sure that
you shouldn't put a command button onto the original Form (the one with
the subform included), but I get the impression that you want to do
something unrelated to the original purpose, and I think it might be
better not to clutter up the Form needlessly.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.