test for no values in a multidimensional array 2

[sub5]

Hello all,

How do I test that a multidimensional array has nothing in it? This is my code and process:

A form is opened. The following is declared at the form module level.

Private marr1() as variant
Private mintrows as integer

The form on open event includes:
mintrows = -1

The user selects an item from cbo1 and clicks cmd1 which runs this code:

mintrows = mintrows + 1
redim preserve marr1(0,mintrows)
marr1(0,mintrows) = me!cbo1

The SQL rowsource of ctl1 is modified so that the selected item drops off the list.

The user then selects another item from cbo1 and clicks cmd1 again.

They can cycle through like this until not items remain in cbo1's dropdown list.

First I wanted to get rid of the need for mintrows by using this code but I get runtime error 9; subscript out of range:

dim upper as integer
upper = ubound(marr1,2)
(nb also tried ubound(marr1) and ubound(marr1,0) and ubound(marr1,1))
redim preserve marr1(0,upper)
marr1(0,upper) = me!cbo1

I also tried to make it so that the first time round the code checks to see if the variable has not been initialised / is empty / null / blank but it always thinks there is something there:

if isempty(marr1) = true then
redim preserve marrrateid(0,0)
marr1(0,0) = me!cbo1
else
Dim upper as integer
upper = ubound(marr1,2)
redim preserve marr1(0,upper)
marr1(0,upper) = me!cbo1
end if

Instead of isempty I have also tried isnull, trim([marr1] & "") = "", marr1 =/is/like "".

Any ideas?

 

[TheAceMan1]

How are ya sub5 . . .

Why not use a [blue]MultiSelect[/blue] listbox and [blue]pack your array all in one shot[/blue] with cmd1?

See Ya! . . . . . .

 

[sub5]

Hi AceMan

Thanks for your idea, but, for simplicity, I missed out a step that happens with cmd1 which means I can't use a multiselect listbox.

IE
The frm also has a multiselect listbox. So the user selects an item from cbo1, say a model of a car. They then also select car attributes from a multiselect listbox eg colour, carlos fandango wheels etc. Then when they click cmd1, info is taken from the multiselect listbox and populated in a table along with the car model id from cbo1.

I can't make cbo1 multiselct because the attributes won't neccessarily be the same for each model for which the user wants to create an attibute record.

 

[TheAceMan1]

sub5 . . .

Understood . . . however in your latest post you say:

[blue]Then when they click cmd1, info is taken from the multiselect listbox and [purple]populated in a table[/purple] along with the car model id from cbo1.[/blue]

I fail to see the need for the Array in this case. it must be serving you some other function (unexplained as yet).

In any case to relieve yourself of the [blue]Redim[/blue] mess, have a look at [blue]Custom Collections![/blue]

See Ya! . . . . . .

 

[sub5]

Hi AceMan,

I know of the concept of custom collections, but never used them, can you point me to any links that you think would be a good place to start learning (considering the process I have outlined here).

Yes you are right, I am using the array elsewhere - namely to build SQL which I use as the row source for a subsequent cbo later on the form ie another tab.

Thanks again for your input.

 

[TheAceMan1]

sub5 . . .

In VBE help, do a search for [blue]Collection Object[/blue].

See Ya! . . . . . .

 

[sub5]

Hi AceMan, I'm liking these collections!
Is there anyway I can refer to a collection property as a parameter for a query (other than building an SQL)?
Also what is the syntax for refering to a collection which has been declared as public in a form module but I want to refer to it in an independant module?

 

[PHV]

Any Public variable declared in a form module may be refered as any property of the form, provided the form is open.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

[blue] . . . or return the collection value thru a function![/blue]

See Ya! . . . . . .

 

[sub5]

Hi guys,
I have applied what you said, but now have hit another problem, I've posted it under thread "using a function as a query criteria"

http://www.tek-tips.com/viewthread.cfm?qid=1241819&page=1

because it seemed so different to my original question under this thread.

Thankyou for your continuing help!