If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
combo question
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
#4
|
|||
|
|||
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" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
#5
|
|||
|
|||
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 [MVP]" wrote: 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" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
#6
|
|||
|
|||
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].[email], [SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE ((([School.nschid])=[me]![cmb_district].[district_id])); combo 1 after update event - cmb_school. Requery combo 2 after update event - Private Sub cmb_school_BeforeUpdate(Cancel As Integer) 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) end if "rob" wrote: 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 [MVP]" wrote: 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" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
#7
|
|||
|
|||
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].[email], [SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE ((([Schools.nschid])=[cmb_district])); -- Ken Snell MS ACCESS MVP "rob" wrote in message ... 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 [MVP]" wrote: 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" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
#8
|
|||
|
|||
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 [MVP]" wrote: 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].[email], [SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE ((([Schools.nschid])=[cmb_district])); -- Ken Snell MS ACCESS MVP "rob" wrote in message ... 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 [MVP]" wrote: 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" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
#9
|
|||
|
|||
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" wrote in message ... 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 [MVP]" wrote: 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].[email], [SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE ((([Schools.nschid])=[cmb_district])); -- Ken Snell MS ACCESS MVP "rob" wrote in message ... 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 [MVP]" wrote: 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" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
#10
|
|||
|
|||
Thanks Ken For your Help. It's working fine now.
"Ken Snell [MVP]" wrote: 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" wrote in message ... 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 [MVP]" wrote: 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].[email], [SCHOOLS].[PHONE], [SCHOOLS].[FAX] FROM SCHOOLS WHERE ((([Schools.nschid])=[cmb_district])); -- Ken Snell MS ACCESS MVP "rob" wrote in message ... 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 [MVP]" wrote: 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" wrote in message ... 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. "Ken Snell [MVP]" wrote: 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" wrote in message ... 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. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Combo Box Report Question (Please Help) | Demoman | Using Forms | 0 | March 15th, 2005 08:59 PM |
combo question | Rob | New Users | 1 | March 15th, 2005 06:04 PM |
Question: combo box on switchboard | SG | Using Forms | 3 | March 15th, 2005 02:31 PM |
cascading combo boxes on a subform question | Alex Anderson | Using Forms | 17 | December 18th, 2004 01:36 AM |
Requery Combobox | MJ | Running & Setting Up Queries | 7 | May 25th, 2004 11:01 AM |