combo question

G

Guest

I have 2 combo boxes and 5 text boxes. I want the second combo box only to
display the items that are linked to my selection in combo box 1. Then when I
select the appropriate text from combo 2, I want the 5 text boxes to display
the text that is linked to my cvombo 2 choice in my 5 text boxes. here is my
code so far which does step 2 by populating the 5 text boxes when i make a
selection in combo 2. My problem being i can't get combo 2 results to only
reflect my choice from combo 1. all choices are still displayed in combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the rowsource
in combo 2. Any help would be appreciated.
 
K

Ken Snell [MVP]

As I noted in an earlier reply to your post (
), this article tells you how to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source SQL
statement for the second combo box, where that WHERE clause uses the value
of the first combo box as the "criterion" value. And that you use the
AfterUpdate event of the first combo box to requery the second combo box.

The row source you're using has no filtering criterion, so of course it will
show all records.

And this article tells you how to show the other columns of a combo box in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values from the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries is fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

rob said:
I have 2 combo boxes and 5 text boxes. I want the second combo box only to
display the items that are linked to my selection in combo box 1. Then
when I
select the appropriate text from combo 2, I want the 5 text boxes to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes. here is
my
code so far which does step 2 by populating the 5 text boxes when i make a
selection in combo 2. My problem being i can't get combo 2 results to only
reflect my choice from combo 1. all choices are still displayed in combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the rowsource
in combo 2. Any help would be appreciated.[/QUOTE]
Click to expand...
 
G

Guest

Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE ((([School.nschid])=[me]![cmb_district].[district_id]));[/QUOTE][/QUOTE][/QUOTE]

I also added an afterupdate event to the first combo box to requery the
second combo box. It still doesn't seem to work.

[QUOTE="Ken Snell"]
As I noted in an earlier reply to your post (
), this article tells you how to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source SQL
statement for the second combo box, where that WHERE clause uses the value
of the first combo box as the "criterion" value. And that you use the
AfterUpdate event of the first combo box to requery the second combo box.

The row source you're using has no filtering criterion, so of course it will
show all records.

And this article tells you how to show the other columns of a combo box in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values from the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries is fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

[QUOTE="rob"]
I have 2 combo boxes and 5 text boxes. I want the second combo box only to
display the items that are linked to my selection in combo box 1. Then
when I
select the appropriate text from combo 2, I want the 5 text boxes to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes. here is
my
code so far which does step 2 by populating the 5 text boxes when i make a
selection in combo 2. My problem being i can't get combo 2 results to only
reflect my choice from combo 1. all choices are still displayed in combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the rowsource
in combo 2. Any help would be appreciated.[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

You use SCHOOLS as the table name in the rest of the SQL statement, but you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

rob said:
Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));[/QUOTE][/QUOTE]

I also added an afterupdate event to the first combo box to requery the
second combo box. It still doesn't seem to work.

[QUOTE="Ken Snell"]
As I noted in an earlier reply to your post (
), this article tells you how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source SQL
statement for the second combo box, where that WHERE clause uses the
value
of the first combo box as the "criterion" value. And that you use the
AfterUpdate event of the first combo box to requery the second combo box.

The row source you're using has no filtering criterion, so of course it
will
show all records.

And this article tells you how to show the other columns of a combo box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries is
fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

[QUOTE="rob"]
I have 2 combo boxes and 5 text boxes. I want the second combo box only
to
display the items that are linked to my selection in combo box 1. Then
when I
select the appropriate text from combo 2, I want the 5 text boxes to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes. here
is
my
code so far which does step 2 by populating the 5 text boxes when i
make a
selection in combo 2. My problem being i can't get combo 2 results to
only
reflect my choice from combo 1. all choices are still displayed in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the
rowsource
in combo 2. Any help would be appreciated.[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That was a typo. I have schools in there. All I get is after i select a
district from combo 1 and then hit the dropdown in combo 2 i get prompted for
a parameter value. When i enter the school id it appears in the combo box.
but i only want the schools pertaining to district 2 to appear and i don't
want parameter values to appear. I have a lot to learn in Access. I feel bad
asking these questions but I guess That's the only way I going to learn
things.

Ken Snell said:
You use SCHOOLS as the table name in the rest of the SQL statement, but you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

rob said:
Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));[/QUOTE]

I also added an afterupdate event to the first combo box to requery the
second combo box. It still doesn't seem to work.

[QUOTE="Ken Snell"]
As I noted in an earlier reply to your post (
), this article tells you how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source SQL
statement for the second combo box, where that WHERE clause uses the
value
of the first combo box as the "criterion" value. And that you use the
AfterUpdate event of the first combo box to requery the second combo box.

The row source you're using has no filtering criterion, so of course it
will
show all records.

And this article tells you how to show the other columns of a combo box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries is
fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

I have 2 combo boxes and 5 text boxes. I want the second combo box only
to
display the items that are linked to my selection in combo box 1. Then
when I
select the appropriate text from combo 2, I want the 5 text boxes to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes. here
is
my
code so far which does step 2 by populating the 5 text boxes when i
make a
selection in combo 2. My problem being i can't get combo 2 results to
only
reflect my choice from combo 1. all choices are still displayed in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the
rowsource
in combo 2. Any help would be appreciated.
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Ken, Is there anywhere I could email you my sample so you couls see my
problem? If not thanks anyway. I appreciate the help.

Here is a brief description of the whole thing:

Table 1= Districts- Ndistid- Primary Key, District
Table 2 = Schools - Nschid-Primary Key, ndistid, school,
principal,email,phone,fax

Combo 1 just adds all records from district throughthe wizard.
combo 2 - row source = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));[/QUOTE][/QUOTE][/QUOTE]

combo 1 after update event - cmb_school. Requery

combo 2 after update event - Private Sub cmb_school_BeforeUpdate(Cancel As
Integer)[QUOTE][QUOTE][QUOTE]
If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)[/QUOTE][/QUOTE][/QUOTE]

end if

[QUOTE="rob"]
That was a typo. I have schools in there. All I get is after i select a
district from combo 1 and then hit the dropdown in combo 2 i get prompted for
a parameter value. When i enter the school id it appears in the combo box.
but i only want the schools pertaining to district 2 to appear and i don't
want parameter values to appear. I have a lot to learn in Access. I feel bad
asking these questions but I guess That's the only way I going to learn
things.

[QUOTE="Ken Snell"]
You use SCHOOLS as the table name in the rest of the SQL statement, but you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

[QUOTE="rob"]
Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));

I also added an afterupdate event to the first combo box to requery the
second combo box. It still doesn't seem to work.

:

As I noted in an earlier reply to your post (
), this article tells you how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source SQL
statement for the second combo box, where that WHERE clause uses the
value
of the first combo box as the "criterion" value. And that you use the
AfterUpdate event of the first combo box to requery the second combo box.

The row source you're using has no filtering criterion, so of course it
will
show all records.

And this article tells you how to show the other columns of a combo box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries is
fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

I have 2 combo boxes and 5 text boxes. I want the second combo box only
to
display the items that are linked to my selection in combo box 1. Then
when I
select the appropriate text from combo 2, I want the 5 text boxes to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes. here
is
my
code so far which does step 2 by populating the 5 text boxes when i
make a
selection in combo 2. My problem being i can't get combo 2 results to
only
reflect my choice from combo 1. all choices are still displayed in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the
rowsource
in combo 2. Any help would be appreciated.
[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

OK - let's dissect what you're trying to use as the parameter here.

This is what you typed:
[me]![cmb_district].[district_id]

"me" has no meaning in an SQL statement in a row source; it's valid only
within a module that is behind a form or report. I assume that you want to
refer to the combo box that is on the same form as this combo box, right? If
I'm reading your first post correctly, the name of that first combo box is
"cmb_district", so change the SQL statement to this:

Select distinct [SCHOOLS].[nschid], [SCHOOLS].[SCHOOL],
[SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX]
FROM SCHOOLS WHERE
((([Schools.nschid])=[cmb_district]));



--

Ken Snell
<MS ACCESS MVP>



[QUOTE="rob"]
That was a typo. I have schools in there. All I get is after i select a
district from combo 1 and then hit the dropdown in combo 2 i get prompted
for
a parameter value. When i enter the school id it appears in the combo box.
but i only want the schools pertaining to district 2 to appear and i don't
want parameter values to appear. I have a lot to learn in Access. I feel
bad
asking these questions but I guess That's the only way I going to learn
things.

[QUOTE="Ken Snell"]
You use SCHOOLS as the table name in the rest of the SQL statement, but
you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

[QUOTE="rob"]
Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));

I also added an afterupdate event to the first combo box to requery the
second combo box. It still doesn't seem to work.

:

As I noted in an earlier reply to your post (
), this article tells you
how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source SQL
statement for the second combo box, where that WHERE clause uses the
value
of the first combo box as the "criterion" value. And that you use the
AfterUpdate event of the first combo box to requery the second combo
box.

The row source you're using has no filtering criterion, so of course
it
will
show all records.

And this article tells you how to show the other columns of a combo
box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values
from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries is
fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

I have 2 combo boxes and 5 text boxes. I want the second combo box
only
to
display the items that are linked to my selection in combo box 1.
Then
when I
select the appropriate text from combo 2, I want the 5 text boxes to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes.
here
is
my
code so far which does step 2 by populating the 5 text boxes when i
make a
selection in combo 2. My problem being i can't get combo 2 results
to
only
reflect my choice from combo 1. all choices are still displayed in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the
rowsource
in combo 2. Any help would be appreciated.
[/QUOTE]
[/QUOTE][/QUOTE]
 
G

Guest

It still prompts me to enter a parameter value which I don't want to do. When
I select a district I just want the schools from that district to be
available in the dropdown for a selection choice. Right now I type in a 3
digit school_id and i get that school alone in my dropdown. I want all from
that district so I can choose the school I want. Thanks again.

Ken Snell said:
OK - let's dissect what you're trying to use as the parameter here.

This is what you typed:
[me]![cmb_district].[district_id]

"me" has no meaning in an SQL statement in a row source; it's valid only
within a module that is behind a form or report. I assume that you want to
refer to the combo box that is on the same form as this combo box, right? If
I'm reading your first post correctly, the name of that first combo box is
"cmb_district", so change the SQL statement to this:

Select distinct [SCHOOLS].[nschid], [SCHOOLS].[SCHOOL],
[SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX]
FROM SCHOOLS WHERE
((([Schools.nschid])=[cmb_district]));



--

Ken Snell
<MS ACCESS MVP>



[QUOTE="rob"]
That was a typo. I have schools in there. All I get is after i select a
district from combo 1 and then hit the dropdown in combo 2 i get prompted
for
a parameter value. When i enter the school id it appears in the combo box.
but i only want the schools pertaining to district 2 to appear and i don't
want parameter values to appear. I have a lot to learn in Access. I feel
bad
asking these questions but I guess That's the only way I going to learn
things.

[QUOTE="Ken Snell"]
You use SCHOOLS as the table name in the rest of the SQL statement, but
you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));

I also added an afterupdate event to the first combo box to requery the
second combo box. It still doesn't seem to work.

:

As I noted in an earlier reply to your post (
), this article tells you
how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source SQL
statement for the second combo box, where that WHERE clause uses the
value
of the first combo box as the "criterion" value. And that you use the
AfterUpdate event of the first combo box to requery the second combo
box.

The row source you're using has no filtering criterion, so of course
it
will
show all records.

And this article tells you how to show the other columns of a combo
box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values
from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries is
fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

I have 2 combo boxes and 5 text boxes. I want the second combo box
only
to
display the items that are linked to my selection in combo box 1.
Then
when I
select the appropriate text from combo 2, I want the 5 text boxes to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes.
here
is
my
code so far which does step 2 by populating the 5 text boxes when i
make a
selection in combo 2. My problem being i can't get combo 2 results
to
only
reflect my choice from combo 1. all choices are still displayed in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the
rowsource
in combo 2. Any help would be appreciated.
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
 
K

Ken Snell [MVP]

What parameter does it ask you for? Please provide details so that I can
help you figure out what is happening.

Tell me the names of the combo boxes on the form too. Which one is "combo 1"
and which is "combo 2"?

--

Ken Snell
<MS ACCESS MVP>



rob said:
It still prompts me to enter a parameter value which I don't want to do.
When
I select a district I just want the schools from that district to be
available in the dropdown for a selection choice. Right now I type in a 3
digit school_id and i get that school alone in my dropdown. I want all
from
that district so I can choose the school I want. Thanks again.

Ken Snell said:
OK - let's dissect what you're trying to use as the parameter here.

This is what you typed:
[me]![cmb_district].[district_id]

"me" has no meaning in an SQL statement in a row source; it's valid only
within a module that is behind a form or report. I assume that you want
to
refer to the combo box that is on the same form as this combo box, right?
If
I'm reading your first post correctly, the name of that first combo box
is
"cmb_district", so change the SQL statement to this:

Select distinct [SCHOOLS].[nschid], [SCHOOLS].[SCHOOL],
[SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX]
FROM SCHOOLS WHERE
((([Schools.nschid])=[cmb_district]));



--

Ken Snell
<MS ACCESS MVP>



[QUOTE="rob"]
That was a typo. I have schools in there. All I get is after i select a
district from combo 1 and then hit the dropdown in combo 2 i get
prompted
for
a parameter value. When i enter the school id it appears in the combo
box.
but i only want the schools pertaining to district 2 to appear and i
don't
want parameter values to appear. I have a lot to learn in Access. I
feel
bad
asking these questions but I guess That's the only way I going to learn
things.

:

You use SCHOOLS as the table name in the rest of the SQL statement,
but
you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));

I also added an afterupdate event to the first combo box to requery
the
second combo box. It still doesn't seem to work.

:

As I noted in an earlier reply to your post (
), this article tells
you
how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source
SQL
statement for the second combo box, where that WHERE clause uses
the
value
of the first combo box as the "criterion" value. And that you use
the
AfterUpdate event of the first combo box to requery the second
combo
box.

The row source you're using has no filtering criterion, so of
course
it
will
show all records.

And this article tells you how to show the other columns of a combo
box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values
from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries
is
fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

I have 2 combo boxes and 5 text boxes. I want the second combo box
only
to
display the items that are linked to my selection in combo box 1.
Then
when I
select the appropriate text from combo 2, I want the 5 text boxes
to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes.
here
is
my
code so far which does step 2 by populating the 5 text boxes when
i
make a
selection in combo 2. My problem being i can't get combo 2
results
to
only
reflect my choice from combo 1. all choices are still displayed
in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the
rowsource
in combo 2. Any help would be appreciated.
[/QUOTE]
[/QUOTE][/QUOTE]
Click to expand...
Click to expand...
 
G

Guest

Thanks Ken For your Help. It's working fine now.

Ken Snell said:
What parameter does it ask you for? Please provide details so that I can
help you figure out what is happening.

Tell me the names of the combo boxes on the form too. Which one is "combo 1"
and which is "combo 2"?

--

Ken Snell
<MS ACCESS MVP>



rob said:
It still prompts me to enter a parameter value which I don't want to do.
When
I select a district I just want the schools from that district to be
available in the dropdown for a selection choice. Right now I type in a 3
digit school_id and i get that school alone in my dropdown. I want all
from
that district so I can choose the school I want. Thanks again.

Ken Snell said:
OK - let's dissect what you're trying to use as the parameter here.

This is what you typed:
[me]![cmb_district].[district_id]

"me" has no meaning in an SQL statement in a row source; it's valid only
within a module that is behind a form or report. I assume that you want
to
refer to the combo box that is on the same form as this combo box, right?
If
I'm reading your first post correctly, the name of that first combo box
is
"cmb_district", so change the SQL statement to this:

Select distinct [SCHOOLS].[nschid], [SCHOOLS].[SCHOOL],
[SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX]
FROM SCHOOLS WHERE
((([Schools.nschid])=[cmb_district]));



--

Ken Snell
<MS ACCESS MVP>



That was a typo. I have schools in there. All I get is after i select a
district from combo 1 and then hit the dropdown in combo 2 i get
prompted
for
a parameter value. When i enter the school id it appears in the combo
box.
but i only want the schools pertaining to district 2 to appear and i
don't
want parameter values to appear. I have a lot to learn in Access. I
feel
bad
asking these questions but I guess That's the only way I going to learn
things.

:

You use SCHOOLS as the table name in the rest of the SQL statement,
but
you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));

I also added an afterupdate event to the first combo box to requery
the
second combo box. It still doesn't seem to work.

:

As I noted in an earlier reply to your post (
), this article tells
you
how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row Source
SQL
statement for the second combo box, where that WHERE clause uses
the
value
of the first combo box as the "criterion" value. And that you use
the
AfterUpdate event of the first combo box to requery the second
combo
box.

The row source you're using has no filtering criterion, so of
course
it
will
show all records.

And this article tells you how to show the other columns of a combo
box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the values
from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the entries
is
fine
for the first two tests, except that your code doesn't cancel the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

I have 2 combo boxes and 5 text boxes. I want the second combo box
only
to
display the items that are linked to my selection in combo box 1.
Then
when I
select the appropriate text from combo 2, I want the 5 text boxes
to
display
the text that is linked to my cvombo 2 choice in my 5 text boxes.
here
is
my
code so far which does step 2 by populating the 5 text boxes when
i
make a
selection in combo 2. My problem being i can't get combo 2
results
to
only
reflect my choice from combo 1. all choices are still displayed
in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use the
rowsource
in combo 2. Any help would be appreciated.
[/QUOTE][/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

What changed to make it work now?

--

Ken Snell
<MS ACCESS MVP>

rob said:
Thanks Ken For your Help. It's working fine now.

Ken Snell said:
What parameter does it ask you for? Please provide details so that I can
help you figure out what is happening.

Tell me the names of the combo boxes on the form too. Which one is "combo
1"
and which is "combo 2"?

--

Ken Snell
<MS ACCESS MVP>



rob said:
It still prompts me to enter a parameter value which I don't want to
do.
When
I select a district I just want the schools from that district to be
available in the dropdown for a selection choice. Right now I type in a
3
digit school_id and i get that school alone in my dropdown. I want all
from
that district so I can choose the school I want. Thanks again.

:

OK - let's dissect what you're trying to use as the parameter here.

This is what you typed:
[me]![cmb_district].[district_id]

"me" has no meaning in an SQL statement in a row source; it's valid
only
within a module that is behind a form or report. I assume that you
want
to
refer to the combo box that is on the same form as this combo box,
right?
If
I'm reading your first post correctly, the name of that first combo
box
is
"cmb_district", so change the SQL statement to this:

Select distinct [SCHOOLS].[nschid], [SCHOOLS].[SCHOOL],
[SCHOOLS].[PRINCIPAL], [SCHOOLS].,
[SCHOOLS].[PHONE], [SCHOOLS].[FAX]
FROM SCHOOLS WHERE
((([Schools.nschid])=[cmb_district]));



--

Ken Snell
<MS ACCESS MVP>



That was a typo. I have schools in there. All I get is after i
select a
district from combo 1 and then hit the dropdown in combo 2 i get
prompted
for
a parameter value. When i enter the school id it appears in the
combo
box.
but i only want the schools pertaining to district 2 to appear and i
don't
want parameter values to appear. I have a lot to learn in Access. I
feel
bad
asking these questions but I guess That's the only way I going to
learn
things.

:

You use SCHOOLS as the table name in the rest of the SQL statement,
but
you
used School (no s) in the WHERE clause.

--

Ken Snell
<MS ACCESS MVP>

Hi.I've just added the WHERE clause to my rowsource as follows:

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL],
[SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE
((([School.nschid])=[me]![cmb_district].[district_id]));

I also added an afterupdate event to the first combo box to
requery
the
second combo box. It still doesn't seem to work.

:

As I noted in an earlier reply to your post (
), this article tells
you
how
to
tie the two combo boxes together:
http://www.mvps.org/access/forms/frm0028.htm

The "trick" is that you must have a WHERE clause in the Row
Source
SQL
statement for the second combo box, where that WHERE clause uses
the
value
of the first combo box as the "criterion" value. And that you
use
the
AfterUpdate event of the first combo box to requery the second
combo
box.

The row source you're using has no filtering criterion, so of
course
it
will
show all records.

And this article tells you how to show the other columns of a
combo
box
in
textboxes:
http://www.mvps.org/access/forms/frm0058.htm

You use the AfterUpdate event of the combo box to write the
values
from
the
other columns into the textboxes, not the BeforeUpdate event.

Your code for the BeforeUpdate event that is validating the
entries
is
fine
for the first two tests, except that your code doesn't cancel
the
BeforeUpdate event when the validation test fails.

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal Code"
Cancel = True
End If


--

Ken Snell
<MS ACCESS MVP>

I have 2 combo boxes and 5 text boxes. I want the second combo
box
only
to
display the items that are linked to my selection in combo box
1.
Then
when I
select the appropriate text from combo 2, I want the 5 text
boxes
to
display
the text that is linked to my cvombo 2 choice in my 5 text
boxes.
here
is
my
code so far which does step 2 by populating the 5 text boxes
when
i
make a
selection in combo 2. My problem being i can't get combo 2
results
to
only
reflect my choice from combo 1. all choices are still
displayed
in
combo 2
for all criteria. Here is my code so far.

Row source for combo 2 = Select distinct [SCHOOLS].[nschid],
[SCHOOLS].[SCHOOL], [SCHOOLS].[PRINCIPAL], [SCHOOLS].[EMAIL],
[SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS;

Column count =6
Column widths = 0.5299";0.35";0.2799";0.1;0.1;0.1"


Private Sub cmb_school_BeforeUpdate(Cancel As Integer)

If cmb_SCHOOL = "*" Then
MsgBox "Select a postal code.", vbExclamation, "Postal
Code"
ElseIf cmb_postcode = "" Then
MsgBox "Select a postal code.", vbExclamation, "Postal
Code"
Else
txt_nschid = Forms![School]![cmb_school].Column(0)
txt_SCHOOL = Forms![School]![cmb_school].Column(1)
txt_PRINCIPAL = Forms![School]![cmb_school].Column(2)
txt_EMAIL = Forms![School]![cmb_school].Column(3)
txt_PHONE = Forms![School]![cmb_school].Column(4)
txt_FAX = Forms![School]![cmb_school].Column(5)

End If

End Sub

Do i have to use SQL in my first combo box since I can't use
the
rowsource
in combo 2. Any help would be appreciated.
[/QUOTE]
[/QUOTE][/QUOTE]
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

Filling Text Boxes from a Combo Box 2
Combo box columns 1
combo question 1
code will and won't work 1
Need Help with Combo Boxes 2
Change forms' RecordSource by Combo box selection 4
flexibility in searching by adding “search by†combo box 1
Autofilling a form based on combo box selection 8

Top