Hi guys

I've got a calling function that calls a function called GetScenarioLog and it returns an array called ChangeLogArray. It then assigns the contents of the array to a listbox (ChangeLog) with multiple columns

ChangeLogArray = GetScenarioLog()
ChangeLog.Column() = ChangeLogArray


If no records are retrieved, no recordset will be assigned to the array and therefore the I get errors trying to assign an empty array to the listbox.

I need to be able to check if the array is empty before I try to assign the contents of the array to a listbox.

How do I check for this?

Many thanks

Cheers

Ciaran

If its declared as an array:
If UBound(ChangeLogArray) > 0 then ' it has data.

If its declared as a variant:
If VarType(ChangeLogArray)> vbArray then ' it has data

hth
Phil

Hi Phil

The ChangeLogArray is declared as a variant and the GetScenarioLog also returns a variant but I tried the VarType(ChangeLogArray)> vbArray and this is TRUE but in my watches (when debugging) there is no data.

Is this correct?

Cheers

Ciaran

Hmm, that's unusual. Sounds like you'd better use both tests:
If VarType(ChangeLogArray)> vbArray then
    If UBound(ChangeLogArray) > 0 then
        ' it has data

I'm assuming here that the array is zero-based?

Hi Phil

I get subscript is out of range errors when I use the UBound presumably because the array is not dimensioned.

Here is the GetScenarioLog function

Function GetScenarioLog() As Variant
Dim SQL_Records As Recordset
Dim SQLCollection As New Collection
Dim HoldingArray() As Variant

Set objComm = New ADODB.Command
objComm.ActiveConnection = objConn
objComm.CommandText = "select sc_owner, sc_updated_date, sc_username, sc_change_log from olapsys.users_scenario_log where sc_scenario = '" & AttachedAW & "' and sc_owner = '" & AWOwner & "' order by sc_updated_date desc"
objComm.CommandType = adCmdText

Set SQL_Records = New ADODB.Recordset
SQL_Records.ActiveConnection = objConn
Set SQL_Records.Source = objComm

SQL_Records.Open

If Not SQL_Records.BOF And Not SQL_Records.EOF Then
    HoldingArray = SQL_Records.GetRows
End If

GetScenarioLog = HoldingArray
Set SQL_Records = Nothing

End Function

If no records are returned in the recordset, then the .GetRows function is not used, therefore the HoldingArray has no data assigned to it and hence the ChangeLogArray in the calling function is the same.

If for instance there is one row with four columns in the users_scenario_log table, then .GetRows function will populate the HoldingArray like so

HoldingArray(0,0) = sc_owner
HoldingArray(1,0) = sc_updated_date
HoldingArray(2,0) = sc_username
HoldingArray(3,0) = sc_change_log

Cheers

Ciaran

OIC its a dynamic array which hasn't been re-dimmed.

Looking at your code I think it would be better to change your function GetScenarioLog to return a boolean to indicate whether there are any records - if there are return them as a byref argument.

Alternatively you can redim the array to (0,0) if there are no records, then in your code check UBound(ChangeLogArray, 1)

hth
Phil

Just looked back at some previous code I've written which returns a GetRows array, and in that I returned Null if there were no records. Then in the calling function I just check If Not IsNull(returnVar) Then ' there is data

Just another alternative. Basically, dynamic arrays are a pain!

Hi Phil

Thanks for your comments

You mentioned that you returned Null if there were no records. Then in the calling function you just check If Not IsNull(returnVar)

How did you return Null, would you be able to post up your code so that I can see what you mean.

I firmly agree with your last statement - dynamic arrays are a pain!

Cheers for your help on this one!

Ciaran

Null is a recognised keyword in VB, so you could just do something like this:
rgds
Phil

Hi Phil

Thanks for your help

Cheers

Ciaran