Need help figuring out why Access can't find the form. (1 Viewer)

[Garren.Shannon]

Well, counter to what you may believe, I could not have gotten it to the point it is now without your suggestions. I appreciated all your help to date. Sorry, it was not my intention to frustrate you so.

Thanks for the advice you did give.

 

[Pat Hartman]

You're welcome

 

[Garren.Shannon]

BTW... I figured out why I can't edit a query with multiple tables. In one table (Vehicles) instead of using the VehicleID primary key, I created a VehicleUniqID field that was not auto-numbered. I used that field to inner join the two tables but the unique ID was not set as a primary key. If you join without using primary keys between tables, you can view but can't edit.

I set the new field to primary key and can now edit where I need to.

 

[Pat Hartman]

BTW... I figured out why I can't edit a query with multiple tables

It isn't the fact that the query had multiple tables that caused the problem.

If your join is not valid, the query will not be updateable. If any part of the query contains aggregation, the query will not be updateable. If the BE is Not Jet/ACE, the table will not be updateable unless it has a primary key, etc. etc. etc.

 

[Garren.Shannon]

So in my case, I had a table "Vehicles" with a primary key of VechcleID but was linking to a different field "VehicleUniqID" that was NOT set as the primary which makes is an invalid join?!? And changing the primary key to "VehicleUniqID" validated the query. That makes total sense to me. I did not know that. I just made the assumption that since it "worked" it was the right way to do things.

Thanks again Pat.

 

[Pat Hartman]

Relationships are ALWAYS PK of one table to data field (FK) in a different table. If the FK is also a PK, the relationship is 1-1 which is very rare. The FK can also be part of a multi-field PK. We don't do that very often because usually, we define autonumbers as a PK rather than multi-field unique indexes.

Joins are not the same as relationships, but for a query to be updateable, the "relationships" represented by the join must be valid. You can join any text field to any other text field or any long integer to any other long integer. Doesn't mean the results will make sense, but the query engine will attempt to perform the join as you coded it. You could join StudentName in the student table to TeacherName in the teacher table. If there are any students who are also teachers, you will get one or more records returned but the query will probably not be updateable since it could create a Cartesian Product. You could also get bogus results if the TomJones who is a student is not the same person as the TomJones who is the teacher. Updating, is a whole different situation. Then more stringent rules come into play.