Linked drop down fields - auto populate second field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hermithead - 27 Feb 2008 03:47 GMT

Hi, This macro works fine http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm
however is it possible to modify this so the second field Auto
Populates based on the selection of the first drop down box?
At present the user has to manually click in the second field and
select from a list. In my case my list only needs to contain one
choice. Eg. My first drop down list includes a list of names: John
Smith, Bob Smith, Fred Smith which correspond to each of their own
postal addresses. So If I select Bob Smith then the second field
should auto populate with his address and so forth. This doesnt happen
in the macro above can anyone suggest how this second field can be
auto populated. Thanks.

Reply to this Message

Doug Robbins - Word MVP - 27 Feb 2008 09:20 GMT

If you have the addresses stored as autotext entries in the template with
the "name" of the autotext entry being the name as listed in the DropDown
FormFields, the following code run on exit from the DropDown Formfield

' Macro created 15-11-97 by Doug Robbins to add the address corresponding to
a drop down name

Dim myDrop As DropDown
Dim myName As String
Set myDrop = ActiveDocument.FormFields("Name").DropDown
myName = myDrop.ListEntries(myDrop.Value).Name
Address = ActiveDocument.AttachedTemplate.AutoTextEntries(myName).Value
ActiveDocument.FormFields("Address").Result = Address

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hi, This macro works fine
> http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm
[quoted text clipped - 8 lines]
> in the macro above can anyone suggest how this second field can be
> auto populated. Thanks.

Reply to this Message

hermithead - 28 Feb 2008 03:39 GMT

A bit confused. This is what I have.
3 x Addresses are added to Autotext
In Drop-Down Form Field #1 the following items are added: A Smith, B
Smith, C Smith
In Drop-Down Form Field #2 the following Items are added: A Street, B
Street, C Street
Drop-Down Form Field #1 Runs macro below On Exit

Dim myDrop As DropDown
Dim myName As String
Set myDrop = ActiveDocument.FormFields("name").DropDown
myName = myDrop.ListEntries(myDrop.Value).Name
Address =
ActiveDocument.AttachedTemplate.AutoTextEntries(myName).Value
ActiveDocument.FormFields("address").Result = Address

Do I have to add the actual name and address into this macro and
repeat 3 x times per name and address, for example:

Dim myDrop As DropDown
Dim myName As String
Set myDrop = ActiveDocument.FormFields("A Smith").DropDown
myName = myDrop.ListEntries(myDrop.Value).Name
Address =
ActiveDocument.AttachedTemplate.AutoTextEntries(myName).Value
ActiveDocument.FormFields("A Address").Result = Address

Dim myDrop As DropDown
Dim myName As String
Set myDrop = ActiveDocument.FormFields("B Smith").DropDown
myName = myDrop.ListEntries(myDrop.Value).Name
Address =
ActiveDocument.AttachedTemplate.AutoTextEntries(myName).Value
ActiveDocument.FormFields("B Address").Result = Address

Dim myDrop As DropDown
Dim myName As String
Set myDrop = ActiveDocument.FormFields("C Smith").DropDown
myName = myDrop.ListEntries(myDrop.Value).Name
Address =
ActiveDocument.AttachedTemplate.AutoTextEntries(myName).Value
ActiveDocument.FormFields("C Address").Result = Address

Thanks

On Feb 27, 6:20 pm, "Doug Robbins - Word MVP"
<d...@REMOVECAPSmvps.org> wrote:

> If you have the addresses stored as autotext entries in the template YES
> with the "name" of the autotext entry being the name as listed in the DropDown
[quoted text clipped - 32 lines]
>
> - Show quoted text -

Reply to this Message

Doug Robbins - Word MVP - 28 Feb 2008 05:29 GMT

You do not use a second DropDown FormField, but you do have a Text FormField
and my code assumes that the bookmark name "Address" has been assigned to
that FormField.and it assumes that the name of the bookmark assigned to the
DropDown FormField is "name"

You only have the code once (exactly as I posted it) and you set it to be
run On Exit from the DropDown FormField.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> If you have the addresses stored as autotext entries in the template YES
> with the "name" of the autotext entry being the name as listed in the
[quoted text clipped - 38 lines]
>
> - Show quoted text -

Reply to this Message

hermithead - 29 Feb 2008 02:55 GMT

On Feb 28, 2:29 pm, "Doug Robbins - Word MVP"
<d...@REMOVECAPSmvps.org> wrote:

> You do not use a second DropDown FormField, but you do have a Text FormField
> and my code assumes that the bookmark name "Address" has been assigned to
[quoted text clipped - 13 lines]
>
> "hermithead" <hermith...@gmail.com> wrote in message

> On Feb 27, 6:20 pm, "Doug Robbins - Word MVP"
>
[quoted text clipped - 43 lines]
>
> - Show quoted text -

It's now working - Thank you. However I was hoping the Text Form Field
would auto populate without the user having to click into this field.
Just want it so the user selects a name from the drop down and then an
address automatically appears in the Text form. Without the user
having to click in the Text Form at all. At the moment it is prone to
user error - the user could select the name but neglect to click in
the Text Form which triggers the change of address. In html I think
its called a cascading dropdown. This site explains it
http://www.asp.net/AJAX/AjaxControlToolkit/Samples/CascadingDropDown/CascadingDr
opDown.aspx
Thanks again

Reply to this Message