Use Combo Box to set up query criteria (1 Viewer)

Jose · Nov 3, 2010

Hello,
I have created a query with fund prices and fund prices variances and I am using a combo box to set up certain criteria in the query. The idea is that a user can select to see funds whose prices have varied 0%, 0.5% etc... This is my query:

SELECT FundPrices_Qry00.Date, FundPrices_Qry00.Fund_Name, FundPrices_Qry00.MCH_Code, FundPrices_Qry00.Variance
FROM FundPrices_Qry00
WHERE (((FundPrices_Qry00.Variance)>[Forms]![TrackerForm]![CboTolerance]))
ORDER BY FundPrices_Qry00.Variance DESC;

This is my combo box contained in a form:

The property sheet of the combo box shows the following information

Row Source: "0%";0;"0.10%";0.1;"0.50%";0.5
Bound Column 1
Limit to List Yes
Allow Value List Edits No
Inherit value list No
Show only row source No

In the format data sheet properties I have

Decimal Places 2
Visibles Yes
Column Count 2
Column Widths 0.523cm;0cm
Column heads No
List Width 0.753cm
With 3

The problem I have is when I run the query I dont get the correct results. For instance when I choose 0.50 in my form the query returns values below 0.5. Can someone advice what I am doing wrong, please? I think it might be something related to the usage of logical operators..

Thanks

Jo

SpentGeezer · Nov 4, 2010

Me thinks:

You may need to use combobox.text in your code as just combobox will return the row selected (e.g row 2, combobox = 2). Using combobox.text will also require a setfocus. Perhaps on the form you need the combobox onchange to update another control that is easier to use in your SQL statement?

Sorry post was a bit rushed, hope it makes sense???

AccessBlaster · Nov 4, 2010

Jose,

Is this what your trying to do?

Jose · Nov 4, 2010

AccessBlaster said:
Jose,

Is this what your trying to do?

Nope sorry. I am trying to use a combobox to pass criteria to a query.. nothing to do with reports.. sorry and thank for your help

J

Jose · Nov 4, 2010

SpentGeezer said:
Me thinks:

You may need to use combobox.text in your code as just combobox will return the row selected (e.g row 2, combobox = 2). Using combobox.text will also require a setfocus. Perhaps on the form you need the combobox onchange to update another control that is easier to use in your SQL statement?

Sorry post was a bit rushed, hope it makes sense???

I am really sorry but I dont know too much VBA. How can i get a combobox.text? and what is a setfocus?

Thanks for your help

J

SpentGeezer · Nov 4, 2010

Thanks Bob!! I never knew about the .column property of comboboxes, how sweet it is.

Jose · Nov 10, 2010

Bob:

Thanks for your help. I am using a value list for my combobox as you can see below.

Basically the idea is to choose the text ( Example "0.5%" or other ) which is the first column but I would like to pass as criteria the numbers ( Example 0.5)which are on the second column. This second column is the hidden one. So the query gives values over 0.5.

I thought the bound column property should be 1 since the first column where the text is would be zero. am i correct?

I also thought the whole problem was that I was using a conditional operator for the query criteria and the information from the combobox. (>[Forms]![FormName]![Comboboxname]

The property sheet of the combo box shows the following information

Row Source: "0%";0;"0.10%";0.1;"0.50%";0.5
Bound Column 1
Limit to List Yes
Allow Value List Edits No
Inherit value list No
Show only row source No

In the format data sheet properties I have

Decimal Places 2
Visibles Yes
Column Count 2
Column Widths 0.523cm;0cm
Column heads No
List Width 0.753cm
With 3

Do you think the above in my combobox is correct?

Thanks for your help and sorry if my questions seem to be very easy but I am newbie.

Jose

AC5FF · Apr 19, 2016

Hope this works - quite an older thread but I am having some difficulty in getting parts of this to work.

Essentially I have set this up the same way, and if I open the query directly or the form made from the query, it works like a champ. But, my combo box is on a form and the subform (from my query) will not ever update and I can't figure that one out.

Open the subform on its own - great, but that's not how I hope to have this working....

Thx

boblarson said:
Jose:

Don't bother with .Text as you have to have the focus on that control to use that.

Combos have multiple columns available IF they are set up to do so. You can have a query in the combo box's row source which can have up to several columns. Let's say you have a combo box which has an ID and the text description. The column count property of the combo would be set to 2 and usually the column widths would be set to something like 0";2" which would hide the first column (usually the ID) and display the text. But the bound column property would be 1 so it is storing the ID but displaying the text.

So, in your query you need to be able to reference the bound column of the combo box. If you need to keep the bound column to 1 but need to use the text from column 2 as criteria in the query you need to do it this way:

1. You put a hidden text box on the form (just set the visible property to NO).

2. Let's say you name that hidden text box txtVal and so in your combo box's After Update event (in the VBA window, not in the properties dialog - and you can find how to get to the VBA window by looking at this on my website) you would put this code:

Code:

Me.txtVal = Me.YourComboBoxNameHere.Column(1)

In that context the column numbers are zero based so the first column would be Column(0) and the second Column(1) etc.

Once you have that code in the combo box's After Update event, you can then reference the text box in the criteria of your query by using:
[Forms]![YourFormNameHere]![txtVal]

I hope that helps.

AC5FF · Apr 19, 2016

Just realized. If I select an item in the combo box, go into design view for the form, then back to form view, the data will update in my subform.

Thought I would update my above question with this information - maybe it's a clue I'm not seeing?

AC5FF · Apr 20, 2016

Again - this is an old thread - but I wanted to post a solution to my problem that I was able to come up with today...

The clue was that if I went into design view and back my subforms would update accordingly. So, where Bob said above to add a line of code to transfer the combo box value over to a text box value worked to run the query, the form had to refresh.
This code fixed it for me:

Private Sub AccountSelect_AfterUpdate()

Me.AccountSelection = Me.AccountSelect.Column(0)
Me.Refresh

End Sub

Use Combo Box to set up query criteria (1 Viewer)

Jose

Registered User.

SpentGeezer

Pure Noobism

AccessBlaster

Registered User.

Attachments

Jose

Registered User.

Jose

Registered User.

SpentGeezer

Pure Noobism

Jose

Registered User.

AC5FF

Registered User.

AC5FF

Registered User.

AC5FF

Registered User.

Similar threads

Users who are viewing this thread