MS Access query returning insufficient records, but only for certain constraints

1
votes

In Access 2013, I am trying to create a set of cascading combo boxes in a form. Should be simple enough, even using only macros.

The query for the second combo box isn't returning all relevant records, but only for certain values in the first combo box. Instead, it returns only the first record.

Example dummy database:

Title    Volume
Furniture 1
Furniture 2
Furniture 3
Sapiens   2000-1
Sapiens   2000-2
Sapiens   Feb-2000

AfterUpdate in the first box calls requery on both.

The query is

SELECT Updates.Vol, Updates.Title
FROM Updates
WHERE (((Updates.Title)=[Forms]![Form1]![TitleEdit]))
ORDER BY Updates.Vol DESC;

When the first combo box is set to "Furniture" the second populates with 1,2,3 as it should. When I set the first combo box to "Sapiens" the second one contains only "2000-1".

The actual DB is somewhat larger and has more entries, which either work or give only the first entry as above.

1
sqlms-accesscombobox
Are "Furniture" and "Sapiens" the actual values that work resp. don't work? Or made up examples? – Andre
Examples. If you think the full titles might be involved, "FEDERAL INCOME TAXATION OF CORPORATIONS AND SHAREHOLDERS", "FEDERAL TAXES AFFECTING REAL ESTATE" are examples of failures. "ECKSTROM'S LICENSING IN FOREIGN AND DOMESTIC OPERATIONS: Forms", "ACCOUTING ARTICLES" are examples of records that return properly. – A.j.
I should mention those titles are selected by dropdown, and are required and unique in the table they originate in. – A.j.

1 Answers

0
votes

The problem ended up being lookup fields. Lookup entries show the results of the lookup, like a title, but as far as filters and searches are concerned they contain only an index number. Because of the way this database was imported, for several titles the first entry contained a traditional lookup value, while subsequent ones were overwritten with the actual title. In short, copy and paste your data. If the pasted data contains something other than what you expected, searches and filters won't work properly.