Check Box question 1

[JAES]

I'm currently having a problem with a yes/no check box that somehow stopped working. I have a query that pulls 3 columns from one table (the one that contains the yes/no column) and 3 columns from another table. Before I add the second table to the query the check box works fine. When I add the second table to the query I just get a "ding" when I try and check it (in a form or directly from the query results). This has been working for over a year so I've obliviously changed something that is effecting this. I've tried changing the relationships to no use. Do I need to provide more information or is it something oblivious that I'm missing.

As always, thanks... Jeff

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JAES]

PHV, OK I'm trying but still don't understand my flaw. Here is the code of the query. According to the MSDN information I'm correct in using INNER JOIN.

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

TheVehiclesInvestigations,RecoveredStolen is the yes/no check box. I was hoping the used could check this box on the form and it would change the data in the table to yes.

I was mistaken when I thought this same query had worked before. It was in a different format that did not use the query.

Thanks for your patience. Your help in the past has been much appreciated.

 

[MajP]

check to see if vehInvestigations.vehIDNum is a primary key and that vehicles.vehIDNum is not a primary key.
If they are both not primary keys then the query is not updateable.
If vehIDNum is a primary key in the vehicleInvestigations table you have a design problem.

Because of the way the above query is ordered, I guess that vehIDNum is not a primary key in your vehicles table if it was the vehcile fields would be ordered first and the join would list the vehicle field first.

 

[JAES]

Thanks majP. I tried to set the primary key for vehInvestigations.vehIDnum but duplicates are required for this column. I then changed the query to where the vehicles table was first and set the vehIDNum primary key on in this table since there can only be one unique vehIDNum in vehicles.. It now works.

I hope that makes sense. Thank you for your help and thanks PHV for the MSDN link.

 

[MajP]

Glad you figured it out because I noticed I said backwards what I meant to say. I assume one vehicle has a unique primary key (vehIDnum) and many investigations can relate back to a vehicle using a foriegn key vehIDnum(not primary).