[RESOLVED] Problem with my query...

[DubweiserTM]

Hi everybody...

I have a query that does'nt work, maybe I have an error into it or it's not possible what I'm trying to do ??? Refer to the underlined and bold line in the code below...

I want that the strSQL query will be equal to the PrimaryKey of the value in the Combobox

Here is the code (Sorry some words are in french):

'Instanciation des variables
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmdSQL = New ADODB.Command
Set prmSQL = New ADODB.Parameter
 
'Connection Database
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & adoSource
cn.Open
 
'Création de la requête
strSQL = "SELECT Desc_F FROM RevType WHERE TypePK in (SELECT TypePK FROM Revetement WHERE CategPK = ?)"
 
'Configurer la commande
cmdSQL.CommandText = strSQL
cmdSQL.CommandType = adCmdText
 
'Création du paramètre
Set prmSQL = cmdSQL.CreateParameter("Whatever", adVarChar, adParamInput, 255, [B][U]"SELECT CategPK FROM RevCategorie WHERE Desc_F = " & cboFexRev(0).Text[/U][/B])
cmdSQL.Parameters.Append prmSQL
 
'Exécuter la requête paramétrée
Set cmdSQL.ActiveConnection = cn
Set rs = cmdSQL.Execute
 
'Vider le ComboBox
adoCombo.Clear
 
'Positionner sur le premier recordset
rs.MoveFirst
        'Ajouter Items ComboBox
        Do Until rs.EOF
                adoCombo.AddItem rs.Fields("Desc_F").Value
                'Déplace curseur sur prochain recordset
                rs.MoveNext
        Loop
 
'Fermeture de la connection ADO
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Thanks in advance !

[brucevde]

Is the CategPK field numeric? If yes, set the ComboBox.ItemData = CategPK when you load cboFexRev(0).
Assuming the recordset to load the combobox has these the 2 fields CategPK and Desc_F

Do Until rs.EOF
    cboFexRev(0).AddItem rs.Fields("Desc_F").Value
    cboFexRev(0).ItemData(cboFexRev(0).NewIndex) = rs.Fields("CategPK").Value
    rs.MoveNext
Loop

Then use this Parameter statement

Set prmSQL = cmdSQL.CreateParameter("Whatever", adInteger, adParamInput,, cboFexRev(0).ItemData(cboFexRev(0).ListIndex))

If CategPK is not numeric, try this SQL Query

strSQL = "SELECT RT.Desc_F FROM RevCategorie RC " _
& "Inner Join Revetement R On R.CategPK = RC.CategPK " _ 
& "Inner Join RevType RT On RT.TypePK = R.TypePK " _ 
& "Where RC.Desc_F = ?"
 
Set prmSQL = cmdSQL.CreateParameter("Whatever", adVarChar, adParamInput, 255, cboFexRev(0).Text)

Note: the syntax of the query above for use in Access is probably incorrect.

[DubweiserTM]

Is the CategPK field numeric? If yes, set the ComboBox.ItemData = CategPK when you load cboFexRev(0).

Yes CategPK is numeric...

Assuming the recordset to load the combobox has these the 2 fields CategPK and Desc_F

I use the CategPK of cboFexRev(0).text to fill another combobox cboFexRev(1)... I will add this part of the code(.itemdata) at the good place...

Thanks !

[DubweiserTM]

I will add this part of the code(.itemdata) at the good place...

Finally it's not easy, because I fill all the comboboxes with a Function and in some table there is no CategPF...

Is there another way ???

Thanks in advance !

[si_the_geek]

You could use something similar to the sub I wrote for the Database FAQ's (here).. pass parameters to it to specify field names, and preferably pass the recordset rather than build it in the sub as I did (as the method above is better, but a little complex for the FAQ).

[DubweiserTM]

Your code will be helpful ! Thanks !

[DubweiserTM]

Hi !

Is someone could explain to me in details the signification of this query.

Usually I use very simple query but this one is complicated for me and I want to understand...

strSQL = "SELECT Desc_F FROM RevType WHERE TypePK in (SELECT TypePK FROM Revetement WHERE CategPK = ?)"

Thanks in advance !

[si_the_geek]

I presume the highlighted parts are what concern you, so:

Instead of doing a "SELECT *" (which returns all fields from the row) it is more efficient and accurate to specify just the fields you want, and in the order you want them, eg: "SELECT Desc_F, AnotherField, AndAnother"

The "In" is a bit like an = , except it checks against multiple values. A simpler version would be "WHERE TypePK in (1, 2, 3)", which would return all rows where TypePK is 1, 2, or 3. In this case, instead of specifying the values, they come from the results of another query (all TypePK's in Revetement that have a specified CategPK).

[DubweiserTM]

I presume the highlighted parts are what concern you, so:

Instead of doing a "SELECT *" (which returns all fields from the row) it is more efficient and accurate to specify just the fields you want, and in the order you want them, eg: "SELECT Desc_F, AnotherField, AndAnother"

The "In" is a bit like an = , except it checks against multiple values. A simpler version would be "WHERE TypePK in (1, 2, 3)", which would return all rows where TypePK is 1, 2, or 3. In this case, instead of specifying the values, they come from the results of another query (all TypePK's in Revetement that have a specified CategPK).

Merci ! (Thanks !)