combo boxes pointing to foreign key

[Howlsta]

I know this is easy but haven't done it for ages and can't find my book.

I've got a form of accident details. The form it's based on has a vehicle ID field which is the foreign key. I want the user to be able to select the model and make of the car from the combo boxes then Access will store it as vehicle ID in the accident details table.

the Make and model combination is always unique, so points to the vehicleID in that particular table.

I'm using forms!myform!mycbo etc in the queries the problem is I can't seem to set it up quite right.

i.e. when I pick a model and make it tries to store it in the wrong table (trying to make a duplicate) rather than changing the vehicleID foreign key in the accident details table

say for instance

tblvehicles
VehicleID make model
1 ford escort


other table
formID Vehicle ID Year Crash Description
A0001 1 1987 took a bang from behind

I want to be able to change vehicle ID in the other table on my form sipmly by picking the make and model from cbos, at the moment when I pick make and model it tries to add a new record in vehicle table

 

[Oldsoftboss]

You should base your form on query.
See the post by vangogh228 at this post
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=37277

HTH
Dave

 

[Howlsta]

Thanks, but not sure this example is quite the same.

In the example you gave the user selects the store number which is equivalent to vehicleID in my problem. If I enter VehicleID the model and make will appear, but this is not what I want to do. VehicleID is meaningless to the user. The user is not going to know that VW Polo has vehicleID 1. I want the user to be able to pick a vehicle from cbo then pick the model (I know how to restrict the model cbo to model associated with a particular make). Once they pick the combination of model and make, I want the vehicleID to change to the number which represents that make and model in tblVehicle.

Whatever I seem to try at the moment seems to try and add a new record in tblVehicle (which would violate the index) rather than changing the VehicleID in tblAccident.

thanks, hope i've managed to explain it better.

Rich

 

[Howlsta]

thanks Pat,

got it doing exactly what I want now. I can use this in some of the other forms too. I've been handed this db and it's in a right state - normalisation not done properly/at all