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
|
|||
|
|||
Cascading combo box between two forms
I have been getting some information about cascading cbo's
from he http://www.blueclaw-db.com/filter_combo_box.htm This example works fine where both cbo's are on the same form. However, what I would like to do is have the primary cbo on a main form and the secondary (cascaded result) cbo on a subform. I edited the commands to reflect this using the example given and although Access appears to accept the SQL statement no entries appear in the cbo. Should this, in theory work? Or should there be extra code to cascade between two sheets? |
#2
|
|||
|
|||
Aaron,
Judging by the name you have used in referring to the combo box, it appears that the combo box is on the subform. If so, the syntax does NOT include the name of the subform. The subform is actually held in a control on the main form called a subform control. You need to refer to this control instead. It may or may not have the same name as the subform, depending on whether you changed it or on how you added the subform to the main form. Also, since you are referring to a combo box, how many columns are in the combo box that you are referring to? If there is more than one, be aware that you are referring to the Value property and that will come from the Bound column, which is not necessarily the column that is displayed in the textbox portion of the combo box. If that is the problem, you can either change which field you are filtering on or use the Column property of the combo box to get the value from the desired column. To get the name of the subform control, open the main form in design mode and open the Properties sheet. Click on the subform ONE time. The properties sheet should show the name of the subform control. If you click more than once, you'll be in the subform and the properties sheet will show the name of the subform or its controls. Once you have the name of the subform control, the syntax would be: [Forms]![NameOfMainForm]![NameOfSubformControl].[Form]![cboCltName] I tried referring to a particular column in the SQL, but I don't know if I wasn't getting the bracketing correct or is SQL just doesn't like specifying a property of the combo box. But I couldn't get it to work. So, if the Value of the combo box isn't the Client Name, you'll need to filter on the field correlates to the bound column. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message ... Yes that would have helped wouldn't it :-) SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltName)=[Forms]![frmSubStatic]! [cboCltName])); Hope this makes sense. |
#3
|
|||
|
|||
You're right, I was referencing the second column of
cboCltName. However when I altered the query to reflect this (qryWorkerLookup) it still didn't work. Here is the SQL so far: SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltNumber)=[Forms]![frmSubStatic]! [frmSubDynamic].[Form]![cboCltName])); Both cboCltName and cboTempName are referencing the second column (to save a numerical value in the underlying table), but all fields are available to the query. Is there any further info I can post, because so far it's still blanking me... -----Original Message----- Aaron, Judging by the name you have used in referring to the combo box, it appears that the combo box is on the subform. If so, the syntax does NOT include the name of the subform. The subform is actually held in a control on the main form called a subform control. You need to refer to this control instead. It may or may not have the same name as the subform, depending on whether you changed it or on how you added the subform to the main form. Also, since you are referring to a combo box, how many columns are in the combo box that you are referring to? If there is more than one, be aware that you are referring to the Value property and that will come from the Bound column, which is not necessarily the column that is displayed in the textbox portion of the combo box. If that is the problem, you can either change which field you are filtering on or use the Column property of the combo box to get the value from the desired column. To get the name of the subform control, open the main form in design mode and open the Properties sheet. Click on the subform ONE time. The properties sheet should show the name of the subform control. If you click more than once, you'll be in the subform and the properties sheet will show the name of the subform or its controls. Once you have the name of the subform control, the syntax would be: [Forms]![NameOfMainForm]![NameOfSubformControl].[Form]! [cboCltName] I tried referring to a particular column in the SQL, but I don't know if I wasn't getting the bracketing correct or is SQL just doesn't like specifying a property of the combo box. But I couldn't get it to work. So, if the Value of the combo box isn't the Client Name, you'll need to filter on the field correlates to the bound column. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message ... Yes that would have helped wouldn't it :-) SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltName)=[Forms]! [frmSubStatic]! [cboCltName])); Hope this makes sense. . |
#4
|
|||
|
|||
Forget that, I have it working now with some tweaking.
Problem is now I can't seem to figure out the Requery command to make it refresh the list - it's only showing an abbreviated list from one client number... -----Original Message----- You're right, I was referencing the second column of cboCltName. However when I altered the query to reflect this (qryWorkerLookup) it still didn't work. Here is the SQL so far: SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltNumber)=[Forms]! [frmSubStatic]! [frmSubDynamic].[Form]![cboCltName])); Both cboCltName and cboTempName are referencing the second column (to save a numerical value in the underlying table), but all fields are available to the query. Is there any further info I can post, because so far it's still blanking me... -----Original Message----- Aaron, Judging by the name you have used in referring to the combo box, it appears that the combo box is on the subform. If so, the syntax does NOT include the name of the subform. The subform is actually held in a control on the main form called a subform control. You need to refer to this control instead. It may or may not have the same name as the subform, depending on whether you changed it or on how you added the subform to the main form. Also, since you are referring to a combo box, how many columns are in the combo box that you are referring to? If there is more than one, be aware that you are referring to the Value property and that will come from the Bound column, which is not necessarily the column that is displayed in the textbox portion of the combo box. If that is the problem, you can either change which field you are filtering on or use the Column property of the combo box to get the value from the desired column. To get the name of the subform control, open the main form in design mode and open the Properties sheet. Click on the subform ONE time. The properties sheet should show the name of the subform control. If you click more than once, you'll be in the subform and the properties sheet will show the name of the subform or its controls. Once you have the name of the subform control, the syntax would be: [Forms]![NameOfMainForm]![NameOfSubformControl].[Form]! [cboCltName] I tried referring to a particular column in the SQL, but I don't know if I wasn't getting the bracketing correct or is SQL just doesn't like specifying a property of the combo box. But I couldn't get it to work. So, if the Value of the combo box isn't the Client Name, you'll need to filter on the field correlates to the bound column. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message ... Yes that would have helped wouldn't it :-) SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltName)=[Forms]! [frmSubStatic]! [cboCltName])); Hope this makes sense. . . |
#5
|
|||
|
|||
In the AfterUpdate event of the combo on the main form try,
Me.frmSubDynamic.Form!cboCltName.Requery Do this after you have assigned the SQL. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message ... Forget that, I have it working now with some tweaking. Problem is now I can't seem to figure out the Requery command to make it refresh the list - it's only showing an abbreviated list from one client number... -----Original Message----- You're right, I was referencing the second column of cboCltName. However when I altered the query to reflect this (qryWorkerLookup) it still didn't work. Here is the SQL so far: SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltNumber)=[Forms]! [frmSubStatic]! [frmSubDynamic].[Form]![cboCltName])); Both cboCltName and cboTempName are referencing the second column (to save a numerical value in the underlying table), but all fields are available to the query. Is there any further info I can post, because so far it's still blanking me... -----Original Message----- Aaron, Judging by the name you have used in referring to the combo box, it appears that the combo box is on the subform. If so, the syntax does NOT include the name of the subform. The subform is actually held in a control on the main form called a subform control. You need to refer to this control instead. It may or may not have the same name as the subform, depending on whether you changed it or on how you added the subform to the main form. Also, since you are referring to a combo box, how many columns are in the combo box that you are referring to? If there is more than one, be aware that you are referring to the Value property and that will come from the Bound column, which is not necessarily the column that is displayed in the textbox portion of the combo box. If that is the problem, you can either change which field you are filtering on or use the Column property of the combo box to get the value from the desired column. To get the name of the subform control, open the main form in design mode and open the Properties sheet. Click on the subform ONE time. The properties sheet should show the name of the subform control. If you click more than once, you'll be in the subform and the properties sheet will show the name of the subform or its controls. Once you have the name of the subform control, the syntax would be: [Forms]![NameOfMainForm]![NameOfSubformControl].[Form]! [cboCltName] I tried referring to a particular column in the SQL, but I don't know if I wasn't getting the bracketing correct or is SQL just doesn't like specifying a property of the combo box. But I couldn't get it to work. So, if the Value of the combo box isn't the Client Name, you'll need to filter on the field correlates to the bound column. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message .. . Yes that would have helped wouldn't it :-) SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltName)=[Forms]! [frmSubStatic]! [cboCltName])); Hope this makes sense. . . |
#6
|
|||
|
|||
That works, thank you very much Wayne. I've learned a lot
from those pieces of code, hopefully I'll have the confidence to fix the next few problems I'm going to encounter now! -----Original Message----- In the AfterUpdate event of the combo on the main form try, Me.frmSubDynamic.Form!cboCltName.Requery Do this after you have assigned the SQL. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message ... Forget that, I have it working now with some tweaking. Problem is now I can't seem to figure out the Requery command to make it refresh the list - it's only showing an abbreviated list from one client number... -----Original Message----- You're right, I was referencing the second column of cboCltName. However when I altered the query to reflect this (qryWorkerLookup) it still didn't work. Here is the SQL so far: SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltNumber)=[Forms]! [frmSubStatic]! [frmSubDynamic].[Form]![cboCltName])); Both cboCltName and cboTempName are referencing the second column (to save a numerical value in the underlying table), but all fields are available to the query. Is there any further info I can post, because so far it's still blanking me... -----Original Message----- Aaron, Judging by the name you have used in referring to the combo box, it appears that the combo box is on the subform. If so, the syntax does NOT include the name of the subform. The subform is actually held in a control on the main form called a subform control. You need to refer to this control instead. It may or may not have the same name as the subform, depending on whether you changed it or on how you added the subform to the main form. Also, since you are referring to a combo box, how many columns are in the combo box that you are referring to? If there is more than one, be aware that you are referring to the Value property and that will come from the Bound column, which is not necessarily the column that is displayed in the textbox portion of the combo box. If that is the problem, you can either change which field you are filtering on or use the Column property of the combo box to get the value from the desired column. To get the name of the subform control, open the main form in design mode and open the Properties sheet. Click on the subform ONE time. The properties sheet should show the name of the subform control. If you click more than once, you'll be in the subform and the properties sheet will show the name of the subform or its controls. Once you have the name of the subform control, the syntax would be: [Forms]![NameOfMainForm]![NameOfSubformControl].[Form]! [cboCltName] I tried referring to a particular column in the SQL, but I don't know if I wasn't getting the bracketing correct or is SQL just doesn't like specifying a property of the combo box. But I couldn't get it to work. So, if the Value of the combo box isn't the Client Name, you'll need to filter on the field correlates to the bound column. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message . .. Yes that would have helped wouldn't it :-) SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltName)=[Forms]! [frmSubStatic]! [cboCltName])); Hope this makes sense. . . . |
#7
|
|||
|
|||
And to prove the point, I've just added a second
validation on the field to another criteria to filter it further - and it works perfectly after I moved the AfterUpdate event! I hope Santa brings you something extra this Christmas in recognitition of your good deed ;-) -----Original Message----- That works, thank you very much Wayne. I've learned a lot from those pieces of code, hopefully I'll have the confidence to fix the next few problems I'm going to encounter now! -----Original Message----- In the AfterUpdate event of the combo on the main form try, Me.frmSubDynamic.Form!cboCltName.Requery Do this after you have assigned the SQL. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message ... Forget that, I have it working now with some tweaking. Problem is now I can't seem to figure out the Requery command to make it refresh the list - it's only showing an abbreviated list from one client number... -----Original Message----- You're right, I was referencing the second column of cboCltName. However when I altered the query to reflect this (qryWorkerLookup) it still didn't work. Here is the SQL so far: SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltNumber)=[Forms]! [frmSubStatic]! [frmSubDynamic].[Form]![cboCltName])); Both cboCltName and cboTempName are referencing the second column (to save a numerical value in the underlying table), but all fields are available to the query. Is there any further info I can post, because so far it's still blanking me... -----Original Message----- Aaron, Judging by the name you have used in referring to the combo box, it appears that the combo box is on the subform. If so, the syntax does NOT include the name of the subform. The subform is actually held in a control on the main form called a subform control. You need to refer to this control instead. It may or may not have the same name as the subform, depending on whether you changed it or on how you added the subform to the main form. Also, since you are referring to a combo box, how many columns are in the combo box that you are referring to? If there is more than one, be aware that you are referring to the Value property and that will come from the Bound column, which is not necessarily the column that is displayed in the textbox portion of the combo box. If that is the problem, you can either change which field you are filtering on or use the Column property of the combo box to get the value from the desired column. To get the name of the subform control, open the main form in design mode and open the Properties sheet. Click on the subform ONE time. The properties sheet should show the name of the subform control. If you click more than once, you'll be in the subform and the properties sheet will show the name of the subform or its controls. Once you have the name of the subform control, the syntax would be: [Forms]![NameOfMainForm]![NameOfSubformControl]. [Form]! [cboCltName] I tried referring to a particular column in the SQL, but I don't know if I wasn't getting the bracketing correct or is SQL just doesn't like specifying a property of the combo box. But I couldn't get it to work. So, if the Value of the combo box isn't the Client Name, you'll need to filter on the field correlates to the bound column. -- Wayne Morgan MS Access MVP "Aaron Howe" wrote in message .. . Yes that would have helped wouldn't it :-) SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo FROM qryWorkerLookup WHERE (((qryWorkerLookup.CltName)=[Forms]! [frmSubStatic]! [cboCltName])); Hope this makes sense. . . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cascading combo box data disappearing from form | Susan L | Using Forms | 7 | November 16th, 2004 05:13 PM |
Cascading Combo Boxes | Tom | Using Forms | 1 | June 9th, 2004 02:04 AM |
Cascading Combo Boxes -- Part 2 | rich | Using Forms | 2 | June 8th, 2004 10:29 PM |
Cascading Combo Boxes -- Part 1 | rich | Using Forms | 0 | June 8th, 2004 10:03 PM |
Cascading Combo Boxes | Tom | Using Forms | 0 | June 8th, 2004 09:24 PM |