Help with excel vba form sql cascade comboboxes

[valossa515]

Good afternoon, I'm having trouble getting a SQL string that integrates into 2 different comboboxes by pulling data from access, I'm falling into an infinite loop and I'm not able to solve it, can anyone help me?. Here is the code

RS.Open "SELECT subcriterio_id, Subcriterio FROM tb_subcriterio WHERE criterio_id = " & cmb_criterio.Text & " ORDER BY Subcriterio", connection, adOpenKeyset, adLockReadOnly
        If RS.RecordCount > 0 Then
            Do Until RS.EOF
                cmb_subcriterio.AddItem RS!subcriterio_id, Subcriterio
                RS.MoveNext
            Loop
        End If

criterio.PNG
subcriterios.PNG
design.PNG
design2.PNG
rela??es.PNG

[mogul]

I think you want this:

sql = ""
sql = sql & "SELECT subcriterio_id, " & vbCrLf
sql = sql & "       Subcriterio " & vbCrLf
sql = sql & "FROM   tb_subcriterio AS sc " & vbCrLf
sql = sql & "       INNER JOIN tb_criterios c " & vbCrLf
sql = sql & "               ON sc.criterio_id = " & cmb_criterio.Text & vbCrLf
sql = sql & "ORDER  BY Subcriterio"

[valossa515]

well thanks for your reply but i want to change this part "SELECT subcriterio_id, Subcriterio FROM tb_subcriterio WHERE criterio_id = " & cmb_criterio.Text & " ORDER BY Subcriterio" to mke it work i think this sql isn't correct cause when i execute it in the excel form i fall into a inifnite loop

[mogul]

Updated SQL. What happens when execute in Access? It needs to work in Access first.

For Access

SELECT subcriterio_id,
       Subcriterio
FROM   tb_subcriterio AS sc
       INNER JOIN tb_criterios c
               ON sc.criterio_id = c.criterio_id
       WHERE sc.criterio_id = 1 
ORDER  BY Subcriterio

For VBA

sql = ""
sql = sql & "SELECT subcriterio_id, " & vbCrLf
sql = sql & "       Subcriterio " & vbCrLf
sql = sql & "FROM   tb_subcriterio AS sc " & vbCrLf
sql = sql & "       INNER JOIN tb_criterios c " & vbCrLf
sql = sql & "               ON sc.criterio_id = c.criterio_id " & vbCrLf
sql = sql & "WHERE  sc.criterio_id = 1 " & vbCrLf
sql = sql & "ORDER  BY Subcriterio"

Debug.Print sql

[valossa515]

ok WHERE sc.criterio_id = 1 " & vbCrL works great now how to change it to get my combobox text? cause when i try to replace 1 by my cmb_criterio.text the script breaks
I tried this but not work
sql = sql & "WHERE sc.criterio_id = " & cmb_criterio.Text & " " & vbCrLf

this is my excel form

cmb.PNG

[mogul]

Add a Debug.Print sql after the last query statement and see the value of sql in the Immediate window.

sql = sql & "WHERE sc.criterio_id = " & cmb_criterio.Text & " " & vbCrLf

It should be

sql = sql & "WHERE sc.criterio_id = " & cmb_criterio & vbCrLf

[alansidman]

crossposted: https://www.excelforum.com/excel-pro...omboboxes.html

Rule 03: Cross-posting Without Telling Us

Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this.

I have added the crosspost reference for you today. Please comply with this and all our rules in the future