INNER JOIN problem

[Lestatos]

Good day , guys .
Small problem ... SQL related
I have 2 tables and one combo refreshing other combo

Table_Companies :
[Comp_ID] [ Comp_Name ]
1 Comp_a
2 Comp_b

Table_Projects :
[Project_ID] [Comp_ID] [Project_Name]
1 1 Proj_a
2 1 Proj_b
3 2 Proj_c

Combo_Sel_Project ( default row source :

 " SELECT Project_ID , Project_Name from Table_Projects " )

Combo_Sel_Company( default row source :

 "SELECT Comp_ID , 
Comp_Name from Table_Companies " )

The On_Change event for Combo_Sel_Project is indeeed my problem :

if not isnull(me.Combo_Sel_Company.column(0)) then 
me.Combo_Sel_Company.rowsource = " SELECT Comp_ID , Comp_Name "_ 
& " FROM Table_Companies INNER JOIN Table_Projects " _
& " ON Table_Companies.Comp_ID = Table_Projects.Comp_ID 
& " WHERE (((Table_Projects.Project_ID) = " & me.Combo_Sel_Project.Column(0) & " )) "
me.Combo_Sel_Company.requery

The resultat should update Combo_Sel_Company with the companies ID and NAME from Table_Companie , which are conected to the selected PROJECT in Combo_Sel_Project .
The problem is that after runing the code , nothing is listed in the Combo_Sel_Company . .... Stucked

 

[pr2-eugin]

Just a bit of clarification, should it not be vice versa? I mean select the Company and list all associated projects of that company?

Could you provide the Bound Column of the Combo_Sel_Project ? Also is Comp_ID Number type?

 

[Lestatos]

The bound column for Combo_Sel_Project is Column0 and the type of Comp_ID in Table_Projects is Number .

 

[pr2-eugin]

The bound column for Combo_Sel_Project is Column 0

You mean the First column? Any comments on my first question?

 

[Lestatos]

Yes , the first column of the combo is the bound column .
Indeed my idea is to update the Combo_Company after Combo_Project is changed , so it lists only the companies who worked on ProjectA let's say .
I thought that SQL would help , but it seems I am wrong .

 

[pr2-eugin]

Okay Try this..

If Me.Combo_Sel_Project.ListIndex <> -1 Then 
    Me.Combo_Sel_Company.rowsource = "SELECT Comp_ID , Comp_Name " & _ 
                                     "FROM Table_Companies INNER JOIN Table_Projects " & _
                                     "ON Table_Companies.Comp_ID = Table_Projects.Comp_ID " & _
                                     "WHERE Table_Projects.Project_ID = " & Me.Combo_Sel_Project & ";"
    Me.Combo_Sel_Company.Requery
Else
    MsgBox "Please select a valid Project", vbCritical
End If

 

[Lestatos]

Thanks for the answer , pr2-eugin . Now I put the code on the on_change event of combo_sel_project and when I change its selection and try to drop-down the combo-sel-company I get : " The specified field "Comp_ID" could refer to more than one table listed in the FROM clause of your SQL statement ". When I click OK , the combo_Sel_company has no fields (values ) ...

 

[pr2-eugin]

Okay. Now we are making some progress. Try moving the code into AfterUpdate, rather than using OnChange..

Private Sub Combo_Sel_Project_AfterUpdate()
    If Me.Combo_Sel_Project.ListIndex <> -1 Then 
        Me.Combo_Sel_Company.rowsource = "SELECT Table_Companies.Comp_ID , Table_Companies.Comp_Name " & _ 
                                         "FROM Table_Companies INNER JOIN Table_Projects " & _
                                         "ON Table_Companies.Comp_ID = Table_Projects.Comp_ID " & _
                                         "WHERE Table_Projects.Project_ID = " & Me.Combo_Sel_Project & ";"
        Me.Combo_Sel_Company.Requery
    Else
        MsgBox "Please select a valid Project", vbCritical
    End If
End Sub

 

[Lestatos]

Now it worked as well as expected ! Thanks a lot , pr2-eugin ! Nice day .

 

[pr2-eugin]

Glad to help.. Good Luck..