Can not edit a query driven form 2

[JAES]

I have a form that is driven by the following query. I would like to be able to edit one column (VehiclesInvestigations.RecoveredStolen) and change it from no to yes (it is a Y/N checkbox format). When I try and do that it beeps and will not change the box. Do I have something out of order? There are 2 tables I'm drawing information from.


SELECT VehiclesInvestigations.VehIDNum, VehiclesInvestigations.InvestigationNumber, VehiclesInvestigations.RecoveredStolen, Vehicles.VehYear, Vehicles.VehMake, Vehicles.VehModel, Vehicles.VehPrimaryColor, Vehicles.VehLicenseNumber
FROM VehiclesInvestigations INNER JOIN Vehicles ON VehiclesInvestigations.VehIDNum = Vehicles.VehIDNum

thank you for your help

 

[lespaul]

There are 2 tables I'm drawing information from.

I believe that in order for the query to be updatable when using two tables you have to include the PK from both tables in the query.



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[JAES]

I didn't have primary keys set in either table. I can set the VehIDNum column as a primary key in the Vehicles table but not in the VehiclesInvestigations table. I get an error when I try and set the primary key on VehIDNum telling me that it would create duplicate values, which that tables has. This has worked in the past but all of a sudden it doesn't.

 

[jedraw]

In the VehiclesInvestigations table, any given vehicle could be related to more than 1 investigation, so VehIDNum in this table would not identify distinct records.

Is it possible you have recently added a second investigation for a specific VehIDNum?

 

[dhookom]

You should be able to edit records in the query if Vehicles.VehIDNum is the primary key field.

Duane
Hook'D on Access
MS Access MVP

 

[fneily]

No primary keys?? When you create your tables, did you see the statement that Microsoft highly suggests a primary key? Basically, all tables should have a PK. Here's some suggested reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

It seems, and I know from my working in a narcotics unit, that a vehicle can be part of many investigations and an investigation can involve many vehicles. So you have a many-to-many relationship which relational databases don't like. So you must build a so-called junction table.

 

[JAES]

Sorry for the delayin responding, ATT has been down since yesterday.

Thanks to all who have helped with my problem. I assigned the PK to the VehIDNum column in the Vehicles tables like dhookonm suggested and it seems like the form will now update the table, via the query. It was frustrating because it had been working and they it stopped. I must have eliminated the PK somehow.

fniely, I do assign PK's many times but get confused when the column that I think needs the key has duplicates and I get an error when assigning it. I have read and will re-read the material you provided, thanks. Your correct that that a vehicle can be part of many investigations and an investigation can involve may vehicles (this is a BNE joint task force). That is why I created the VehiclesInvestigations table that only consists of VehIDNum, InvestigationNumber and the RecoveredStolen Y/N column. I thought that was the only way to store and retrieve that info. With the help of Tek Tips I learn something new every day, thanks all.