Updating field from a combo box

[Guest]

My form is opened in date entry mode to create a new project. A Contact Name
is chosen from tbl_Contact through a combo box that lists all possible
contacts. After selecting the contact I need to display the contact's phone
number. I have done this doing using the DLookup function in the phone
number control.

If the Contact Name is then changed again, an event triggered either OnDirty
or OnChange goes to a maco that will Requery the PhoneNumber control and will
update the phone number to match the Contact's Name.

However, if a Contact name is typed into the Contact Name field rather than
selected from the drop down of the comobo box, the phone number is not
updated even when the typed name is part of the combo box list.

Should the user be able to type in a name rather than select one and have
the phone number update. If so, how?

Any help will be appreciated. Thanks.

 

[Allen Browne]

If you wanted the user to be able to alter the phone number for this
particular project (while leaving the contact's original default phone
number in place), just use the AfterUpdate event of the combo.

If the user does not need to change the number, you could add an extra
column to the combo with the phone number, and then read it from there in
your form. For example, if the combo already has 2 column in its RowSource,
add a 3rd:
RowSource: SELECT ContactID, ContactName, ContactPhone
FROM tblContact;
Column Count: 3
Column Widths: 0;1;0
Name: ContactID
Now the text box can show the phone number just by setting its Control
Source to:
=[ContactID].Column(2)

(Note that Column() is zero-based, i.e. the first column is 0, the 2nd is 1,
and so on.)

 

[Guest]

Thanks for the quick response. This approach is easier than the way I was
doing it and I can think of several places in my application to apply it in
addition to this one. As they say in the PGA (golf association), "these guys
are good."

If you wanted the user to be able to alter the phone number for this
particular project (while leaving the contact's original default phone
number in place), just use the AfterUpdate event of the combo.

If the user does not need to change the number, you could add an extra
column to the combo with the phone number, and then read it from there in
your form. For example, if the combo already has 2 column in its RowSource,
add a 3rd:
RowSource: SELECT ContactID, ContactName, ContactPhone
FROM tblContact;
Column Count: 3
Column Widths: 0;1;0
Name: ContactID
Now the text box can show the phone number just by setting its Control
Source to:
=[ContactID].Column(2)

(Note that Column() is zero-based, i.e. the first column is 0, the 2nd is 1,
and so on.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My form is opened in date entry mode to create a new project. A Contact
Name
is chosen from tbl_Contact through a combo box that lists all possible
contacts. After selecting the contact I need to display the contact's
phone
number. I have done this doing using the DLookup function in the phone
number control.

If the Contact Name is then changed again, an event triggered either
OnDirty
or OnChange goes to a maco that will Requery the PhoneNumber control and
will
update the phone number to match the Contact's Name.

However, if a Contact name is typed into the Contact Name field rather
than
selected from the drop down of the comobo box, the phone number is not
updated even when the typed name is part of the combo box list.

Should the user be able to type in a name rather than select one and have
the phone number update. If so, how?

Any help will be appreciated. Thanks.