comboboxes

[rcollins]

I have two combo boxes, combo1 and combo2. Combo1 has all of the different employers that our clients work for. Cobo2 has the job descriptions that each of the employers have. On the form, I want to be able to pick an emplyer from combo1 then have combo2 only show what corrosponds with combo1. I have done this once with seperate tables for the different values in the table for combo1, but since there are always employers and job descriptions being added, it won't work this way. Any suggestions?

[missinglinq]

The concept is called cascading comboboxes and here's a link to a HowTo on the subject authored by Rabbit of this forum:

http://bytes.com/forum/thread605958.html

Linq ;0)>

[rcollins]

So I followed the instructions, they sound pretty simple, but all I get in the second dropdown is an empty list. Here is what I have, am I missing something?
My two comboboxes are called Employer and JobDescription.

tblEmployer
[ID] - AutoNNumber, PK
[Name] - Text, Name of Employer

tblEmployerJobs
[JobTitle] - Text, Description of Job
[EmployerID] - FK, Used to link Job to Employer
[ID] - AutoNumber, PK

Private Sub Employer_AfterUpdate()
  With Me![JobDescription]
    If IsNull(Me!Employer) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [JobTitle] " & _
                   "FROM tblEmployerJobs " &  _
                   "WHERE [ID]=" & Me!Employer
    End If
    Call .Requery
  End With
End Sub

[Stewart Ross]

Hi. You are referring to the wrong ID field in your WHERE clause (at line 17 above). The ID you specified is the autonumber of tblEmployerJobs instead of the employer FK. Revised version below.

It was very helpful to include the details of the two tables in your code, otherwise this would have been hard to spot - well done!

"WHERE [EmployerID]=" & Me!Employer

-Stewart

[rcollins]

Still nothing

Private Sub Employer_AfterUpdate()
  With Me![JobDescription]
    If IsNull(Me!Employer) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [JobTitle] " & _
                   "FROM tblEmployerJobs " & _
                   "WHERE [EmployerID]=" & Me!Employer
    End If
    Call .Requery
  End With
End Sub

[Stewart Ross]

Hi again. 'Still nothing' does not help me (or anyone else) at the other end of a web connection to help you! You will need to do some systematic debugging now that the original error is, presumably, cleared, because we cannot know what else could be wrong unless you tell us the state values of the variables involved.

You could set a breakpoint in your code and step through each line, using the local variable window to watch the values of the variables as you go.

You could also check what the rowsource is actually being set to by outputting its value using a messagebox, placing the following line before your End WIth:

msgbox "Rowsource = " & .rowsource

If you find that an employer ID is being provided check that it matches a row in the table. If it doesn't there is a problem with setting the employer control value.

This will give us more of a concrete nature to go on than we have at present.

-Stewart

[rcollins]

Sorry about the no info on Friday, Get braindead by the end of the week. I put the message box like you sugested and I get the value ok from the employer dropdown. But isnt the msgbox "Rowsource = " & .rowsource supposed to give me the JobDescription value? The JobDescription box still shows empty. I tried using breakpoints and am not getting any info from it. I hope this is enough for you.

[Stewart Ross]

Hi again. The messagebox should display something like this:

Rowsource = SELECT [JobTitle] FROM tblEmployerJobs WHERE [EmployerID]= 12345

What is being tested is that, firstly, the SQL is OK (as it certainly looks OK in the after-update code), and, secondly, that there is a numeric value for the employer ID. If that numeric ID corresponds to one of the records in your job description table there should be no problem with the job description combo - in which case you could manually paste the SQL code into the SQL view of the query editor to check that it does return records. If it does so in the query editor but not in your combo then the width setting of the combo column is suspect (set a value of 5cm (2in), say, to make sure you will see values in the combo).

If none of this helps you could zip a sanitised copy of your database to your next post for us to check. If that is not possible (for confidentiality or other reasons) I'd be glad to assist by checking your database on your behalf in confidence. Should that be an option you wish to explore just let me know by sending a PM and I will send my e-mail address on to you.

Cheers

Stewart

PS the rowsource for a combo box is normally a table or query, not a value list. It is the execution of the query which returns the data listed in the combo (similarly for listboxes). It is the ability to change the query on the fly which is exploited when cascading listboxes.