Combo box Array

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BV2312 - 29 Jan 2007 19:09 GMT

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

Reply to this Message

Daniel - 29 Jan 2007 20:15 GMT

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

> Hi all
>
[quoted text clipped - 34 lines]
>
> Hope this helps

Reply to this Message

BV2312 - 29 Jan 2007 20:21 GMT

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

> 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
[quoted text clipped - 50 lines]
> >
> > Hope this helps

Reply to this Message

Daniel - 29 Jan 2007 20:20 GMT

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

> Hi all
>
[quoted text clipped - 34 lines]
>
> Hope this helps

Reply to this Message

BV2312 - 29 Jan 2007 20:38 GMT

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

> 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
[quoted text clipped - 44 lines]
> >
> > Hope this helps

Reply to this Message

Daniel - 29 Jan 2007 20:54 GMT

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

> 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.
[quoted text clipped - 61 lines]
> > >
> > > Hope this helps

Reply to this Message

BV2312 - 29 Jan 2007 21:22 GMT

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

> This works for me,
>
[quoted text clipped - 86 lines]
> > > >
> > > > Hope this helps

Reply to this Message

Daniel - 29 Jan 2007 21:42 GMT

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

> 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
[quoted text clipped - 95 lines]
> > > > >
> > > > > Hope this helps

Reply to this Message

BV2312 - 30 Jan 2007 07:19 GMT

Access 2003

> It depends.
>
[quoted text clipped - 104 lines]
> > > > > >
> > > > > > Hope this helps

Reply to this Message

BV2312 - 30 Jan 2007 16:10 GMT

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

> Access 2003
>
[quoted text clipped - 106 lines]
> > > > > > >
> > > > > > > Hope this helps

Reply to this Message