Drop-down list depending on the value of another field

[stemar]

Amongst other tables in my property maintenance database, I have the following:

Jobs: Jobs that need to be done
Job Code, Contractor, etc

Job Types: Types of Job
Job Code, Description

Contractors: List of Contractors who may be given a job
ID, Name, etc.

Contractors & Jobs: Links contractors to the type of jobs they do
ID, Job code

I have a form for entering jobs, and I’d like a dropdown list in the Contractors field that will give a list of contractors who undertake this kind of work, so that if the Job Type code is BD01, the list will give a list of Contractors who do BD01 work. As I understand it, I’ll need a query something like

Select ID code, Name
from Contractors where ID code =
(Select ID code
from Contractors & Jobs
where job code = JobsForm. Job Code)

I know this isn’t right, but my SQL’s very rusty! Can anyone help me get it right? Alternatively, I’m while I’ve never used VB for Access, I’m halfways competent at VBA for Excel. Would I be better off with a VB userform and a button?

I’m using Access 2002 & WinXP Pro

 

[Dugantrain]

You can do this with the barest minimum SQL. In the Combo Box's Properties, go to its Recordsource and click on the three dots. It will then pull up a Query Builder. Add the three tables that you need, join them if they don't automatically join themselves, and pull all of the fields from the tables that you're going to want to see in your combo box. In the criteria of Job Type Code, you're going to have it look at the control on your form which you've populated with the Job Type Code (probably a text box or another combo box, right?):

Forms!Your_Form.Form!Your_Job_Type_Code_Control

The only thing is that you're going to have to decide exactly when you want this combo to Requery; I'm thinking it will probably be on the Lost_Focus event of the Job Type Code control if it's a text-box or the After_Update event if it's a combo box.

 

[stemar]

That worked fine. Many thanks