Ubound does not work

[Wysy]

Hi,
I have a modul that creates a dynamic array which is made public. The array works, contains the appropriate values.
However if i want to check ubound function it gives a result of 0. The array is not empty. Just curious why.
thank you

 

[The_Doc_Man]

Can you show us sample code of how you used it?

 

[Wysy]

Public Sub HorseSelectionForInvoice()
Dim hl As Recordset
Dim strHL As String

On Error Resume Next
Dim rc As Long

strHL = "SELECT HorseID FROM tbHorse WHERE Mark=-1;"
Set hl = CurrentDb.OpenRecordset(strHL)
hl.MoveLast
hl.MoveFirst
HLforInvoice = hl.GetRows(hl.RecordCount)
rc = hl.RecordCount - 1
End Sub

HLforInvoice array is made public. Then from another form

Public testarray()
    msgbox Ubound(HLforInvoice)
end sub

The result is 0 though the array is not empty, It is checked by

Public testarray2()
for i=0 to rc
    testarr=HLforInvoice(0,i)
    msgbox testarr
next i
end sub

This shows the expected values.

 

[theDBguy]

What do you get if you use UBound() in testarray2?

 

[Gasman]

Not played around much with arrays, but doesn't that last code show a 2 dimensional array, one row by many columns?

 

[Wysy]

also 0

 

[theDBguy]

also 0

What if you didn't use a public variable?

 

[Gasman]

Google shows

Recordset.GetRows method (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[Wysy]

Actually everything works fine, it is only ubound value that fails

 

[Gasman]

Actually everything works fine, it is only ubound value that fails

Have you looked at that link, that shows you how to get your value?

 

[Wysy]

yes sure and used several times. it is only i am trying to find out why the ubound function fails

 

[Gasman]

So what does your function look like now?

 

[The_Doc_Man]

Try UBound(HlForInvoice, 2) and see what THAT returns.

 

[Gasman]

What about the +1 Doc?

 

[Pat Hartman]

Is the array declared in a form or in a module? If it is in a form, it is only available when the form is open. Are you sure the array is loaded at the time you reference it?

 

[Galaxiom]

Is the array declared in a form or in a module? If it is in a form, it is only available when the form is open.

Plus it would need to be referred to via its form name if it is in a form module.

Do you have Option Explicit at the top of your modules? If you don't then the array on the module where you use UBound() would be instantiated as an empty array rather than an getting an error to say it doesn't exist when it can't be seen because you did not include the formname in the reference.

 

[The_Doc_Man]

What about the +1 Doc?

I may have misunderstood something. +1? In reference to what?

 

[gemma-the-husky]

if there is one element in a (zero based) array, then ubound will return 0, as the highest element

eg, an array {1,2} will return a ubound of 1, not 2, and the elements are arrayname(0) and arrayname(1)

 

[SHANEMAC51]

Массив HLforInvoice становится общедоступным. Затем из другой формы

Public testarray()
    msgbox Ubound(HLforInvoice,1)
    msgbox Ubound(HLforInvoice,2)
    
end sub

 

[arnelgp]

you can also Create yet another Public variable for the Ubound of array HLforInvoice:
on a Module:

Public HLUbound As Long


on your code:

Public Sub HorseSelectionForInvoice()
Dim hl As Recordset
Dim strHL As String

On Error Resume Next
Dim rc As Long

strHL = "SELECT HorseID FROM tbHorse WHERE Mark=-1;"
Set hl = CurrentDb.OpenRecordset(strHL)
hl.MoveLast
hl.MoveFirst
HLUbound = hl.RecordCount
HLforInvoice = hl.GetRows(hl.RecordCount)
rc = hl.RecordCount - 1
End Sub