Query criteria based on combo box in form not working!

Ralph

Registered User.
Local time
Today, 09:35
Joined
Mar 13, 2013
Messages
16
Hi all,

Apologies if this is super-simple, but:

I have a query that I returns a list of results as expected.. until...

I add a criteria to a field as below:

=[Forms]![Guardian Termination Form]![Combo0]

which makes the query return no results even though the form in question is displaying the correct info?

Any pointers?
:banghead:
 
My guess is you are using lookups in your table which hides the true situation.

Remove the lookup from your table and all will become clear.

If this does not solve the problem, please post the rowsource to your combobox plus the bound column value, column count and column width properties
 
Will look at your first pointer first!!
 
The query (that i want to alter using a criterion selected in a combo box on a form) gets its information from two separate tables and one other query. Both the tables in question use lookups. If I remove them, wont the whole world fall apart?
 
No - lookups in tables are a bad thing:). You may have to make some small changes to forms - which is where lookups should be.

If you are not sure, take a copy of the db and give it a go.

There are three things which I believe cause most of the debugging time wasting - lookups in tables, spaces in names and use of reserved words.
 
Ah!

I'm probably guilty on all three counts.

Almost all of my tables (25 or so) have lookups in them... as in a person (who is a record in the persons table) is linked to a building (a record in the buildings table).
 
Also - I note that if i don't open the form (and the query asks me to manually input a guardian name) everything works just fine?

Does this make things worse or better?
 
I also use the lookups in my tables.
But always I use for the field name the name of the foreign key (FK).
So, if I see a field in a table with the name ID_Customer I know that in that field I have a number (long) even I see a name (string / text).

Some times is needed to lookup two or more times at the same field from the same table (tblCustomers in my example).
In this situations I name my fields like: ID_Customer_Field1 , ID_Customer_Field2 etc.

I know: Is not the best practice but is easy to use.
 
Here is a link to reserved words

http://office.microsoft.com/en-gb/a...7-reserved-words-and-symbols-HA010030643.aspx

most common 'uses' I have come across are name, date, assistant, desc, description, counter, group, index, time, timestamp, value, year, property.

If you have already created your forms, the lookups will have been copied across to automatically create a combo box, to remove the lookup from the table now will not affect your form.

The main area will be queries where, using your example, you will be seeing the name of the building when what is actually stored is the ID to the building - so the recordsource to the combobox will be something like

Code:
SELECT ID, BuildingName FROM TblBuildings
So in your original post because you are seeing the name you think that is what you are looking up, when in fact you are comparing ID to name - which returns no records - hope that makes sense!

I would expect a quick fix would be to change the bound column from 1 to 2 in your form or change your query to

Code:
=[Forms]![Guardian Termination Form]![Combo0].Column(1)

Note, in VBA & SQL, the first column is 0 but in the combo properties the first column is 1


But better to get rid of those lookups!
 
Also - I note that if i don't open the form (and the query asks me to manually input a guardian name) everything works just fine?
That proves my point:)
 
Thanks CJ_London.

I'm on it! (After lunch obviously!)
 

Users who are viewing this thread

Back
Top Bottom