Access Help (again!)

[Damien]

Hi having another problem with access.

Basically,

I have 3 levels of data:

Company
Department
Contacts

You choose the company first, then the department, then the contacts. I did this bu having a sub form of contacts embedded in department and department embedded in company.

Now, this does work, it only will display the departments that match the company and the contacts that much the department.

The problem is that I wanted a drop down box to select the department and the same in contacts. Access has this feature but when I use it, it will display every possible value and not simply the ones that are in that company or department. When you select the ones that are not part of the company it will not show anything but its still a pretty bad bug.

Any ideas on how to get around it?

[Raistlin]

Ages since I've done this but I think you need to set up your drop-downs so that they get their results from a query, rather than from a table/list.

Once you've done this you can requery them depending on the value of the others.

It's a bit difficult to explain really, and a bit dificcult to understand exactly what you've got and how it's working, just from a post in a forum.

Take another look at the way the data/options are being picked up in the drop-downs, and see if you can get the to work from queries. I think that should sort it

[Damien]

Mmm, I was thinking a Query as well. I'll try that

[Raistlin]

K, if you get stuck give me a shout. As I've said before, I'm more than happy to take a look if you think a second pair of eyes will help

[Damien]

Cool, I may do that. I hope MySQL isnt this annoying. The query does work in that it will only allow the person to view the values that depend on the department. But I cant search the records with that drop down box! :S I will see

[gazzae]

Create a query that contains the data you need.

So you would have fields say Company | Department

In the critera for the Company field enter iif(isnull([Forms]![YOUR FORM NAME]![field name of company drop down],[Company],[Forms]![YOUR FORM NAME]![field name of company drop down])

then on the form select the department drop down and enter the table name as the row source.

[Damien]

I would then need to requery everytime something is updated as well right?

[Raistlin]

I think that if the field is based on a query it should automatically requery it each time the form/field is refreshed.

[gazzae]

Originally Posted by Raistlin

I think that if the field is based on a query it should automatically requery it each time the form/field is refreshed.

Yeah it will.

---------- Post added at 11:26 ---------- Previous post was at 11:24 ----------

Was missing a )

iif(isnull([Forms]![YOUR FORM NAME]![field name of company drop down]),[Company],[Forms]![YOUR FORM NAME]![field name of company drop down])

[Damien]

it tells me to enter a parameter value in? :S

[gazzae]

Originally Posted by Damien

it tells me to enter a parameter value in? :S

If you open the query directly it will

Does it work when you use the dropdowns?

[Damien]

nope! :S That's what I was doing. I'll double check the form names and such

[gazzae]

If your still having probs get rid of the isnull stuff and just put in as the criteria..

[Forms]![YOUR FORM NAME]![field name of company drop down]

[Damien]

IIf(IsNull([Forms]![Contacts]![Combo28]),[Department],[Forms]![Contacts]![Contacts])

I am doing Department | Contacts first. Remember that the contacts form is a subfrom in the department form.

[gazzae]

Originally Posted by Damien

IIf(IsNull([Forms]![Contacts]![Combo28]),[Department],[Forms]![Contacts]![Contacts])

I am doing Department | Contacts first. Remember that the contacts form is a subfrom in the department form.

It should be..

IIf(IsNull([Forms]![Contacts]![Combo28]),[Department],[Forms]![Contacts]![Combo28])


try just putting in..

[Forms]![Contacts]![Combo28]