Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Append query based on combo box selection

Posted on 2003-10-27
5
504 Views
Last Modified: 2012-06-27
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.

0
Comment
Question by:ensor
  • 3
  • 2
5 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 9629537
[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
 

Author Comment

by:ensor
ID: 9629742
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
 
LVL 17

Accepted Solution

by:
walterecook earned 500 total points
ID: 9630327
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
 

Author Comment

by:ensor
ID: 9632196
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
 
LVL 17

Expert Comment

by:walterecook
ID: 9633507
Good for you.  Glad you got it going.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question