I need to run an Append query based on the results of a combo box selection.
I've almost done it but not quite - so I'm hoping there's an expert out there who can tell me what I am doing wrong !
The situation is as follows.....
I have got a subform off a main form which itself has got two subforms on it based on two different tables. The first subform has a combo box which is bound and does a lookup to yet another table if it needs to be changed.
I need to update a field in the second subform with the results from my combo box selection - after update of the combo.
I have written an update query to run After Update which nearly does it...............
INSERT INTO LifeHistory ( PolicyID, CallDate, CallTime, Subject )
SELECT [Forms]![frmNEWMAIN]![Life]![PolcyID] AS PolicyID, Date() AS CallDate,
Time() AS CallTime, [Forms]![frmNEWMAIN]![Life]![StateID] AS Subject;
What this does, is update my Subject field with the StateID as a number - but what I really want is what my combo box is displaying which is "tlkpLifeStates.LifeStateText" - i.e, results of the lookup the combo is doing - NOT it's corresponding foreign key value.
It's a bit confusing (to me at least) so if you need any more information then please ask. Otherwise, I'd appreciate the help.......!