Query me this Batman

  • Thread starter Thread starter anduare2
  • Start date Start date
A

anduare2

District table (primary key is DistrictNumber)
DistrictNumber DistrictName
111 gotham public
222 gotham private

School table (primary key is DistrictNumber and SchoolNumber)
DistrictNumber SchoolNumber SchoolName
111 10 alfred elementary
222 10 wayne elementary
111 20 bruce high


on a form, i suppose an unbound combobox1, i want to be able to drop down a
list of all district names to choose from.

on the same form, i suppose an unbound combobox2 i want to only see the
school names for the district name i selected in combobox1 (DistrictName)

Simple if you know how to do it right???

I have done macro's and minor vb code in excel, but now am just starting to
play with access, so I am familiar with property boxes, and VB Editor. But
the examples I have been able to find are clear as mud to me. I think
because my tables are linked by DistrictNumber and I want to display and
choose data based on Names. Maybe I am making this too hard on myself.
Any help/examples will be greatly appreciated
 
anduare2 said:
District table (primary key is DistrictNumber)
DistrictNumber DistrictName
111 gotham public
222 gotham private

School table (primary key is DistrictNumber and SchoolNumber)
DistrictNumber SchoolNumber SchoolName
111 10 alfred elementary
222 10 wayne elementary
111 20 bruce high


on a form, i suppose an unbound combobox1, i want to be able to drop down
a
list of all district names to choose from.

on the same form, i suppose an unbound combobox2 i want to only see the
school names for the district name i selected in combobox1 (DistrictName)

Simple if you know how to do it right???

I have done macro's and minor vb code in excel, but now am just starting
to
play with access, so I am familiar with property boxes, and VB Editor.
But
the examples I have been able to find are clear as mud to me. I think
because my tables are linked by DistrictNumber and I want to display and
choose data based on Names. Maybe I am making this too hard on myself.
Any help/examples will be greatly appreciated
Click to expand...


cboDistrict
----------------
Row Source:
SELECT DistrictNumber, DistrictName
FROM District
ORDER BY DistrictName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

cboSchool
----------------
Row Source:
SELECT SchoolNumber, SchoolName
FROM School
WHERE DistrictNumber = [Forms]![YourFormName]![cboDistrict]
ORDER BY SchoolName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

Code for form's module:

'-------- start of code --------
Private Sub cboDistrict_AfterUpdate()

Me.cboSchool.Requery

End Sub
'-------- end of code --------
 
In addition to Dirk's well-written response

This link describes two methods of doing it:

http://www.mvps.org/access/forms/frm0028.htm

Additional References:
http://www.comeandread.com/access/tip016.htm

Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm

Or check out this from MS
http://office.microsoft.com/en-us/access/HA011730581033.aspx

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dirk said:
anduare2 said:
District table (primary key is DistrictNumber)
DistrictNumber DistrictName
111 gotham public
222 gotham private

School table (primary key is DistrictNumber and SchoolNumber)
DistrictNumber SchoolNumber SchoolName
111 10 alfred elementary
222 10 wayne elementary
111 20 bruce high


on a form, i suppose an unbound combobox1, i want to be able to drop
down a
list of all district names to choose from.

on the same form, i suppose an unbound combobox2 i want to only see the
school names for the district name i selected in combobox1 (DistrictName)

Simple if you know how to do it right???

I have done macro's and minor vb code in excel, but now am just
starting to
play with access, so I am familiar with property boxes, and VB Editor.
But
the examples I have been able to find are clear as mud to me. I think
because my tables are linked by DistrictNumber and I want to display and
choose data based on Names. Maybe I am making this too hard on myself.
Any help/examples will be greatly appreciated
Click to expand...


cboDistrict
----------------
Row Source:
SELECT DistrictNumber, DistrictName
FROM District
ORDER BY DistrictName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

cboSchool
----------------
Row Source:
SELECT SchoolNumber, SchoolName
FROM School
WHERE DistrictNumber = [Forms]![YourFormName]![cboDistrict]
ORDER BY SchoolName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

Code for form's module:

'-------- start of code --------
Private Sub cboDistrict_AfterUpdate()

Me.cboSchool.Requery

End Sub
'-------- end of code --------
Click to expand...
 
"I See" says the blind access noob.

Excellent response, in working thru your examples, I now understand what it
does AND how it works. This will be a great reference to come back to as I
continue to build my little project/database. I see I am going to have to
really expand my way of thinking with access and sql.

Many thanks

Martin




Dirk Goldgar said:
anduare2 said:
District table (primary key is DistrictNumber)
DistrictNumber DistrictName
111 gotham public
222 gotham private

School table (primary key is DistrictNumber and SchoolNumber)
DistrictNumber SchoolNumber SchoolName
111 10 alfred elementary
222 10 wayne elementary
111 20 bruce high


on a form, i suppose an unbound combobox1, i want to be able to drop down
a
list of all district names to choose from.

on the same form, i suppose an unbound combobox2 i want to only see the
school names for the district name i selected in combobox1 (DistrictName)

Simple if you know how to do it right???

I have done macro's and minor vb code in excel, but now am just starting
to
play with access, so I am familiar with property boxes, and VB Editor.
But
the examples I have been able to find are clear as mud to me. I think
because my tables are linked by DistrictNumber and I want to display and
choose data based on Names. Maybe I am making this too hard on myself.
Any help/examples will be greatly appreciated
Click to expand...


cboDistrict
----------------
Row Source:
SELECT DistrictNumber, DistrictName
FROM District
ORDER BY DistrictName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

cboSchool
----------------
Row Source:
SELECT SchoolNumber, SchoolName
FROM School
WHERE DistrictNumber = [Forms]![YourFormName]![cboDistrict]
ORDER BY SchoolName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

Code for form's module:

'-------- start of code --------
Private Sub cboDistrict_AfterUpdate()

Me.cboSchool.Requery

End Sub
'-------- end of code --------


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Thanks for the links

I had been to the first one and at the time could not quite figure out how
to use it, to get the results I needed. (not for lack of trying, i assure
you) I will re-visit it and see if I can understand it now that I have seen
a working solution.

The other links are also great, they will be a real boon as I continue my
quest from this point forward.

Thanks again for the help

Martin

John Spencer said:
In addition to Dirk's well-written response

This link describes two methods of doing it:

http://www.mvps.org/access/forms/frm0028.htm

Additional References:
http://www.comeandread.com/access/tip016.htm

Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm

Or check out this from MS
http://office.microsoft.com/en-us/access/HA011730581033.aspx

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dirk said:
anduare2 said:
District table (primary key is DistrictNumber)
DistrictNumber DistrictName
111 gotham public
222 gotham private

School table (primary key is DistrictNumber and SchoolNumber)
DistrictNumber SchoolNumber SchoolName
111 10 alfred elementary
222 10 wayne elementary
111 20 bruce high


on a form, i suppose an unbound combobox1, i want to be able to drop
down a
list of all district names to choose from.

on the same form, i suppose an unbound combobox2 i want to only see the
school names for the district name i selected in combobox1 (DistrictName)

Simple if you know how to do it right???

I have done macro's and minor vb code in excel, but now am just
starting to
play with access, so I am familiar with property boxes, and VB Editor.
But
the examples I have been able to find are clear as mud to me. I think
because my tables are linked by DistrictNumber and I want to display and
choose data based on Names. Maybe I am making this too hard on myself.
Any help/examples will be greatly appreciated
Click to expand...


cboDistrict
----------------
Row Source:
SELECT DistrictNumber, DistrictName
FROM District
ORDER BY DistrictName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

cboSchool
----------------
Row Source:
SELECT SchoolNumber, SchoolName
FROM School
WHERE DistrictNumber = [Forms]![YourFormName]![cboDistrict]
ORDER BY SchoolName
Column Count: 2
Bound Column: 1
Column Widths: 0";1.5" (or whatever is suitable for the second column)

Code for form's module:

'-------- start of code --------
Private Sub cboDistrict_AfterUpdate()

Me.cboSchool.Requery

End Sub
'-------- end of code --------
Click to expand...
Click to expand...
 
Back
Top