vb6 INNER JOIN ADODB.recordset

[XavierCG]

Hello,

I'm trying to join 2 combobox with Microsoft Access database. With this code it give me this error message at line 36: Error number 3705 Description: This operation is not authorised if the object is open.

Public Sub RemplirComboFabricant()

5       On Error GoTo AfficherErreur

        'Rempli le combo des fabricants
10      Dim rstFabricant As ADODB.Recordset
15      Dim sCategorie   As String
20      Dim iCompteur    As Integer
21      Dim sFabricant   As String
  
25      sCategorie = Replace(cmbCategorie.Text, "'", "''")
  
30      Set rstFabricant = New ADODB.Recordset

35      Call rstFabricant.Open("SELECT DISTINCT FABRICANT FROM GRB_CatalogueElec WHERE CATEGORIE = '" & sCategorie & "' ORDER BY FABRICANT", g_connData, adOpenDynamic, adLockOptimistic)
36      Call rstFabricant.Open("SELECT * FROM GRB_CatalogueElec INNER JOIN GRB_InventaireElec ON GRB_CatalogueElec.FABRICANT=GRB_InventaireElec.Localisation WHERE GRB_CatalogueElec.FABRICANT LIKE '" & sFabricant & "'  ORDER BY FABRICANT", g_connData, adOpenDynamic, adLockOptimistic)

        'Il faut vider le combo avant de le remplir
40      Call cmbFabricant.Clear
41      sChoisirTous = ""
      'on ajoute la possibilité de choisir tout les fabricants
42      Call cmbFabricant.AddItem("-- CHOISIR TOUS --")
43        If Not rstFabricant.EOF Then
44          rstFabricant.MoveFirst
          End If
        'Tant que ce n'est pas la fin des enregistrements
        
45      Do While Not rstFabricant.EOF
          'Si l'élément n'est pas null
          
50        If Not IsNull(rstFabricant.Fields("Fabricant")) Then
            
            'on l'ajoute
55          Call cmbFabricant.AddItem(Trim(rstFabricant.Fields("FABRICANT")))
            If sChoisirTous = "" Then
                sChoisirTous = " AND (FABRICANT = '" & Trim(rstFabricant.Fields("FABRICANT")) & "'"
            Else
56              sChoisirTous = sChoisirTous + " OR FABRICANT = '" & Trim(rstFabricant.Fields("FABRICANT")) & "'"
            End If
60        End If
    
65        Call rstFabricant.MoveNext
            
70      Loop
        
        sChoisirTous = sChoisirTous + ")"
        
75      Call rstFabricant.Close
80      Set rstFabricant = Nothing
        
        'Si le combo n'est pas vide, on sélectionne le premier élément
85      If cmbFabricant.ListCount > 0 Then
            
90        If m_sSelectFabricant <> vbNullString Then
95          For iCompteur = 0 To cmbFabricant.ListCount - 1
                
100           If UCase(cmbFabricant.LIST(iCompteur)) = UCase(m_sSelectFabricant) Then
105             cmbFabricant.ListIndex = iCompteur
                
110             m_sSelectFabricant = ""
                
115             Exit For
120           End If
125         Next
            
130       Else
            
135         cmbFabricant.ListIndex = 0
            
140       End If
145     Else
            
150       Call cmbNoItem.Clear
155       Call cmbDescriptionFR.Clear
160     End If
        
165     Exit Sub

AfficherErreur:

170     Call AfficherErreur("frmCatalogueElec", "RemplirComboFabricant", Err, Erl)
End Sub

The databases from Access are;

GRB_CatalogueElec:



GRB_InventaireElec:

Is my syntaxe correct or it's something else? I don't know. What I want it to do is when you select a localisation it will automaticaly select fill in the possible maker(fabricant)/manufacturer(manufacturier) linked with that localisation from the Access database.

Thanks!