Cascading Comboboxes "Enter Parameter Value" Pop-up

[Prevost]

Hi There,
I have a form with two combo boxes. The First is named cboxNoSpeeds (and the user can choose 1 or 2 for the number of speeds value) and the second is named cboxHP1 (and the intent is for the user to be able to select all the horsepower values that have the corresponding speed value. Now I have looked for this existing question and I think I have the answer almost correct. Here is the code that I have:

Option Compare Database
Option Explicit

Private Sub cboxNoSpeeds_AfterUpdate()

    Me.CBoxHP1.RowSource = "SELECT HP1 FROM tblWoundFrameInfo WHERE Speeds = Me.CBoxNoSpeeds ORDER BY HP1"
    Me.CBoxHP1 = Me.CBoxHP1.ItemData(0)

End Sub

But whenever I pick a speed, I get the following pop up window (that I do not want). Thanks for any help!

 

[ranman256]

the cbox needs to be OUTSIDE of the quotes to resolve the value. (the ME is not needed)

cboxHP1.RowSource = "SELECT HP1 FROM tblWoundFrameInfo WHERE Speeds = " & CBoxNoSpeeds & " ORDER BY HP1"

 

[Prevost]

Thanks again ranman. It is working. So access recognizes the SQL statement SELECT, FROM, WHERE and ORDER even though they are within the quotations? Also, I added SELECT DISTINCT to remove duplicates displayed in the combo box. Does this also organize the values in ascending order or how do I control that from within that SQL statement?

Also, is it by default that Access orders 100 below 2 (so in my DISTINCT list, it is ascending in value, but only with respect to the first number character, which is why 100 is shown before 2).

 

[Joe4]

So access recognizes the SQL statement SELECT, FROM, WHERE and ORDER even though they are within the quotations?

It is important to understand what is going on there. You are building a String to place in your Data Source property. Everything between quotes will appear as literal text, exactly how you type it in. However, if you have variables that you want to return whatever they are set to, they need to be outside the quotes. Otherwise, it will return just what you typed (the name of the variable/object), and not what it is equal to (its value).

For example, let's say that you had a variable called "age" that calculated your age. Let's say it takes the current date minus your birth date. If you wanted to return that in a message box, you would use this:

MsgBox "I am " & age & " years old."

If you tried this:

MsgBox "I am age years old."

It would return "I am age years old."

Make sense?

Also, is it by default that Access orders 100 below 2 (so in my DISTINCT list, it is ascending in value, but only with respect to the first number character, which is why 100 is shown before 2).

If the field is stored as a Number, 2 comes before 100 (numerical order).
If the field is stored as a String (Text), 100 comes before 2 (alphabetic order).

 

[Prevost]

That makes sense. Thank you! I have numbers with decimal places so I am going to try and figure out how to keep those decimal places and reformat the data type to number.