Populating a text box with results of a query 1

[robertsfd]

I have a form based on a table called PEOPLE which has personal info (name, address, phone, etc). One field in the table is "State" which is a 2 character field (the abbreviation only). I have a table STATES that has the 2 character state code and the full state name - I use this to populate a drop-down box for inputting the state in the PEOPLE table.

I want a form to add info to the PEOPLE table. One added thing I want is that when a user chooses their state abbreviation from the drop-down box, I want a textbox on the form that then displays the associated state name (unbound control - I do not want to store this info).

I know how to get a drop-down box to populate via an SQL query that uses a value chosen on the current form in the WHERE part of the statement, but I have been unsuccessful making the same thing happen with a text box.

Any ideas? Thanks in advance.

 

[scriverb]

In the ComboBox dropdown with the State abbreviation, the RowSource should include both the abbrebiation field and the Full State Name field.

Two columns. Change the column count to 2. Columns widths to .5";0". Put this VBA code in the AfterUpdate of the ComboBox.

Me.StateNameControl = Me.StateCombo.Column(1)

The problem with this technique is that everytime you change records you will have to do a lookup of the abbreviated value and populate this unbound control. I would recommend that you combine the abbreviation and the state name into one field for the ComboBox and display it in the following manner:
MI - Michigan

You could use the following SQL as the RowSource for the combobox:
Select A.Abbreviation, A.Abbreviation & " - " & A.StateName as State from States as A Order by A.Abbreviation;

Now both values are visible when a pick is made. Make sure that the Bound column is 1 and the column widths are 0;2" with a column count of 2. In this scenarion you only see the MI - Michigan during and after the pick and the control is bound to the Abbreviation field and that value is stored in the record. Because this pick doesn't require the filling of an unbound text box you don't have to worry about on a find a record or next record situation.

Let me know which way you want to go and we can work through the process.



Bob Scriver

 

[robertsfd]

Thanks Bob! Both ways work like a charm. I do understand the implications of accessing the table every time with the first option.

Thanks for your help.