Update 3 fields from one drop down box

[dzirkelb]

Is it possible to update 3 fields from one drop down box? the drop down box's sql is as follows:

SELECT [tblCurrentRoute].[KFC ID], [tblCurrentRoute].[PERIOD/MONTH], [tblCurrentRoute].[PK ID] FROM [tblCurrentRoute]

I would like to update the following fields on tblMasterEvaluations:

tblCurrentRoute.KFC ID = tblMasterEvaluations.StoreID
tblCurrentRoute.Period/Month = tblMasterEvaluations.Period
tblCurrentRoute.PK ID = tblMasterEvaluations.PKID

I have the drop down showing the correct information, and it stores the Store ID correctly; however, I would like to use that selection to update other fields in my table also...is this possible? Thanks!

 

[Len Boorman]

Use the On Click property of the combo to fire update queries to update the various fields

I assume that the combo contents are derived from some existing data

Len

You can reference the various columns in a combo box as

ComboName.Column(0) etc. Column references start from zero

 

[dzirkelb]

Thanks for the reply!

I have tried this:

Private Sub cmbStoreID_LostFocus()
DoCmd.RunSQL ("UPDATE tblMasterEvaluations SET (StoreID = '" & cmbStoreID.Column(0) & "')")
End Sub

everytime I try to test it, it wants to close / repair my database...any ideas why?

 

[dzirkelb]

I've just tried to simplify it and it still doesnt' work...I tried:

StoreID.Value=cmbStoreID.Column(0) and it still breaks....

 

[dzirkelb]

I have made a workaround for this that works great...one of hte values was a primary key, so I just stored that and ran an update query afterwareds...thanks!

 

[dzirkelb]

that code still produces my database to fail...I have a workaround which works perfectly for this database, and, the queries are used later on in other applications..so I'm going to stick with that. Thanks though