field names in SQL

G

Guest

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
 
G

Guest

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
 
G

Guest

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

Sharkbyte said:
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

Andy said:
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
Click to expand...
Click to expand...
 
G

Guest

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

Sharkbyte said:
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

Andy said:
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
Click to expand...
Click to expand...
 
G

Guest

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


Andy said:
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

Sharkbyte said:
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

Andy said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot table question: Sorting the subgroup in a pivot table (whenusing the top 10 option for the sub 0
Select query count function help 1
Names of individual controls in option group 4
Need help with DefaultView.RowFilter 2
linking a cell to multiple cells in another table 1
Running Sum in a Query 3
multiple related comboboxes in datasheet view 4
Table Foreign Key. Access 2003, Windows XP. 7

Top