Combo box Array

[BV2312]

Hi all

I am trying to use the following code in VBA however i seem to get an error
about compiling it.....

I copied this from:

Combo Box Content depending on result of another combo box
In: microsoft.public.word.vba.userforms


Private Sub UserForm_Initialize()
ComboBox1.List = Array("Cat", "Dog")
ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "Cat"
With ComboBox2
.Clear <--------------------------------
.List = Array("Purr", "Meow", "Catnip")
.ListIndex = 0
End With
Case "Dog"
With ComboBox2
.Clear
.List = Array("Bark", "Wag", "Dog Toy")
.ListIndex = 0
End With
Case Else
' ignore
End Select
End Sub


the error accurs at .clear

Hope this helps

 

[Daniel]

In the case "Cat" your .clear line has extra symbols that are most probably
causing the error because they cannot be compiled. You need to comment them
out or remove them

.Clear <--------------------------------

change it to either
.Clear '<--------------------------------
to comment out the extra symbols

or

.Clear

Daniel

 

[Daniel]

Actually after looking at it some more. I cannot find this method in Access.
Is this an access question or is this relevant to word (if so repost your
question is a word forum).

As for clearing a combobox in access try

Me.ComboboxName.RowSource = vbNullString

Daniel

 

[BV2312]

Hi

Sorry that was there for indication purposes only for the reader....

my code looks like this really:

Private Sub UserForm_Initialize()
ComboBox1.List = Array("Cat", "Dog")
ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
Select Case ComboBox1.Text
Case "Cat"
With ComboBox2
.Clear
.List = Array("Purr", "Meow", "Catnip")
.ListIndex = 0
End With
Case "Dog"
With ComboBox2
.Clear
.List = Array("Bark", "Wag", "Dog Toy")
.ListIndex = 0
End With
Case Else
' ignore
End Select
End Sub

i am having problems with .Clear .List function maybe the the VBA has
changed I'm using Access 2003 if that helps....

 

[BV2312]

Well it's an access question using an Word example. I would be grateful if
you could help me convert this code into a method in access.

so i have cboName1 and cboName2

cboname1= Cat or Dog (array)

and depending on cboName1, cboName2 would be: (array)

Purr, Meow, Catnip

OR

Bark , Wag , Dog Toy

BV2312

 

[Daniel]

This works for me,

Select Case Me.ComboBox1.value
Case "Cat"
With Me.ComboBox2
.RowSource = vbNullString
.AddItem "Purr"
.AddItem "Meow"
.AddItem "Catnip"
End With
Case "Dog"
With Me.ComboBox2
.RowSource = vbNullString
.AddItem "Bark"
.AddItem "Wag"
.AddItem "Dog Toy"
End With
Case Else
' ignore
End Select

For this to work the rowsource of ComboBox2 must be set to Value List

Daniel

 

[BV2312]

Hi that seems to work... but there seems to be update bug eg: when i select
cat nothing comes up in cboname2 but when i change CBOname1 to dog cboname2
has the following values Purr, Meow, Catnip

On an opinion do you think it would be better to do in a table instead on
putting the information in VB?

regards

 

[Daniel]

It depends.

If your values are likely to change/eveolve then a table is the way to go.
If they will never change then VBA is fine.

What version of access are you using?

Daniel

 

[BV2312]

Access 2003

It depends.

If your values are likely to change/eveolve then a table is the way to go.
If they will never change then VBA is fine.

What version of access are you using?

Daniel

 

[BV2312]

i am going to use a TABLE instead now....

so now i have 2 tables

1=animal type
fields includes:
name
noise

name = rowsource = SELECT distinct animal.Name FROM animal
(so shows only dog or cat);
Noise = rowsource = this is where i am having my problem i want all the rows
that name = dog or name = cat

2= animal
name = cat or dog
noise = Purr, Meow, Catnip or Bark , Wag , Dog Toy

Regards