vb6 INNER JOIN ADODB.recordset

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.

Code:

---

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:Attachment 186434



GRB_InventaireElec:Attachment 186435

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!

Code:

---

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

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

Code:

---

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

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

Code:

---

Set rstFabricant = New ADODB.Recordset

---

-tg

Quote:

---

Originally Posted by techgnome

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

Code:

---

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.

Quote:

---

Originally Posted by XavierCG;[URL="tel:5588931"

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?

Quote:

---

Originally Posted by Zvoni

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:

Code:

---

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

---

But it doesn't change anything.

Thanks.

Quote:

---

Originally Posted by XavierCG;[URL="tel:5588939"

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

I have put one there like this:

Code:

---

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

---

But it doesn't change anything.

Thanks.

---

because the LIKE-Operator without wildcards acts like equality?

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