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!

[techgnome]

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)

Line 35 opens the recordset ... and then you open it AGAIN in the next line. That doesn't work. If you open a door... can you open it again immediately? No... what do you have to do before you can open it a second time? Close it. Same with the recordsets .. either close it, in which case why was it open in the first place? Or use a different recordset for the second operation.


-tg

[XavierCG]

I used a different recordset to try it out.
I added :

11            Dim rstLocalisation as ADODB.Recordset
36            Call rstLocalisation.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)

It still doesn't work.
Error number 91 Description: Variable object or variable bloc With not defined

And I can't see the manufacturer in the combobox.

Thanks

[techgnome]

Did you create a new instance? I don't mean the dim line ... I mean a line like this:

Set rstFabricant = New ADODB.Recordset

-tg

[XavierCG]

Did you create a new instance? I don't mean the dim line ... I mean a line like this:

Set rstFabricant = New ADODB.Recordset

-tg

Now theres no more errors but the localisation doesn't stay once you click one and the manufacturer combobox doesn't change when selecting a localisation. I'm not sure why it is not working like expected and for the other comboxbox it work just fine.

Thanks.

[Zvoni]

5588931[/URL]]Now theres no more errors but the localisation doesn't stay once you click one and the manufacturer combobox doesn't change when selecting a localisation. I'm not sure why it is not working like expected and for the other comboxbox it work just fine.

Thanks.

besides the fact that sFabricant is nowhere set before you open that recordset?

[XavierCG]

besides the fact that sFabricant is nowhere set before you open that recordset?

You mean like line 25 but for sFabricant?

I have put one there like this:

sFabricant = Replace(cmbFabricant.Text, "'", "''")

But it doesn't change anything.

Thanks.

[Zvoni]

5588939[/URL]]You mean like line 25 but for sFabricant?

I have put one there like this:

sFabricant = Replace(cmbFabricant.Text, "'", "''")

But it doesn't change anything.

Thanks.

because the LIKE-Operator without wildcards acts like equality?

[XavierCG]

Yes that's what I'm thinking. That is what is told here I guess?