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

I am sorry, there are so many errors that is not easy where to start...
I will try to give some hints.

First of all remove the declaration of strAdd, it is useless.
The correct syntax for CreateArray is:

Private Function CreateArray(sText as string) as Variant
   CreateArray = Split(sText, ";")
end function

Note that the name of the function is what the function returns.
To use it :

dim s as Variant
for each s in CreateArray(Text1.Text)
   '' use Trim(s) to discard spaces
   if len(trim(s)) > 0 then ''' this fixes empty strings caused by ";;"
      strSQL = "SELECT IDNumber, Name FROM Users WHERE Name = '" & Trim(s) & "'"
next

Using 'for each' avoids ubound/lbound problems.
Last, in the MSHFlexGrid1_DblClick event you use rsClickCount that is not defined (at least in the code you posted), but you can avoid it:

  If len(trim(Text1.Text)) = 0 Then
    Text1.Text = MSHFlexGrid1.TextMatrix(r, 1)
  Else
    Text1.Text = Text1.text & "; " & MSHFlexGrid1.TextMatrix(r, 1)
  End If

I just saw another one: use 'Unload Me' instead of specifing the form name. If the form was created dinamically like
   dim frx as New frm_userSelect
   frx.Show
the 'Unload frm_userSelect' has no action.

Hope this helps,
Marco

Marco,

OK I have modified my code and works great, Many thanks. One last thing I want to do is refresh the ListView in the previous window to show that the users have been added to the group.

The previous form consists of the following code:
--------------------------------------------------------------------
Option Explicit

Private Sub Command1_Click()

  ' Reset tabs
  TabStrip1.Tabs(1).Selected = True

  ' Close window
  Unload Me

End Sub

Private Sub Command2_Click()

  ' Reset tabs
  TabStrip1.Tabs(1).Selected = True

  ' Close window
  Unload Me

End Sub

Private Sub Command4_Click()

frm_UserSelect.Show

End Sub

Private Sub Form_Load()

  ' Create an SQL Statement.
  Dim strSQL As String
  strSQL = "SELECT * FROM Groups WHERE GroupID = " & rsGroupSelected & ";"

  ' Declare variables
  Dim rst As New ADODB.Recordset

  ' Open recordset utilizing SQL statement
  rst.Open strSQL, strCn

  ' Populate textboxes and label with data
  rst.MoveFirst
  Text1.Text = rsGroupSelected
  Text2.Text = rst!Channel
  Text3.Text = rst!Description
  Label1.Caption = rst!Name
  frm_Groups.Caption = rst!Name & " Properties"


  ' Create an SQL Statement.
  Dim strSQL2 As String
  strSQL2 = "SELECT Groups.GroupID, Groups.Name AS Groups_Name, Groups.FullName," _
          & " Groups.Channel, Groups.Description, Groups.Notes, GroupMembers.IDNumber," _
          & " Users.Name AS Users_Name" _
          & " FROM Users INNER JOIN (Groups INNER JOIN GroupMembers ON Groups.GroupID = GroupMembers.GroupID) ON Users.IDNumber = GroupMembers.IDNumber" _
          & " WHERE (((Groups.GroupID)=" & rsGroupSelected & "));"

  ' Declare variables
  Dim rsp As New ADODB.Recordset
  Dim li As ListItem

  ' Open recordset utilizing SQL statement
  rsp.Open strSQL2, strCn, adOpenStatic, adLockBatchOptimistic

  ' Populate ListView1 with data
  rsp.MoveFirst
  Do
    Set li = ListView1.ListItems.Add(1, , rsp!Users_Name & "", , 1)
  rsp.MoveNext
  Loop Until rsp.EOF
  rsp.Close
  Set rsp = Nothing

  ' Set default visibility settings
  Picture1.Visible = True
  TabStrip1.Tabs(1).Selected = True

End Sub

Private Sub ListView1_click()

Command5.Enabled = True

End Sub
-------------------------------------------------------------------

What commands in the select users window to do I use to refresh the recordset and listview in this window.

I thought it might be something like:

frm_Groups.Recordset.refresh
frm_Groups.ListView1.refresh

But the problem is with the recordset as it has a ADODB.Recordset name of "rsp".

Also should allow a little time to allow the Database job to be completed before refreshing, or it will refresh before the job is complete without showing a change.

Any further advice on this matter would be greatly received.

Many thanks

Paul

move the code that populates the listview from the Load event to a local method like
Private Sub LoadData()
and call it from the Load event.
Show frm_userSelect modally, and when done refresh the listview:

frm_useSelect.Show vbModal
LoadData

Marco

Worked great Marco, Thanks very much for all your help