Make one ComboBox list Dependent on Another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have as form with two combo boxes that force the user to select
combinations of values that have been pre-defined in 'Setup' table. The
RowSource of the second is:

SELECT DISTINCT tblDeviceFunctions.Function FROM tblDeviceFunctions WHERE
(((tblDeviceFunctions.DeviceType)=[CodeContextObject]![cboDeviceType])) ORDER
BY tblDeviceFunctions.Function;

In the AfterUpdate event of cboDeviceType there is a cboDeviceFunction.Requery
and this has worked up to now... when we updated to Access 2003.
Now, when it hits the .Requery it opens a dialog box asking for the
parameter 'CodeContextObject'. I have looked in the object browser and see
that there is still a property 'Application.CodeContextObject', so why
doesn't it know?
 
?The name of your form is "CodeContextObject"?

The generic syntax, when you are within a form, would be something like:

WHERE tblDeviceFunctions.DeviceType = Me!cboDeviceType

Good luck

Jeff Boyce
<Access MVP>
 
No, the name of the form is not 'CodeContextObject', CodeContextObject is a
pointer to the Object in which the Code is running...which is the same as
'Me', but I've never been able to use the 'Me' reference in an SQL statement
that is stored in the RowSource of a combo or list box, but the
CodeContextObject has always worked in the past.

Have I been using this incorrectly all this time... since Access 2.0?




In this case, the form is used as a subform (under a Tab Control) of the
main form.

Jeff Boyce said:
?The name of your form is "CodeContextObject"?

The generic syntax, when you are within a form, would be something like:

WHERE tblDeviceFunctions.DeviceType = Me!cboDeviceType

Good luck

Jeff Boyce
<Access MVP>

KMac said:
I have as form with two combo boxes that force the user to select
combinations of values that have been pre-defined in 'Setup' table. The
RowSource of the second is:

SELECT DISTINCT tblDeviceFunctions.Function FROM tblDeviceFunctions WHERE
(((tblDeviceFunctions.DeviceType)=[CodeContextObject]![cboDeviceType]))
ORDER
BY tblDeviceFunctions.Function;

In the AfterUpdate event of cboDeviceType there is a
cboDeviceFunction.Requery
and this has worked up to now... when we updated to Access 2003.
Now, when it hits the .Requery it opens a dialog box asking for the
parameter 'CodeContextObject'. I have looked in the object browser and
see
that there is still a property 'Application.CodeContextObject', so why
doesn't it know?
Click to expand...
Click to expand...
 
I have as form with two combo boxes that force the user to select
combinations of values that have been pre-defined in 'Setup' table. The
RowSource of the second is:

SELECT DISTINCT tblDeviceFunctions.Function FROM tblDeviceFunctions WHERE
(((tblDeviceFunctions.DeviceType)=[CodeContextObject]![cboDeviceType])) ORDER
BY tblDeviceFunctions.Function;

In the AfterUpdate event of cboDeviceType there is a cboDeviceFunction.Requery
and this has worked up to now... when we updated to Access 2003.
Now, when it hits the .Requery it opens a dialog box asking for the
parameter 'CodeContextObject'. I have looked in the object browser and see
that there is still a property 'Application.CodeContextObject', so why
doesn't it know?
Click to expand...

This is all being done within the Form class code module?
Leave the RowSource of the second combo box blank.

If the [DeviceType] datatype is Number, code the First combo box
AfterUpdate event:

Combo2.Rowsource = "SELECT DISTINCT tblDeviceFunctions.Function FROM
tblDeviceFunctions WHERE tblDeviceFunctions.DeviceType = " &
Me![cboDeviceType] & " ORDER BY tblDeviceFunctions.Function;"

If the [DeviceType] datatype is Text, then use:

Combo2.Rowsource = "SELECT DISTINCT tblDeviceFunctions.Function FROM
tblDeviceFunctions WHERE tblDeviceFunctions.DeviceType = '" &
Me![cboDeviceType] & "' ORDER BY tblDeviceFunctions.Function;"

No need to Requery.
 
I know your suggestion will work, but it is also what I was trying to avoid.
I have many instances of this in my application and wanted to avoid the
re-write. I just didn't know why Access could evaluate the
Application.CodeContextObject property in an SQL statement at run-time in all
the previous versions, but can't in Access 2003. Reguardless, thanks for
your response. At least you have confirmed the direction I will need to
persue... It sure was nice not having to redefine the RowSource in the
previous versions, but I guess there's a price to pay for progress.

fredg said:
I have as form with two combo boxes that force the user to select
combinations of values that have been pre-defined in 'Setup' table. The
RowSource of the second is:

SELECT DISTINCT tblDeviceFunctions.Function FROM tblDeviceFunctions WHERE
(((tblDeviceFunctions.DeviceType)=[CodeContextObject]![cboDeviceType])) ORDER
BY tblDeviceFunctions.Function;

In the AfterUpdate event of cboDeviceType there is a cboDeviceFunction.Requery
and this has worked up to now... when we updated to Access 2003.
Now, when it hits the .Requery it opens a dialog box asking for the
parameter 'CodeContextObject'. I have looked in the object browser and see
that there is still a property 'Application.CodeContextObject', so why
doesn't it know?
Click to expand...

This is all being done within the Form class code module?
Leave the RowSource of the second combo box blank.

If the [DeviceType] datatype is Number, code the First combo box
AfterUpdate event:

Combo2.Rowsource = "SELECT DISTINCT tblDeviceFunctions.Function FROM
tblDeviceFunctions WHERE tblDeviceFunctions.DeviceType = " &
Me![cboDeviceType] & " ORDER BY tblDeviceFunctions.Function;"

If the [DeviceType] datatype is Text, then use:

Combo2.Rowsource = "SELECT DISTINCT tblDeviceFunctions.Function FROM
tblDeviceFunctions WHERE tblDeviceFunctions.DeviceType = '" &
Me![cboDeviceType] & "' ORDER BY tblDeviceFunctions.Function;"

No need to Requery.
Click to expand...
 
Back
Top