Drop down list with 100 items + using AutoComplete

[Debbiedo]

I am trying to design a protected MS Word 2003 template. Data from
this form will eventually be imported into Access. One of the fields
will be used to do a join on a look up table in Access so the
information inputted into the word document must be standardized.

I figure I will have to create a drop down list to accomplish this.
The problem is that the list has about 100 items. I would like the
user to add the first few letters and have it autocomplete the item in
the list and populate the field with this.

From what I have read the Word drop down list option can handle only

25 items. What do I need to do to make a list of 100 items and how do
I get it to use autocomplete? This form will be on over 20 separate
computers.

Suggestions anyone? Thank you in advance.

Deb

 

[David Sisson]

For more than twenty-five entries, you'll have to use a userform.

Consider building the userform to accommodate all the questions, then
click OK, and have the userform populate your document.

See http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

To add listbox items from Access see this message. http://tinyurl.com/2k5azj

To add the selection from the OK click, to a formfield;

Private Sub CommandButton1_Click()
ActiveDocument.FormFields(1).Result = ListBox1.Text
Unload Me
End Sub

Using this approach, the Autocomplete wouldn't be used. If you have
long passages that need a abbreviated list, then you may need to add
some Select Case statements in the userform to parse the answers.

Hope this helps,
David

 

[Debbiedo]

For more than twenty-five entries, you'll have to use a userform.

Consider building the userform to accommodate all the questions, then
click OK, and have the userform populate your document.

Seehttp://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

To add listbox items from Access see this message.http://tinyurl.com/2k5azj

To add the selection from the OK click, to a formfield;

Private Sub CommandButton1_Click()
ActiveDocument.FormFields(1).Result = ListBox1.Text
Unload Me
End Sub

Using this approach, the Autocomplete wouldn't be used. If you have
long passages that need a abbreviated list, then you may need to add
some Select Case statements in the userform to parse the answers.

Hope this helps,
David

I created a userform exactly as described in article below and it
works great. Thanks for the pointer.

http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm

Note, if it helps, my userform properties are exactly the same as
example above.

Now I need to populate the combobox with the 100 + items from my
access table. Please note that the Access table will NOT be accessible
to the form users. Users will be emailing me the word documents and I
will be uploading the responses into the Access table.

Is there an automated way to populate my combobox or will I have to
hand enter each one. (I need to have the items spelled exactly as is
in the table to do a join when uploading) Link http://tinyurl.com/2k5azj
seemed to only cover listboxes and it seemed to me that the data was
uploaded in realtime during data entry on the user form. Is this
correct?

Many thanks thus far. I am on the home stretch here.

Deb

 

[David Sisson]

Is there an automated way to populate my combobox or will I have to
hand enter each one. (I need to have the items spelled exactly as is
in the table to do a join when uploading)

That's right, you said you would be collecting the data FROM the
template/form and parsing/storing later.

If the data is fairly stable, meaning it doesn't get changed that
much, then yes, hard coding the list into the userform would be the
best solution. You could have a external file that the userform would
use to propagate the combobox, but that's another file to keep up
with.

Linkhttp://tinyurl.com/2k5azj

seemed to only cover listboxes and it seemed to me that the data was
uploaded in realtime during data entry on the user form. Is this
correct?

Listboxes and combo boxes are very similer and can be used
interchangably without much code variation.
Combox boxes drop down and can be editted. Listboxes are fixed in
size and can't be editted.

So, in the userform_initialize Sub add items like this:

Private Sub UserForm_Initialize()
Me.ComboBox1.AddItem "Apples"
Me.ComboBox1.AddItem "Oranges"
Me.ComboBox1.AddItem "Bananas"
Me.ComboBox1.AddItem "Grape"
Me.ComboBox1.AddItem "Pineapple"

End Sub

Private Sub CommandButton1_Click()
ActiveDocument.FormFields("FavoriteFruit").Result = Me.ComboBox1
'Or you can refer to the formfield by number.
ActiveDocument.FormFields(2).Result = Me.ComboBox1
Unload Me
End Sub

 

[Debbiedo]

That's right, you said you would be collecting the data FROM the
template/form and parsing/storing later.

If the data is fairly stable, meaning it doesn't get changed that
much, then yes, hard coding the list into the userform would be the
best solution. You could have a external file that the userform would
use to propagate the combobox, but that's another file to keep up
with.

Linkhttp://tinyurl.com/2k5azj


Listboxes and combo boxes are very similer and can be used
interchangably without much code variation.
Combox boxes drop down and can be editted. Listboxes are fixed in
size and can't be editted.

So, in the userform_initialize Sub add items like this:

Private Sub UserForm_Initialize()
Me.ComboBox1.AddItem "Apples"
Me.ComboBox1.AddItem "Oranges"
Me.ComboBox1.AddItem "Bananas"
Me.ComboBox1.AddItem "Grape"
Me.ComboBox1.AddItem "Pineapple"

End Sub

Private Sub CommandButton1_Click()
ActiveDocument.FormFields("FavoriteFruit").Result = Me.ComboBox1
'Or you can refer to the formfield by number.
ActiveDocument.FormFields(2).Result = Me.ComboBox1
Unload Me
End Sub

Works like a charm. Thank you very much.

Deb

 

[Doug Robbins - Word MVP]

If you want to populate the list box with all of the fields from the Access
table, use:



Private Sub UserForm_Initialize()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim NoOfRecords As Long

' Open the database

Set db = OpenDatabase("D:\Access\ResidencesXP.mdb")

' Retrieve the recordset

Set rs = db.OpenRecordset("SELECT * FROM Owners")

' Determine the number of retrieved records

With rs

.MoveLast

NoOfRecords = .RecordCount

.MoveFirst

End With

' Set the number of Columns = number of Fields in recordset

ListBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records

ListBox1.Column = rs.GetRows(NoOfRecords)

' Cleanup

rs.Close

db.Close

Set rs = Nothing

Set db = Nothing

End Sub



If you just want to populate the list box with the data from a particular
field, use:



Private Sub UserForm_Initialize()

'allocate memory for the database object as a whole and for the active
record

Dim myDataBase As Database

Dim myActiveRecord As Recordset

'Open a database

Set myDataBase = OpenDatabase("E:\Access97\Ely\ResidencesXP.mdb")

'Access the first record from a particular table

Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)

'Loop through all the records in the table until the end-of-file marker is
reached

Do While Not myActiveRecord.EOF

ListBox1.AddItem myActiveRecord.Fields("Owner")

'access the next record

myActiveRecord.MoveNext

Loop

'Then close the database

myActiveRecord.Close

myDataBase.Close

End Sub


You will of course have to modify the path\filename of the database and the
table name from that used in the above and also the field name with the
second example.

--
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

 

[alborg]

If you wish to take Doug's code a bit further,

Dim aa, tt

**** Doug's code ****

'If you have a table with 14 fields but wish to show only 1, then
'limit number of fields from 14 to 1
ComboBox1.ColumnCount = .Fields.Count - 13
' Load the ListBox with the retrieved records
ComboBox1.Column = .GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close

'set 1 column, width 4.6 inches
tt = "4.6 in"
aa = "4.6 in"
ComboBox1.ColumnWidths = tt
ComboBox1.ListWidth = aa

Cheers,
Al

 

[Doug Robbins - Word MVP]

That may not populate the combo box or list box with the data from the
desired field.

The second routine that I posted provides for the specifying (in the code)
of the field from which the data is to be extracted.

--
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

 

[alborg]

Hi Doug:

Your code populates the combo/list box perfectly well!

What I added is the method to control the combobox "look". For example, if
the table that you are accessing has 15 columns and you only wish to show the
first 3 columns of all the rows obtained, then you can do this to hide the
rest of the columns:

'If you have a table with 15 columns but wish to show only the first 3
ComboBox1.ColumnCount = .Fields.Count - 12
' Load the ListBox with the retrieved records
ComboBox1.Column = .GetRows(NoOfRecords)
' Cleanup
rs.Close
db.Close

'set 3 columns, width 6.6 inches
tt = "4.6 in"
uu = "1 in"
vv = "1 in"
aa = "6.6 in"
ComboBox1.ColumnWidths = tt & ";" & uu & ";" & vv
ComboBox1.ListWidth = aa

This is a small aside and can be left out by the programmer if ugly looking
combo and list boxes aren't an issue. You can see the outcome of this issue
here with "before" and "after" shots of a simple zip code combobox. The
underlying table had 4 columns, but I only want to show 1, and I want that
column to be 1 inch:
http://i38.photobucket.com/albums/e103/alborgmd/uglydropdown.png

Cheers,
Al

 

[Doug Robbins - Word MVP]

But if the first column in the data source is not the one that the user
wants to be displayed?

--
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

 

[alborg]

Change it to zero:

tt = "0 in"
uu = "5.6 in"
vv = "1 in"
aa = "6.6 in"

ComboBox1.ColumnWidths = tt & ";" & uu & ";" & vv
ComboBox1.ListWidth = aa

Pretty cool, huh?

Cheers,
Al

 

[Doug Robbins - Word MVP]

Better to just specify the name of the field that you want displayed as in
the second of the two routines that I posted.

--
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

 

[alborg]

Possibly, but as I see it, the method I posted averts a slow Do While...
Loop, which could impact performance especially in large tables and it allows
for direct handling of column sizes. I do admit, it can seem very quirky when
used for the first time...

Cheers,
Al

 

[Doug Robbins - Word MVP]

The column width can just as well be set in the properties dialog of the
control.

--
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

 

[alborg]

Re: Field/List sizing.

This is one of the few times that I find it faster and more efficient to do
it in code, side by side. As always, one needs to consider the method which
works best for oneself!

Cheers,
Al