compile error: expected array.

[Bob H]

I got this code from an access forum and when I compile it I get an
error saying expected array.

This is a module:
Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

Set cn = CurrentProject.Connection

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the current database.

Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name


While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend

End Sub


Then this is a form:
' Declare a record type to break down the user info

Private Type UserRec
bMach(1 To 32) As Byte ' 1st 32 bytes hold machine name
bUser(1 To 32) As Byte ' 2nd 32 bytes hold user name
End Type

Private Sub Form_Open(Cancel As Integer)
Dim WhosOn As String

Me.LoggedOn.RowSource = WhosOn()<<<< Error at this line: Expected array

End Sub

Private Sub OKBtn_Click()

DoCmd.Close A_FORM, "frmLoggedOn"

End Sub

Private Sub UpdateBtn_Click()

Me.LoggedOn.RowSource = WhosOn()

End Sub

I am using Access 2007, but the code is for Access 2003.

Thanks

 

[Bob Quintal]

Change: Dim WhosOn As String

to : Dim WhosOn() As String

Then
Me.LoggedOn.RowSource = WhosOn()

will no longer throw the Error at this line: Expected array

Bob

 

[Bob H]

Dim WhosOn() As String

Thanks, but now the next line is throwing up and error:

Me.LoggedOn.RowSource = WhosOn() <<< Method or data member not found
LoggedOn is highlighted.


Thanks

 

[Bob Quintal]

Thanks, but now the next line is throwing up and error:

Me.LoggedOn.RowSource = WhosOn() <<< Method or data member not found
LoggedOn is highlighted.


Thanks

Looking back at your original post, you dim and populate the WhosOn()
array in the form open event sub, then exit that sub.
That puts the array variable out-of-scope, and Private Sub
UpdateBtn_Click() will never even know it existed.

Quick fix: move Dim WhosOn() to the declarations section of your
module.

 

[Bob H]

Looking back at your original post, you dim and populate the WhosOn()
array in the form open event sub, then exit that sub.
That puts the array variable out-of-scope, and Private Sub
UpdateBtn_Click() will never even know it existed.

Quick fix: move Dim WhosOn() to the declarations section of your
module.

Thanks, that worked a treat now.
Just out of interest, I added the said line : Dim WhosOn, after the
initial compile error, as it was not in the original code.

 

[Bob Quintal]

Thanks, that worked a treat now.
Just out of interest, I added the said line : Dim WhosOn, after
the initial compile error, as it was not in the original code.

Code taken from web sites or usenet may often contain shortcomings.

Actually, the Access Basic Editor has an option to require explicit
variable declarations, so if the original poster unchecked his copy,
the code could run on his machine. It can be forced on in individual
modules by putting Option Explicit at the top of the module, before
any sub-procedure, function, constant or variable declarations.

It's a smart thing to do as it makes debugging a lot easier, in my
experience.

If a variable is not dimensioned Access makes a best guess, but most
programmers agree tha the best isn't good enough.