SELECT...WHERE from SQL to VBA (1 Viewer)

[rafa]

Dear all,

a simply simply question...that is taking me hours!
This is the SQL sentence from a Query in a combo (which is filtered for another combo, nothing special, letting only the values with the first letter in that combo). How can I write it for VBA???? (Please, indicate all the ", I usually make a big mesh with it )

SELECT TABLE.IDName, TABLE.Surname
FROM TABLE
WHERE (((TABLE.Surname) Like [Forms]![Nameform]![Combo14] & "*"))
ORDER BY TABLE.Surname;

The problem is in the bold text; I think)

Among all the attempts I tried, this is one of them:

"SELECT TABLE.IDName, TABLE.Surname FROM TABLE" & _
"WHERE TABLE.Surname= " & Me.Combo14.Value & "*" _
"ORDER BY TABLE.Surname" & ";"

Thank you very much in advance.

Rafa

 

[Ranman256]

Your 1st SQL should work, with these conditions.....
If you want ONLY the first letter,then the combo can only have single letters in it. Otherwise
It filters the whole word.

Combo14 is the name of combo 1.
Combo14 has only 1 column

 

[rafa]

Hi Ranman256,

thanks for your quick response,

the SQL works, I made it with the Query builder and then I copied from the SQL view. This is ok.

The problem is rewriting exactly the same for VBA. I don't know how to rewrite the bold part.

the second combo has a list like: A112, B324, B114, etc.

Yes, Combo14 is the name of combo 1 and it has has only 1 column (with a list of letters: A, C, D...)

 

[Ranman256]

You don't need vba. The query works as is.
Just remember to refresh cbo2 AFTER picking cbo1.

Cbo2.rowsource is your query above.
Then when user picks, refresh....

sub cbo1_afterupdate()
Cbo2.requery
End sub

 

[rafa]

Sorry, but yes, I need it (I just posted the key problem, this is part of something bigger).

Anyway, could you write that SQL in VBA? Is it possible?

 

[Minty]

Assuming Surname is text then you need to escape the text with quotation marks . Also lose the .value on the end - it is the default property and can cause problems on certain controls.

MySql = "SELECT TABLE.IDName, TABLE.Surname FROM TABLE " & _
"WHERE TABLE.Surname= [COLOR="Red"]'[/COLOR]" & Me.Combo14 & "*[COLOR="red"]'[/COLOR] " _
"ORDER BY TABLE.Surname ;"
Debug.Print MySql  [COLOR="Green"]' Remove this once you have it working [/COLOR]

Note I've added spaces to the end of you code lines

 

[rafa]

Hi Minty, thanks , but it does not work. This is what I wrote:

MySql = "SELECT TABLE.IDName, TABLE.Surname FROM TABLE " & _
"WHERE TABLE.Surname = ' " & Me.Combo14 & " * ' " & _
"ORDER BY TABLE.Surname" & ";"

And then, what I need is to change the RowSource of combo 2:

Me.Combo2.RowSource = MySql

I will explain better the context. Two combos in a form: Combo14 with list of letters, Combo 2 with list: A123, B143, A155, C333, etc. Combo14 just acts like a filter for Combo2. Before some actions in the database, RowSource for Combo2 is the SQL I wrote previously, and after I have to change it with the VBA.

Many thanks again!

 

[rafa]

In the Immediate Window:

SELECT TABLE.IDName, TABLE.Surname FROM TABLE WHERE TABLE.Surname = '*' ORDER BY TABLE.Surname;

 

[Minty]

You have added extra spaces between the quotes and the wildcard characters. So you record source will only pull in Surnames like

" A123 *" which I'm pretty certain won't match anything.

 

[rafa]

Sorry, my fault when copying. Actually there are no space:

MySql = "SELECT TABLE.IDName, TABLE.Surname FROM TABLE " & _
"WHERE TABLE.Surname = '" & Me.Combo148 & "*' " & _
"ORDER BY TABLE.Surname" & ";"

In summary,

If Combo14 is empty: Combo2: A123, A145, B222, etc.
If Combo 14 has, e.g: "B": Combo 2 is: B222.

 

[Minty]

AH wait you need Like because of the wildcard - apologies poor reading skills.

MySql = "SELECT TABLE.IDName, TABLE.Surname FROM TABLE " & _
"WHERE TABLE.Surname [COLOR="Red"]LIKE [/COLOR]'" & Me.Combo148 & "*' " & _
"ORDER BY TABLE.Surname ;"

 

[Ranman256]

SSql= "selECT TABLE.IDName, TABLE.Surname FROM TABLE
WHERE (((TABLE.Surname) Like " & [Forms]![Nameform]![Combo14] & "'*'))
ORDER BY TABLE.Surname;"

Cbo2.rowsource = sSql

 

[rafa]

OK!! The code works great, thanks! Now the SQL sentence is correct! THANKS!

But now I have a different problem. This code will be in a buttom in the Form, and when I click the code starts, but the combo 14 is empty (which is right); so combo2 is empty too.

If before clicking in the buttom, I select a letter, e.g, B, in combo 14, combo 2 shows the right selection: B333, B123...but if I select another letter, combo 2 does not change, it still shows B333, etc.

So, maybe adding Combo Requery or refresh...

 

[Minty]

Only change your combo2 (Please rename it to something meaninful !) on the After_Update event of Combo14. I suspect you have it on the click or change event.

 

[rafa]

Sorry, in the After_Update event of Combo14 should I put what? Refresh Combo2?

All the code is in the Click event in one Button in the same Form.

The RowSource of combo2 is in a simple query (the previous SQL). After some actions in the database (too long to explain), the rowsource has been changed, precisely to show only some of the values in the list. Then it´s time to come back to the same rowsource, with VBA. I hope this is understandable (at least a little)

And yes, I am a disaster naming things in Access!

Thank you for your time!

 

[Minty]

Normally you would set the rowsource for Combo2 after updating Combo14. You don't need a button to do that, in fact it could be confusing for an end user.

 

[rafa]

Ok, the rowsource for Combo 2 is in AfterUpdate in Combo 14. Now it works!
This part is solved, thank you very much!! Fast and straight to the problem.