Hi all,

Moving on to ever increasing complexity and now I am stuck again.

Let me explain the problem. I have a form representing members of a group, which includes a MSHFlexGrid containing a list of all users in the database and below that I have a large textbox. When I double click on a user in the flexgrid it appears in the texbox below (Showing that user is add to the group. If there is more than one user choosen they are seperated by a (;) semicolon. When the user has finished selecting all the users they want in the current group they click "OK". When the OK button is clicked I SPLIT the contents of the textbox using the (;) as the delimiter. Obviously after I have an array with the contents of the textbox. What I want to do is add a new user to the group in the database. I need to FOR NEXT loop through them putting each one in the database. Sounds simple but before I do that I have to extract the UserID Number from the user table because this is the reference that is placed in the group database. e.g. GroupID and IDNumber

Here is my code so far:

---------------------------------------------------------------------------------------------------

Public strAdd As String

Private Sub Form_Load()

  ' Assign value
  rsClickCount = 0

  ' Create fixed row headers and widths
  MSHFlexGrid1.FormatString = "|User ||Description |"

  ' Set unwanted columns to o width
  MSHFlexGrid1.ColWidth(0) = 0
  MSHFlexGrid1.ColWidth(2) = 0
  MSHFlexGrid1.ColWidth(4) = 0
  MSHFlexGrid1.ColWidth(5) = 0

  ' Enable AllowBigSelection
  MSHFlexGrid1.AllowBigSelection = True

  ' Create an SQL Statement.
  Dim strSQL As String
  strSQL = "SELECT * FROM Users ORDER BY Name"

  ' Assign the ConnectionString to an ADO Data Control's
  ' ConnectionString property, and the SQL Statement to the
  ' control's RecordSource property.
  With Adodc1
    .ConnectionString = strCn
    .RecordSource = strSQL
  End With

  ' Set the HflexGrid control's DataSource property to the
  ' ADO Data control.
  Set MSHFlexGrid1.DataSource = Adodc1

  ' Refresh MSHFlexGrid
  Adodc1.Refresh
  MSHFlexGrid1.Refresh

End Sub
Private Sub cmdCancel_Click()

  'Unload form
  Unload frm_UserSelect

End Sub

Private Sub MSHFlexGrid1_DblClick()

  ' Declare variables
  Dim r As Integer
  Dim rsName As String
  Dim rsSelectOutput As String

  ' Assign variable and identify row selected
  r = MSHFlexGrid1.MouseRow

  If rsClickCount = 0 Or Text1.Text = "" Then
    Text1.Text = ""
    rsName = MSHFlexGrid1.TextMatrix(r, 1)
    rsSelectOutput = rsName
    rsClickCount = rsClickCount + 1
  Else
    rsName = MSHFlexGrid1.TextMatrix(r, 1)
    rsSelectOutput = "; " & rsName
    rsClickCount = rsClickCount + 1
  End If
  Text1.Text = Text1.Text & rsSelectOutput

End Sub

Function CreateArray(UserArray As Variant) As Variant
    UserArray = Split(strAdd, ";")
End Function

Private Sub cmdOK_Click()

  ' Check to see if user has been selected
  If Text1.Text = "<< Type names separated by semicolons or choose from list >>" Then
    ' Unload current window
    Unload frm_UserSelect
  End If
  If Text1.Text = "" Then
    ' Unload current window
    Unload frm_UserSelect
  Else
    Dim oRS As ADODB.Recordset
    strAdd = Trim$(Text1.Text)
    strAdd = Replace(strAdd, " ", "")
    txbTest.Text = strAdd
    CreateArray (UserArray)

    Dim r As Long
    For r = 0 To UBound(UserArray) <------------------------------------------ Problem starts here
      ' Create an SQL Statement that will extract IDNumber from
      ' database in relation to User name selected
      Dim strSQL As String
      strSQL = "SELECT IDNumber, Name FROM Users WHERE Name = '" & UserArray(r) & "'"

      ' Create Recordset and Populate Combo1.
      Dim rst As New ADODB.Recordset
      rst.Open strSQL, strCn
      rsIDNumber = rst!IDNumber
      rst.Close
      Set rst = Nothing

      ' Open database link
      oRS.Open "GroupMembers", strCn, adOpenKeyset, adLockOptimistic
      oRS.AddNew
      oRS.Fields("IDNumber") = rsIDNumber
      oRS.Fields("GroupID") = rsGroupSelected
      oRS.Update
      oRS.Close
    Next

  End If

  ' Close form
  Unload frm_UserSelect

  ' Empty array
  UserArray = Empty

End Sub

---------------------------------------------------------------------------------------------------

The problem comes when I try to open the array from a different sub routine. I am completely unsure of how you handle arrays as I am very new to this programming technique. Look through my code to see if I have structured the program properly in order to achieve a filled data base.

If anyone has any documentation on arrays this would be greatly received as I am trying deperately to understand its inner workings.

With many thanks for you time and assistance

Paul

hi ... sorry about my english
you didn't specify the problems throught the array ( the message is ) ??
check your StrAdd Vars that you access twice, why don't u manage that vars in the
same function that u created ( Replace ... ). check the ubound of UserArray that
belongs to Create_Array parameters. If u mean that your function returning result in array, check again the way u make it. Then try to loops from 1 to ubound(vArr)-1
Sorry i can't give u more than this, cause i'm in the net right now.
hope this usefull for u in concepting your scripts...bye