requery after OnChange 3

[lauraSatellite]

I have a table called Name, with two fields: FName, SName.

I have a form with a combo box that has the source Name.FName.
On this form i also have a text box that i want to display SName.

My table contains an entry of FName: 'John', SName: 'Doe'.
Currently 'John' appears as a possible choice in the combo box (amoung other choices).
I want to choose 'John' in the combo box, and have the text field automatically updated to read 'Doe'.

I have been trying to attach an onChange procedure to the FName combobox, but i havent been able to make this work correctly. Amoung the things i've tried are:

Private Sub IP_Change()
SName.Requery (Me.FName.Value())
End Sub

where Requery should call the following query:

select SName from Name
where FName = ?;

If you have any ideas on how i may get this to work, i would greatly appreciate your input.
Thanks.

 

[KenReay]

Hi

You should not need code to do this

If you make the Combo box have two columns FName and SName (if you do not want user to see SName, set its column width to zero)

in the text box control put =MyComboBoxName.Column(1)

using your control name of course

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

Change the RowSource of your combo like this:
SELECT FName, SName FROM [Name] ORDER BY 1;
Set its ColumnCount property equal to 2.
And then, in the AfterUpdate event procedure of the combo, add the following code:
Me![name of textbox control] = Me![name of listbox control].Column(1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lauraSatellite]

I hadnt thought of doing it like that, great idea.

I am trying to implement that idea right now, but im having some difficulty. I do have a worry about inserting entries to the Name table using this form, if i were to implement this idea. The FName field is the primary key of the table. Will setting the FName combo-box up with a query that has two columns disturb what is saved in the FName field of the table? I need column(0) to be saved, rather than column(1).

 

[PHV]

Take a look at the ControlSource and BoundColumn properties of a ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Laura

The requery repopulates the combo box whcih would mean the current record, if added and written to the table would be included.

The FName field is the primary key

Now here is a question:
Are you using a combo box on the primary key to retrieve a record and / or insert a new record?

Perhaps you may consider using an unbound combo box to retrieve the record (using the combo box wizard), and levae the bound field alone for data entry. Using PHV code with the AfterUpdate (field) Event Procedure, or let the combo the wizard generate the code will work just groovy on the unbound combo box.

Richard

 

[lauraSatellite]

Thanks a million for your help. Its working perfectly now

 

[lauraSatellite]

Richard, sorry i missed your post yesterday. Just to have replied: the combo box is being used to update the value in a text field on the form, which will (along with other information) be inserted as a new entry in the db. I have this working now using the information i recieved in this thread. I didnt use procedures, i used a query with two columns as a source for the combo-box, the second column was hidden. It was this hidden column that i used to update the value in the text-field. Its working magically
Thanks!
Laura

 

[misscrf]

Thank you, Ken. I was looking for this, and it is so perfect and simple!

misscrf

Management is doing things right, leadership is doing the right things