Desperate for help: How to base combo box on another combo box in 2007?

[LocalUser]

Hi all,

I've been searching on the web for so long trying every method to do that but nothing seem to be working with me.

I have database contains of one table "ProjectDetails" and the following fields:
"EDWNumber" - Primary key
"SchoolName"
"OfficerNumber"
"OfficerName"

each officer has number of schools.

I want a form for "Visits" so each officer can fill in when they make visit to certain school.
The first combo box would have all officers names to choose from, then I want the second combo box to list ONLY schools which are allocated to this officer chosen in the first choice.

I tried so many ways so far ... please help if you can!!!

 

[pbaldy]

Here are a couple of methods, since you haven't posted what you've tried:

http://www.baldyweb.com/CascadingCombo.htm

 

[LocalUser]

Here are a couple of methods, since you haven't posted what you've tried:

Thank you very much, but both ways didn't work.
I've must been doing something wrong.
I followed your instructions step by step but no change. My second combo box still doesn't refresh.

 

[John Big Booty]

Can you post a copy of your DB?

 

[LocalUser]

Can you post a copy of your DB?

I can't post the database here, but I can send it to you if you wish.

 

[exebat]

If its privacy you are worried about, just create a new database and import only the form and the table you are having the problem with.

And also leave only few records.

 

[exebat]

If its privacy you are worried about, just create a new database and import only the form and the table you are having the problem with.

And also leave only few records.

 

[LocalUser]

If its privacy you are worried about, just create a new database and import only the form and the table you are having the problem with.

And also leave only few records.

Not privacy issue, I think the size was too big but now it's OK

 

[LocalUser]

My 2 combo boxes are the first two on top left:

Professional Licensing ID

Unique EDW Number

 

[John Big Booty]

Another trick if your DB is a little big is to first do a compact and repair on it and then put it in a Zip file prior to posting it.

I only have Access '03 here, I'll try and have a look latter when I have access to Access '07

 

[LocalUser]

Another trick if your DB is a little big is to first do a compact and repair on it and then put it in a Zip file prior to posting it.

I only have Access '03 here, I'll try and have a look latter when I have access to Access '07

Here is the file in access 2003

 

[John Big Booty]

If I'm following correctly the problems seems to be in this line of code;

Me.EDWNumber = vbNullString

otherwise everything seems to work as advertised. If you change the offending line to;

Me.EDWNumber = 0

That will clear any data may already have been selected in the Combo, you can then trap this to ensure a the Combo is holding a valid selection from the combo, by puttin the following code in the Form's Before Update event;

    If Me.EDWNumber = 0 Then
        MsgBox "EDWNumber must not be blank, PLease make a selection from the list"
        Me.EDWNumber.SetFocus
        Cancel = True
    End If

 

[LocalUser]

If I'm following correctly the problems seems to be in this line of code;

Me.EDWNumber = vbNullString

otherwise everything seems to work as advertised. If you change the offending line to;

Me.EDWNumber = 0

That will clear any data may already have been selected in the Combo, you can then trap this to ensure a the Combo is holding a valid selection from the combo, by puttin the following code in the Form's Before Update event;

    If Me.EDWNumber = 0 Then
        MsgBox "EDWNumber must not be blank, PLease make a selection from the list"
        Me.EDWNumber.SetFocus
        Cancel = True
    End If

Hi John,

I followed exactly what you said but it didn't work. Now I really don't know what to do.

Thanks for your help !!

 

[John Big Booty]

I've made the changes and they work for me

 

[LocalUser]

I've made the changes and they work for me

Hi John,

I checked them on the database that you have attached , but they are not working.
Still when I move to new record the second combo box doesn't change. It still holds the same list for the previous choice.

 

[John Big Booty]

In the Form's On Current event put the following;

Me.EDWNumber.Requery

 

[John Big Booty]

...you do have the DB in a trusted location? and you have enabled the content?

 

[SmallTime]

Not sure if this may help but;

OfficerNumber_AfterUpdate()
Me.EDWNumber = vbNullString

However,
OfficerNumber in EDWNumber.Rowsource
Criteria =[Forms]![ReportDetails]![OfficerNumber]

Isn't this causing a conflict?

It's a little late here so I could be barking up the wrong tree, let me know if I'm wrong and I'll take a closer look in the morning.

Regards
SmallTime

 

[LocalUser]

...you do have the DB in a trusted location? and you have enabled the content?

Yes I have done that John.
I put also the "Current event" as you said but no change.
It's driving me crazy !!

 

[John Big Booty]

Did you try the sample I uploaded?

When you make a change to Combo OfficerNumber, you should see that the selection within Combo EDWNumber changes. Does this happen for you or are you expecting something else