Tweaking my Cascading Combo Boxes (1 Viewer)

Singh400 · Nov 5, 2008

I’ve successfully managed to implement Cascading Combo Boxes into my new database.

Quick overview:
cboOrg is the main combo box
cboDept values are dependent on cboOrg selection.

Now all this works fine.

However, I need to add one value (let’s call this ‘Dept X’)’ to all the Organisation sub-data.

What the selectable criteria currently looks like...

cboOrg: Org A
cboDept: Dept A, Dept G, Dept T

What I want to achieve...

cboOrg: Org A
cboDept: Dept A, Dept G, Dept T, Dept X

So no matter what value I select in cboOrg. ‘Dept X’ should always appear in cboDept.

Any idea how I can do this?

The only way I can think of how to do this is tedious and not a very good solution. It would involve me going back and manually adding ‘Dept X’ to every single Org.

Should it help, this is the code I’m using:

Code:

Private Sub Organisation_AfterUpdate()
    On Error Resume Next
    Department.RowSource = "Select tblService_Users.Department " & _
        "FROM tblService_Users " & _
        "WHERE tblService_Users.Organisation = '" & Organisation.Value & "' " & _
        "ORDER BY tblService_Users.Department;"
End Sub

Thank you for any help!

John Big Booty · Nov 5, 2008

Add

Code:

OR tblService_Users.Organisation = DEPTX

to the WHERE statment.

Singh400 · Nov 5, 2008

Thank you for the very quick reply John. Does this looks right to you?

Private Sub Organisation_AfterUpdate()
On Error Resume Next
Department.RowSource = "Select tblService_Users.Department " & _
"FROM tblService_Users " & _
"WHERE tblService_Users.Organisation = '" & Organisation.Value & "' OR tblService_Users.Organisation = '" Dept X "' " & _
"ORDER BY tblService_Users.Department;"
End Sub

I just tried the above code and it's spitting an error back at me

Cheers for your help.

John Big Booty · Nov 5, 2008

Does Organisation.Value return a numeric value or a text value such as "Dept A"?

Singh400 · Nov 5, 2008

John Big Booty said:
Does Organisation.Value return a numeric value or a text value such as "Dept A"?

A text value.

I'll describe what the data is in a little more detail should it help.

First of all, we provide a service to health care professionals in the UK.

So in Org we might have Goodhope Hospital and within that Org we have the following departments that use our services. Lets say Dept's 'X-Ray Dept', 'Maternity Dept' and 'Physiotherapy Dept'.

We have over 300 Orgs and most if not all have 5 or more Depts that use us.

So if I was to select Goodhope Hospital in the cboOrg, the only values I would be able to select in cboDept would be 'X-Ray Dept', 'Maternity Dept' and 'Physiotherapy Dept'.

Now all this works fine with the current solution in place.

However, what happens sometimes is that the Hospital (or whatever health care service) refers the patient to a specialist clinic, this is my "Dept X". So thus is my reason for adding "Dept X" to every selectable criteria in cboDept.

John Big Booty · Nov 6, 2008

OK. I think I've figured it out. What you will need to do is create a dummy Hospital. Then in your hospital dept table you would link this hospital to "Dept X" only.

Now in the criteria for your cascading combo, you need to put OR the id that corresponds to the Dummy Hospital.

You will also need to ensure that you exclude the ID of the Dummy Hospital from any combo where Hospitals are selected.

It may be clearer if you have a look at my sample.

There may be a more elegant method but it eludes me.

Singh400 · Nov 6, 2008

I did it, had a brainwave just playing about with the code, today at work. Cheers for all your help John. I would have never of got there if it wasn't for your help. I'm very grateful. THANK YOU!

John Big Booty said:
OK. I think I've figured it out. What you will need to do is create a dummy Hospital. Then in your hospital dept table you would link this hospital to "Dept X" only.

Now in the criteria for your cascading combo, you need to put OR the id that corresponds to the Dummy Hospital.

You will also need to ensure that you exclude the ID of the Dummy Hospital from any combo where Hospitals are selected.

It may be clearer if you have a look at my sample.

There may be a more elegant method but it eludes me.

Yeah thats what I was forgetting. "Dept X" comes under "Org X", so in order to have "Dept X" show up on every list, no matter what value is selected in cboOrg. The additional code should have been:

Code:

OR tblService_Users.Organisation = "Org X"

and not...

Code:

OR tblService_Users.Organisation = DEPTX

It's hard not to get confused, with fancy code/sql statements like that.

Now for another question, I like picking your brains

When I create a new record, cboDept remembers the last available choices in it's drop down selection list. Is there any way I can clear this? I know what is causing this; it's the AfterUpdate EventProcedure. I've tried things like cboDept = null, but thats complete messes things up. Any ideas mate?

Edit* Do you think it's better to use the requery method to implement cascading combo boxes? Just wondering.

John Big Booty · Nov 6, 2008

Singh400 said:
I did it, had a brainwave just playing about with the code, today at work. Cheers for all your help John. I would have never of got there if it wasn't for your help. I'm very grateful. THANK YOU!
.......

Glad you got it working

Singh400 said:
..........

When I create a new record, cboDept remembers the last available choices in it's drop down selection list. Is there any way I can clear this? I know what is causing this; it's the AfterUpdate EventProcedure. I've tried things like cboDept = null, but thats complete messes things up. Any ideas mate?

..........

Thats what I would have suggested. What happens if you set cboDept = null when you move to a new record?

Singh400 said:
...........

Edit* Do you think it's better to use the requery method to implement cascading combo boxes? Just wondering.

I've not used any other method, so I can't really comment. Although I do find it easier to work with a query rather than the raw SQL.

Singh400 · Nov 6, 2008

John Big Booty said:
Thats what I would have suggested. What happens if you set cboDept = null when you move to a new record?

The cbo remains permantly blank (depending on where I add it), even when I select a value from the drop down list. It resets after my selection. Where would you add cboDept = null to?

John Big Booty said:
I've not used any other method, so I can't really comment. Although I do find it easier to work with a query rather than the raw SQL.

Ahhh noted. Cheers again.

I'm off to bed now, I promised myself I wouldn't bring work home with me. Yet here I am!

John Big Booty · Nov 6, 2008

Could you do a logical test, in the on current event, on a control that would be Null or have a known value if you are on a new record, and then set cboDept = null ?

Singh400 · Nov 6, 2008

John you will have to enlighten me on how to do this. I'm completely new to the VBA side of Access.

I know where to put it, just don't know what to put there. Would it be an if statement in OnCurrent?

John Big Booty · Nov 7, 2008

You will need to choose a control that you can rely on to have a consistent value on a new record, for augments sake we'll say we are testing for a null value but it could be any value.

Code:

If isnull(ControlNameOfYourChoice) then
     Me.cboDept.Value = Null
End If

Put this in the On Current event.

Singh400 · Nov 7, 2008

Thanks for that John. But it doesn't work, plus it breaks some functionality in the database. It's not a big issue, just a personal annoyance, that the cboDept remembers the last available options - this is soon cleared when the EndUser selects a value in cboOrg, besides cboOrg always comes before cboDept in order of filling out the form. And I can always set cboDept to enabled = no, untill a value is select in cboOrg to stop any curious EndUser's from messing things up.

Thanks for the ALL the help John. Very much appreciated!!

John Big Booty · Nov 7, 2008

If it's just an aesthetic thing, You could just hide cboDpt (ie. cboDpt.Visible = No) until a selection has been made in cboOrg.

Singh400 · Nov 7, 2008

John Big Booty said:
If it's just an aesthetic thing, You could just hide cboDpt (ie. cboDpt.Visible = No) until a selection has been made in cboOrg.

Yeah, I could do that too. But I've set it to enabled = no untill a value in cboOrg is selected, for now. I would hide it like you've suggested, but some of our EndUsers aren't very technical, and I'm pretty sure they would come running when they find that "a thing has gone from the computer".

John Big Booty · Nov 7, 2008

Fair enough, have fun

Singh400 · Jan 22, 2009

Sorry for the old-topic-bump...

Singh400 said:
Edit* Do you think it's better to use the requery method to implement cascading combo boxes? Just wondering.

To answer my own question. The .Requery method is better for cascading combo boxes.

Why you ask?

Because using the other method spits out an error when data being filtered contains an apostrophe - I assume because Access interpreters this as a command instead of it being included in the data. Wrapping it in quotation marks doesn't help either. So I switched over to the .Requery method with the same dataset and it work

Tweaking my Cascading Combo Boxes (1 Viewer)

Singh400

Registered User.

John Big Booty

AWF VIP

Singh400

Registered User.

John Big Booty

AWF VIP

Singh400

Registered User.

John Big Booty

AWF VIP

Attachments

Singh400

Registered User.

John Big Booty

AWF VIP

Singh400

Registered User.

John Big Booty

AWF VIP

Singh400

Registered User.

John Big Booty

AWF VIP

Singh400

Registered User.

John Big Booty

AWF VIP

Singh400

Registered User.

John Big Booty

AWF VIP

Singh400

Registered User.

Similar threads

Users who are viewing this thread