Cascading combo box

[rexb]

I've a cascading combo box that when I choose the first box I get a message

runtime error '-2147352567(80020009)' You tried to assign the Null value to a variable that is not a Variant data type.

I use query as a record source. Any Idea?

 

[rexb]

This is on the first combo -

SELECT DISTINCT tblMaintenanceCategory.MaintenanceCategoryId, tblMaintenanceCategory.MaintenanceCategoryDescription FROM tblMaintenanceCategory ORDER BY tblMaintenanceCategory.MaintenanceCategoryId;

This is from the second combo
SELECT DISTINCT tblMaintenanceRequest.MaintenanceRequestId, tblMaintenanceRequest.MaintenanceId, tblMaintenanceRequest.MaintenanceRequest FROM tblMaintenanceRequest WHERE (((tblMaintenanceRequest.MaintenanceId)=Forms!frmFacilitiesEntryForm!cboMaintenanceCategoryId)) ORDER BY tblMaintenanceRequest.MaintenanceRequestId;

Please keep in mind I use query as record source. thanks so much for the help

 

[John Big Booty]

Can you post a copy of your DB?

 

[wiklendt]

is MaintenanceID and MaintenanceCategoryID the same thing, but in two different tables? (i.e., PK in one and FK in another)? if they are depicting something different, that's where your problem lies

(i have started naming my FK as identical to PK, to avoid confusion downstream)

i.e., my employee table will have:

EmployeeID
EmployeeName
EmployeeNumber
.
.
.

my job title table will have:

JobTitleID
JobName

and my job title junction table will have:

JobEmployeeID
JobTitleID
EmployeeID
.
.
.
.

 

[rexb]

Is your combo Bound?

yes it is bound

 

[rexb]

is MaintenanceID and MaintenanceCategoryID the same thing, but in two different tables? (i.e., PK in one and FK in another)? if they are depicting something different, that's where your problem lies

(i have started naming my FK as identical to PK, to avoid confusion downstream)

i.e., my employee table will have:

EmployeeID
EmployeeName
EmployeeNumber
.
.
.

my job title table will have:

JobTitleID
JobName

and my job title junction table will have:

JobEmployeeID
JobTitleID
EmployeeID
.
.
.
.

It's the same thing on one table

 

[rexb]

Can you post a copy of your DB?

the form's name is frmFacilitiesEntryForm

and also frmQryInProgress&Pending is the first form that displays when I was just using tables as source and not query when I click on the record selector on the side it will bring up that record but when I changed it to query as source it will not display that record any more and will only allow me to add record.

this is way over my head so your (everybody's) help would be very much appreciated

 

[John Big Booty]

Have a look at the attached. I've fixed the cascading combo box, and now that there is some data in the DB your inital form is working.

 

[rexb]

Have a look at the attached. I've fixed the cascading combo box, and now that there is some data in the DB your inital form is working.

Thanks John, can you tell what you did for reference purposes.

 

[rexb]

When you choose any option on combo box 1, combo box 2 will only give you the first option with reference to combo box 1. If you choose the other options it will revert to the first option in combo box 1. Why is that?

e.g.
combo box1 have 3 options = it will only accept the first option
combo box2 have 5 options = it will only accept the first option
combo box3 have 9 options = it will only accept the firts option etc.

 

[John Big Booty]

Which version of the DB are you working on? If it has been modified since the version I posted, are you able to post a copy.

Which form are you working on?

 

[rexb]

Which version of the DB are you working on? If it has been modified since the version I posted, are you able to post a copy.

Which form are you working on?

It's the one you posted

 

[John Big Booty]

It was just a simple problem of the wrong column being bound in the combo box, now fixed; I think

 

[rexb]

Thanks John. It's working now. thank you! thank you!