field names in SQL

[Andy]

Hello

I have a lookup table joining two tables together, Group and SubGroup. Each
Group has a set of corresponding SubGroups. e.g. Group: primary key = grp_id,
SubGroup: primary key = subgrp_ID, foreign key = grp_id.

in the lookup table, i want to be able to select a group from a combo box
listing (using lookup) so when a Group is selected, the corresponding lookup
list in SubGroup in the next field over changes to reflect the chosen Group.
In the SQL statement, how do you reference the field in the same row so that
I can do a select all records in SubGroup matching grp_id where grp_id = # in
the grp_id field of the same record?

THANKS!
Andrew

 

[Sharkbyte]

Andy:

When building your form, use unbound fields. Set the table/query value, for
the first, to be the group listing. Set it to 2 columns (include the ID as
well as name), and bind the ID column.

Then, when establishing the second field, use the bound column - from the
first combo box - in your WHERE statement.

Sample:

Combo1 -
Row Source - "SELECT grp_id, grp_name FROM tblgroup"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display group name)

Combo2 - (You may need to create a query, and reference the query for Row
Source)
Row Source - "SELECT subgrp_ID, subgrp_name FROM tblSubGroups GROUP BY
grp_id HAVING (((tblSubGroup.[grp_id])=[forms]![frmGroups]![Combo1]));"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display the SubGroup.

Hope this helps.

Sharkbyte

 

[Sharkbyte]

As a follow-up...

You may also wish to add

combo2 = ""
Combo2.requery

to the AfterUpdate property of Combo1. This will reset the Combo2 field if
the user goes back and reselects a group from Combo1.

Sharkbyte

Andy:

When building your form, use unbound fields. Set the table/query value, for
the first, to be the group listing. Set it to 2 columns (include the ID as
well as name), and bind the ID column.

Then, when establishing the second field, use the bound column - from the
first combo box - in your WHERE statement.

Sample:

Combo1 -
Row Source - "SELECT grp_id, grp_name FROM tblgroup"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display group name)

Combo2 - (You may need to create a query, and reference the query for Row
Source)
Row Source - "SELECT subgrp_ID, subgrp_name FROM tblSubGroups GROUP BY
grp_id HAVING (((tblSubGroup.[grp_id])=[forms]![frmGroups]![Combo1]));"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display the SubGroup.

Hope this helps.

Sharkbyte

Hello

I have a lookup table joining two tables together, Group and SubGroup. Each
Group has a set of corresponding SubGroups. e.g. Group: primary key = grp_id,
SubGroup: primary key = subgrp_ID, foreign key = grp_id.

in the lookup table, i want to be able to select a group from a combo box
listing (using lookup) so when a Group is selected, the corresponding lookup
list in SubGroup in the next field over changes to reflect the chosen Group.
In the SQL statement, how do you reference the field in the same row so that
I can do a select all records in SubGroup matching grp_id where grp_id = # in
the grp_id field of the same record?

THANKS!
Andrew

 

[Andy]

Hey Sharkbyte

thanks a lot for your reply

could you please breakdown what each component means in:
[forms]![frmGroups]![Combo1]

also, how do you name a display control "combo1"?

thanks
andrew

Andy:

When building your form, use unbound fields. Set the table/query value, for
the first, to be the group listing. Set it to 2 columns (include the ID as
well as name), and bind the ID column.

Then, when establishing the second field, use the bound column - from the
first combo box - in your WHERE statement.

Sample:

Combo1 -
Row Source - "SELECT grp_id, grp_name FROM tblgroup"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display group name)

Combo2 - (You may need to create a query, and reference the query for Row
Source)
Row Source - "SELECT subgrp_ID, subgrp_name FROM tblSubGroups GROUP BY
grp_id HAVING (((tblSubGroup.[grp_id])=[forms]![frmGroups]![Combo1]));"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display the SubGroup.

Hope this helps.

Sharkbyte

Hello

I have a lookup table joining two tables together, Group and SubGroup. Each
Group has a set of corresponding SubGroups. e.g. Group: primary key = grp_id,
SubGroup: primary key = subgrp_ID, foreign key = grp_id.

in the lookup table, i want to be able to select a group from a combo box
listing (using lookup) so when a Group is selected, the corresponding lookup
list in SubGroup in the next field over changes to reflect the chosen Group.
In the SQL statement, how do you reference the field in the same row so that
I can do a select all records in SubGroup matching grp_id where grp_id = # in
the grp_id field of the same record?

THANKS!
Andrew

 

[Sharkbyte]

Andy:

[forms]![frmGroups]![Combo1] breaks down like this:

forms - says you are calling/referencing an object within a form.
frmGroups - would be the form name where the object exists.
Combo1 - would be the object name.

You change an object's name in Properties > Other > Name.

Sharkbyte

Hey Sharkbyte

thanks a lot for your reply

could you please breakdown what each component means in:
[forms]![frmGroups]![Combo1]

also, how do you name a display control "combo1"?

thanks
andrew

Andy:

When building your form, use unbound fields. Set the table/query value, for
the first, to be the group listing. Set it to 2 columns (include the ID as
well as name), and bind the ID column.

Then, when establishing the second field, use the bound column - from the
first combo box - in your WHERE statement.

Sample:

Combo1 -
Row Source - "SELECT grp_id, grp_name FROM tblgroup"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display group name)

Combo2 - (You may need to create a query, and reference the query for Row
Source)
Row Source - "SELECT subgrp_ID, subgrp_name FROM tblSubGroups GROUP BY
grp_id HAVING (((tblSubGroup.[grp_id])=[forms]![frmGroups]![Combo1]));"
Bound Column - 1
Column Count - 2
Column Widths - 0, 1" (Whatever size is necessary to display the SubGroup.

Hope this helps.

Sharkbyte

Hello

I have a lookup table joining two tables together, Group and SubGroup. Each
Group has a set of corresponding SubGroups. e.g. Group: primary key = grp_id,
SubGroup: primary key = subgrp_ID, foreign key = grp_id.

in the lookup table, i want to be able to select a group from a combo box
listing (using lookup) so when a Group is selected, the corresponding lookup
list in SubGroup in the next field over changes to reflect the chosen Group.
In the SQL statement, how do you reference the field in the same row so that
I can do a select all records in SubGroup matching grp_id where grp_id = # in
the grp_id field of the same record?

THANKS!
Andrew