Selecting record in subform

[cuyeda]

I have a subform that I need to display in continuous view. The subform has
three fields which update the table below:

Link ID - Number (foreign key - links this table to the table of the main
form)
Genus ID - Number
Finding - Text
Status - Text

When a user enters the unique "Genus ID" I would like the continuous subform
to display the "genus" and "species" associated with that ID. This
information is stored in two separate tables that look like this (simplified)

Table 1
Genus ID - Autonumber (Primary Key)
Genus Name - text
Species ID - number (foreign key linked to below)

Table 2
Species ID - Autonumber (Primary Key)
Species Name - Text

Any thoughts? I'm new to Access so maybe I've gone about designing this the
wrong way. I think I could do this if I use single form view, but I want
the user to be able and see all the information at once. Thank you all in
advance. I love these discussion groups!

 

[Allen Browne]

Create a query containing both [Table 1] and [Table 2].
Output all the fields from [Table 1], and the [Species Name] field from
[Table 2].

Use this query as the RecordSource for your subform. It now shows the
species name associated with the genus.

 

[cuyeda]

Thanks a million. This works great.

Also, is it possible to use this query to give the user the option of
selecting a species using the opposite way. For example, currently the user
enters the "genus id" and the form displays the species and genus name.

But could I use this method to give the user two combo boxes where they
select the species name, then the genus name and then the "genus id" appears
into the subform table (table3)?

Create a query containing both [Table 1] and [Table 2].
Output all the fields from [Table 1], and the [Species Name] field from
[Table 2].

Use this query as the RecordSource for your subform. It now shows the
species name associated with the genus.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a subform that I need to display in continuous view. The subform
has
three fields which update the table below:

Table3
Link ID - Number (foreign key - links this table to the table of the main
form)
Genus ID - Number
Finding - Text
Status - Text

When a user enters the unique "Genus ID" I would like the continuous
subform
to display the "genus" and "species" associated with that ID. This
information is stored in two separate tables that look like this
(simplified)

Table 1
Genus ID - Autonumber (Primary Key)
Genus Name - text
Species ID - number (foreign key linked to below)

Table 2
Species ID - Autonumber (Primary Key)
Species Name - Text

Any thoughts? I'm new to Access so maybe I've gone about designing this
the
wrong way. I think I could do this if I use single form view, but I want
the user to be able and see all the information at once. Thank you all in
advance. I love these discussion groups!

 

[Allen Browne]

You would need to be careful here.

You actually need an unbound combo to select the species. In its AfterUpdate
event procedure, limit the RowSource of the Genus combo, as explained here:
http://www.mvps.org/access/forms/frm0028.htm

If the subform is in continuous view, that's not going to work very well.
The unbound Species combo will show the same on every row, and the other
rows that contain a Genus from a different species will go blank (because
the value you need to show is no longer in the RowSource.)

You can solve those problems by using a combination of text boxes (to show
the actual values) on top of the combos. In the Enter event of the text
boxes, you SetFocus to the combo, so it jumps forward on just that one line.
Not exactly newbie stuff, but there's a downloadable example of the
technique in this new article:
Enter text in calculated controls
at:
http://allenbrowne.com/TechniqueEnterCalcText.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks a million. This works great.

Also, is it possible to use this query to give the user the option of
selecting a species using the opposite way. For example, currently the
user
enters the "genus id" and the form displays the species and genus name.

But could I use this method to give the user two combo boxes where they
select the species name, then the genus name and then the "genus id"
appears
into the subform table (table3)?

Create a query containing both [Table 1] and [Table 2].
Output all the fields from [Table 1], and the [Species Name] field from
[Table 2].

Use this query as the RecordSource for your subform. It now shows the
species name associated with the genus.

I have a subform that I need to display in continuous view. The subform
has
three fields which update the table below:

Table3
Link ID - Number (foreign key - links this table to the table of the
main
form)
Genus ID - Number
Finding - Text
Status - Text

When a user enters the unique "Genus ID" I would like the continuous
subform
to display the "genus" and "species" associated with that ID. This
information is stored in two separate tables that look like this
(simplified)

Table 1
Genus ID - Autonumber (Primary Key)
Genus Name - text
Species ID - number (foreign key linked to below)

Table 2
Species ID - Autonumber (Primary Key)
Species Name - Text

Any thoughts? I'm new to Access so maybe I've gone about designing
this
the
wrong way. I think I could do this if I use single form view, but I
want
the user to be able and see all the information at once. Thank you all
in
advance. I love these discussion groups!