passing combobox value into sql query MS ACCESS

Question

i have a combobox on a form

i want the text of the combobox to be passed into a query.

my query is:

select..from..where something=[Forms]![Enter Data]![comboCup]

the form name is enter data and the combobox name is combocup. should i do:

[Forms]![Enter Data]![comboCup]![text]

or

[Forms]![Enter Data]![comboCup]![value]

??

Answer 1

If you'll work it in the Form module, you can do something like this (pseudo code only):

Event comboCup_afterUpdate()
    strCup = Me!comboCup
    strSQL = "SELECT ... etc ... ='" & strCup & "'"
End Sub

If in a different module, then still use variables as shown above; in that case however, your syntax for identifying the field in the form needs a lot of work. i can tell you more about that if this all makes sense so far.

Answer 2

Neither. Text is only available when the control has the focus. The value of comboCup is the bound column. Ensure that your query is looking for that value, otherwise you will need to refer to the column property of the combo.

Answer 3

This is yet another of your myriad questions that could be answered by spending a few minutes reviewing the Access help files or Googling for a bit. I really wish you would stop posting these on StackOverflow. I don't see that they serve any purpose but to allow people to up their reputation, which is the worst possible motivation for posting on SO, in my opinion. And 75% of your questions aren't even programming questions -- they are more appropriate to SuperUser.com, in my opinion.

Please stop.

In any event, I'll answer your question:

You should use [Forms]![Enter Data]![comboCup].

As @Remou has said, the .Text property of an Access control is available only when the control has the focus.

The .Value property is redundant, as it's the default property of all Access controls, so these two are equivalent:

  [Forms]![Enter Data]![comboCup]
  [Forms]![Enter Data]![comboCup].Value

(note also that properties like .Text and .Value are separated by the dot operator and not the bang, which delineates collections)

One issue that can be of concern is if you want to use the value of the combo box in the SELECT statement of an APPEND query. In that case, you would be advised to declare the combo box as a parameter in your saved query. If you do not, it can cause the row to not be inserted, whereas if you declare the parameter, it will resolve to the Null that is the value in the referenced combo box.