Query based form not working

[Tewhano]

I recently upgraded to Office 2010 from 2003. I have a database that I created to catalog my coin collection. I have a problem which perplexes me as its behavior defies logic.



I have two forms that access the tables to add or edit my collection. One edits the entire collection the other edits the collection by storage (e.g.: Quarters in an album dated 1932 to 1964). I also have reports that do the same, one reports the entire collection the other by storage. The ‘edit by storage’ form is preceded by a ‘select storage’ form that I select the storage I want to edit.

Here is the problem: I recently added 83 coins via an append query. In the collection form I can edit these coins but not in the’ edit by storage’ form, nothing appears. In the reports I can view the coins by storage. The form code to select the storage for edit or view a report is the same.

If I open the table I can filter by the ID for that storage but not if I open a query and try to filter by that storage. I created a query to test this by returning all the coins in the database and using the filter option on the tool bar to view the coins in that storage. I got no records.
So the coins are there in table but I can’t access them via the edit by storage form (query based). As a side note I deleted the records and added a few of them manually in the edit all coins form and the same thing happens. Also, I can filter any other stoarge ID and see the coins just not this new one I just added since upgrading.

Any clues as to why this is happening?

[Tewhano]

This happens to me all the time; I’ll wrestle with a problem for weeks with no solution and then post my problem on a forum. After I spell out my problem and post it the wheels in my head start turning and I try a few more approaches and finally solve the problem.


I started thinking about this and it came to me that it can’t be just any query that has this problem (since the query for the report works) but just the query that controls the edit form’s data. In the beginning I copied the storage edit query to make the storage report query so it never dawned on me that there was any differences between the two except one controls data for a form the other a report. So I looked at both and realize that some time ago I added another table to the edit query and it was the relationship of this table that caused the problem. I had the relationship going the wrong way (it’s been like this for at least a year now). My append query didn’t append the ID pointer to that table so when I ran the query that relationship said only show records that are tied to this table. I never noticed this before because in the form I always put data in that table.

Thanks for putting up with my babble.

PS: How do I mark this thread as solved?