T,
1. My guess is that you have this field set up as a "lookup" field in your
main table, and although it looks like the field contains the text of the
trade, it actually contains a numeric value (as I recommended in #1). When
you open the main table and put your cursor in the Trade field, does it give
you a dropdown combo box, or is it just text?
2, Regarding the SQL for your combo box. What other fields are in the
query? If you use more than one, you will generally get this kind of
behavior. Can you copy the SQL of your query and paste it here? If you
open the query in design view, then click the View - SQL View option (this
really depends on the version of Access you are using) you can see the text
of your query.
3. Also, copy the SQL for your main query (the one where you refer to
Forms!formname!controlname) and past it here. Yes, Access will continue to
add the brackets, don't worry about that. They generally are not needed but
are required if:
a. you include a space in the name of a table or field
b. you use a reserved word as a field name. For example, novices will
frequently use the word "Date" as a field name. This is a reserved word in
Access (it is a function) and if you don't use brackets around this name,
then it can cause problems. I strongly recommend against using reserved
words as field names. You can find a thorough list of these words at:
http://allenbrowne.com/AppIssueBadWord.html
Dale
T,
1. It looks like your Trade field in the main table contains multiple,
duplicate values. If you are going to use the same set of values over and
over again, and you want to make sure your users only select the values
that
you want in that field, you should generally create a lookup table, and
store the text values in that table. In this case, you might want to
create
tblTrades with fields (TradeID-autonumber, TradeName, and Sort_Order). I
like to include the Sort_Order field so I can either sort alphabetically
or
by most frequently usage. Then, in your main table, you would store the
TradeID field rather than the text. Integers take up less space and are
quicker to index.
However, given your current table structure, I would either use a DISTINCT
or a Group By clause for your combo box, so that you only get a single
instance of each Trade value. The query would look like:
SELECT DISTINCT Trade FROM yourTable ORDER BY Trade
or
SELECT Trade FROM yourTable GROUP BY Trade
2. You didnt't indicate what the name of your form is, but you did use the
syntax: Forms![frmcbotrade]![cboTrade]
Generally, this would imply that the name of your for is frmcboTrade. I
would not normally name my form after a control on that form, I would give
the form a name that has a little more meaning. However, your syntax for
that criteria is wrong. You should be using a dot, rather than a bang
between the name of the form and the name of the control. So the syntax
for
your "query" should be something like:
SELECT Field1, Field2, Field3
FROM yourTable
WHERE [Trade] = Forms![frmcbotrade].cboTrade
HTH
Dale
Hi everyone,
I realise this is probably on here somewhere but I can't find it.
I am basically trying to run a query from a combobox selection on a
form. Here is what I have:
1. I created a Form and added a combobox that gets its value from a
field on a table the field is "trade". (This works fine except it
returns multiple trades ie. if I have 2 builders details in the table
then it returns "Builder" twice)
2. I added a command button that runs a query.
3. I created a query with all the fields I require to be displayed
including trade. - (The query looks at the same table as the
combobox.)
4. In the Criteria line of the query under trade I entered the
following:
Forms![frmcbotrade]![cbotrade]
5. I then linked the command button on the form to the query.
When I run the form I select the trade I want from the drop down
combobox then press the run query command button.
All works great except the query never displays any information.
However if I change the "Forms!" part of the criteria to "Form" and
run the query it displays a criteria input box if I then type in
"Builder" it will display all the builders?
So what I am doing wrong? I seem to be doing everything correct
according to all the forum entries I have read.
Dale,
Thanks for the help with this.
Your correct the table does contain multiple duplicate values, and I
did try use a separate table for trades some time ago but for a
different purpose. However due to the way the data is added this
proved to be more work for our administrators so I reverted back to
how it is now. The other advantage I can see is that the cbobox will
only display the trade in the system at that particular time if that
makes any sense.
1. How do I use a DISTINCT of GROUP By clause on the cbobox? I have
gone into the properties and selected Row source, then looked at the
query builder and changed the query to have a total row showing "Group
By" is this correct? However the cbo still returns multiple values.
2. With regards to the form naming I will take this on board and look
at renaming it. I have changed the Bang ! to a Dot . as you say and
removed the square brackets from the syntax as per your example,
access just puts them back. When I run the query from the form I get
the same result! The query seems to run fine but still no data
displayed.
What do I do now?
Thanks
Tony
