Filter from Combo Box

[Maxi2011]

Hi

I have three main tables.

Supplier_tbl which holds supplier details
Contracts_tbl which holds contract details
Owners_Tbl which holds details of the person is responsible for the contract


I have forms based on all of these which are used for data entry (by the administrator) and a summary view (by the users). I’ve got it set up so the users always view the forms in read-only mode. I could change this if needed and just lock out the fields I don't want them to be able to interact with.

The ‘Owner’ and ‘Supplier’ fields on the Contracts_frm are looked up from the relevant tables and are displayed as drop-down boxes. What I’d like to know is if it’s possible to set up those drop down-boxes so that when the user chooses a Supplier Name or Owner Name from them, a filter is run and the user will be displayed only a list of the contracts related to that supplier or owner. For example if you select Acme from the supplier list, you will be take to the contract which you have with Acme Plc.

I have a feeling I’ll need VBA for this? Perhaps setting something to the AfterUpdate property……Just not sure what. Can I do this? If so how. Please bear in mind, I’m new to all this. Especially VBA!

Thanks!

 

[Maxi2011]

Pat

Thanks for the reply! Just got around to having a look......

Think I might not have explained what I want to do properly though.

On my Contracts_frm I have a field called 'Supplier Name' this is looked up from 'Suppliers_tbl' and is therefore displayed on the form as a dropdown box. If I change this box from 'Supplier A' to 'Supplier B' then just that field on the form will change. All the other fields will remain the same.

What I would like to do is make it so that if they user changes the 'Supplier Name' field from 'Supplier A' to 'Supplier B' all the other fields will change to show the contract for 'Supplier B'. If there is more than one contract for 'Supplier B' then i'd like the user to be able to scroll through these.

I have it set up right now so the users can't change the form content (read-only), only I have write access. However, i could change this so that all the fields are locked except 'Supplier Name' so they can change that content I think....

Hope this is better explained. As I said I'm not sure if this is possible. If it's not, is there a way I could have this field show without the drop-down option box (but remain a lookup field in the tables) because right now it's just confusing my users!

Thanks again!
Maxi2011

 

[Maxi2011]

Pat

The example is great! Seems what I was trying to do was a cascading combo which used Dlookup!

Thanks again for the help and sorry to boggle the mind

Maxi