Append query based on combo box selection

Hello all,

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.......!

Ensor.

ensorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

walterecookCommented:
[Forms]![frmNEWMAIN]![Life]![StateID] will display the value of the Bound Column of the combo box.  You could either
a) change the bound column to the one you want or change the query to this:
[Forms]![frmNEWMAIN]![Life]![StateID].column(2)

Where 2 is the third column in your combo box, for instance.
(Remember 0 is the first column in both examples)

Good luck
Walt
0
ensorAuthor Commented:
Hi Walt,

I'm not sure how to change the bound column if the fields it is looking up are in a different table.......?

So, I tried hardcoding
[Forms]![frmNEWMAIN]![Life]![StateID].column(2)

but all that gave me was.....
Undefined function '[Forms]![frmNEWMAIN]![Life]![StateID].column' in expression

My combo box works on this....
SELECT DISTINCTROW tlkpLifeStates.*, tlkpLifeStates.LifeStateText
FROM tlkpLifeStates;

Still stuck........

Ensor.
0
walterecookCommented:
The "bound column" property of a text box really just says this:
Whenever you reference this combo, report the value in this column.  So run your query:
SELECT DISTINCTROW tlkpLifeStates.*, tlkpLifeStates.LifeStateText
FROM tlkpLifeStates;
If it shows this:
Field1, field2, LifeStateText
1           a          AZ
2            B         NY

If the bound column is 0 the combobox value is 1
If the bound column is 1 the combobox value is a
If the bound column is 2 the combobox value is AZ

See?
Hope that helps
Walt


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ensorAuthor Commented:
Thanks Walt - you've shown me the error of my ways.  My problem was down to the combo box also having it's Control Source as the StateID - which of course was a number - hence when I tried to bound it to a text field it didn't like it.  

Your advice has shown me what I really should be doing in this box.  

Thankyou...

Ensor.
0
walterecookCommented:
Good for you.  Glad you got it going.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.