cascading combobox in word with database from access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

irisbutnaru1@gmail.com - 22 Dec 2007 12:23 GMT

I am trying to create a Userform in word XP with 2 comboboxes.
combobox1 - countries
combobox2 - cities

What I need is for the user to select a country in combobox1 and
combobox2 will be populated only with the cities of this country.

I wrote the code:
Option Explicit

Private Sub cboCountry_change()

Dim dbDatabase As Database
Dim rst As Recordset
Dim y As Integer

Dim strSQL As String

Set dbDatabase = OpenDatabase("C:\phone.mdb")
Set rst = dbDatabase.OpenRecordset("SELECT DISTINCT city FROM tblAll
WHERE Country = '" & cboCountry.Text & "' ORDER BY city;",
dbOpenSnapshot)

y = 0

'This code populates the combo box with the values in the CompanyName
field.

With rst
On Error Resume Next
Do Until .EOF
cboCity.AddItem (y)
cboCity.ColumnCount = 3
cboCity.BoundColumn = 3
cboCity.ColumnWidths = "6 in"
cboCity.AutoTab = True
cboCity.Column(0, y) = rst.Fields("city")
.MoveNext
y = y + 1
Loop
End With
End Sub

Private Sub UserForm_Initialize()

Dim dbDatabase As Database
Dim rs As Recordset
Dim i As Integer

' This code activates the Database connection. (Change the path to
reflect your database).
Set dbDatabase = OpenDatabase("C:\phone.mdb")

' This code opens the Managers table. (Change the Table to reflect
the desired table).
Set rs = dbDatabase.OpenRecordset("SELECT DISTINCT Country FROM tblAll
ORDER BY Country;", dbOpenSnapshot)

i = 0

'This code populates the combo box with the values in the CompanyName
field.
With rs
On Error Resume Next
Do Until .EOF
cboCountry.AddItem (i)
cboCountry.ColumnCount = 3
cboCountry.BoundColumn = 3
cboCountry.ColumnWidths = "6 in"
cboCountry.AutoTab = True
cboCountry.Column(0, i) = rs.Fields("country")
.MoveNext
i = i + 1
Loop
End With
End Sub

the code is produsing the resaults I wanted but with one big problem -
after I chose... say... USA in combobox1 and Washington in
combobox2... and then I change my mind and choose France in combobox1
and opencombobox 2 - There are additional numbers populating the
combobox2 that I don't know where they come from and why it
happends...

If you wan't to try the code create an aceess table called tball with
2 columns: country and city and then try this code... it works... bu
drives me CRAZY!!

thank you

Reply to this Message

Jay Freedman - 22 Dec 2007 13:20 GMT

In cboCountry_change(), before you start adding items to cboCity you
need to make sure that cboCity's list is empty. Just before the "With
rst" statement, insert the statement

cboCity.Clear

The first time you select something in the Country box, this statement
won't do anything because the City list is already empty. The next
time and each time afterward, though, you need to remove the entries
from the previous country before adding cities for the current
country.

>I am trying to create a Userform in word XP with 2 comboboxes.
>combobox1 - countries
[quoted text clipped - 85 lines]
>
>thank you

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Reply to this Message

irisbutnaru1@gmail.com - 22 Dec 2007 21:03 GMT

> In cboCountry_change(), before you start adding items to cboCity you
> need to make sure that cboCity's list is empty. Just before the "With
[quoted text clipped - 108 lines]
>
> -הראה טקסט מצוטט-

good evening jay!
Thank you for your help! it works perfectly!!
But what if I have to save the chosen country and the chosen city and
see them both the next time I open this document... actualy... I'm
saving the chosen country and city in the custom property of each
document based on this template and when I open the document again...
the country and city I chose before are the one's I see when the
userform is opened (and it's opened automatically as I open the
document)...
thank you again and I hope you have an answer for me...

Reply to this Message

Jay Freedman - 23 Dec 2007 03:25 GMT

>good evening jay!
>Thank you for your help! it works perfectly!!
[quoted text clipped - 6 lines]
>document)...
>thank you again and I hope you have an answer for me...

Generally for this kind of operation I prefer to save the values into
document variables rather than custom properties. The main difference
is that custom properties are accessible to the user in the File >
Properties dialog, but you don't generally want to give them the
ability to change (and probably mess up) these values.

Anyway, the idea is to have the UserForm_Initialize procedure read the
saved country value (if it exists) and set the .Value of the country
combo box to it. The cboCountry_Change procedure reads the saved city
value (if it exists) and sets the .Value of the city combo box -- but
only if the userform is still being initialized.

The OK button's Click procedure saves the values of the combo boxes
into the document variables just before exiting. Those values will be
saved with the document.

Here's some simplified sample code; I've replaced your database
accesses with simple Array assignments to keep it short.

Option Explicit
Private initializing As Boolean

Private Sub cboCountry_Change()
Dim initVal As String

With cboCity
Select Case cboCountry.Value
Case "Canada":
.List = Array("Montreal", "Quebec", "Toronto")
Case "Japan":
.List = Array("Osaka", "Tokyo", "Yokohama")
Case "Sweden":
.List = Array("Malmo", "Stockholm", "Uppsala")
Case Else
End Select
.ListIndex = 0

If initializing Then
On Error Resume Next
initVal = ActiveDocument.Variables("city").Value
If Err.Number = 0 Then
cboCity.Value = initVal
End If
End If
End With
End Sub

Private Sub cbOK_Click()
ActiveDocument.Variables("country").Value = cboCountry.Value
ActiveDocument.Variables("city").Value = cboCity.Value
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Dim initVal As String

initializing = True

With cboCountry
.List = Array("Canada", "Japan", "Sweden")
.ListIndex = 0

On Error Resume Next
initVal = ActiveDocument.Variables("country").Value
If Err.Number = 0 Then
cboCountry.Value = initVal
End If
End With

initializing = False
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Reply to this Message

Graham Mayor - 22 Dec 2007 13:51 GMT

See also Greg Maxey's page on cascading list boxes at
http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

Signature

<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> I am trying to create a Userform in word XP with 2 comboboxes.
> combobox1 - countries
[quoted text clipped - 85 lines]
>
> thank you

Reply to this Message

irisbutnaru1@gmail.com - 22 Dec 2007 21:06 GMT

> See also Greg Maxey's page on cascading list boxes athttp://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm
>
[quoted text clipped - 98 lines]
>
> -הראה טקסט מצוטט-

good evening Graham!
thank you for your answer. I will try the codes you reffered me to
yesterday and see if it works.
Jayws answer works but I have another problem... you can see in my
reply to Jay.
As per your suggestion... I will be smarter tomorrow and will keep you
informed.
Thank you again!

Iris

Reply to this Message

irisbutnaru1@gmail.com - 22 Dec 2007 21:18 GMT

On 22 דצמבר, 23:06, irisbutna...@gmail.com wrote:

> > See also Greg Maxey's page on cascading list boxes athttp://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm
>
[quoted text clipped - 111 lines]
>
> -הראה טקסט מצוטט-

oopps... I will try it tomorrow.... not yesterday... sorry

Reply to this Message