How to add data from DataGrid into ComboBox list?

[Mas Juliza Alias]

Hi,
How to add the list of a combo box with a data in a table from data grid?
I have a data grid connected to a .mdb database in one form, and two combo boxes connected to the same database in another form. I am using Data Environment to connect the objects to the database. I've set the combo boxes' properties, DataSource, DataMember and DataField to the Data Environment, data Table and data Field respectively. The problem is, only the first data in the field is shown on both combo boxes' lists. How to add the rest of data into the drop down list?
(see attachment)
Thank you in advance.

[Guido Geurs]

I hope this code will help you:
It runs through the datagrid and collects first the unique dates in an array.
Once all the unique dates found, it add s them to the comboboxes.

Private Sub Command1_Click()
Dim ARRREC() As String
Dim ARRRECidx As Integer
Dim RECSET As ADODB.Recordset
Dim RECSETdate As String
Dim PRESENT As Boolean
   Set RECSET = DataEnvironment1.rsCommand1 '§ connect the recordset to the database
   ReDim ARRREC(0)
   '§ put unique dates in the array
   With RECSET
      Do Until .EOF
         RECSETdate = .Fields(0)
         PRESENT = False
         '§ check array if retreved date exist in array
         For ARRRECidx = 0 To UBound(ARRREC)
            If RECSETdate = ARRREC(ARRRECidx) Then PRESENT = True
         Next
         '§ if not present: add
         If PRESENT = False Then
            ReDim Preserve ARRREC(UBound(ARRREC) + 1)
            ARRREC(UBound(ARRREC)) = RECSETdate
         End If
         .MoveNext '§ go to next row
      Loop
      .MoveFirst '§ go back to top of list
   End With
   With frmComboBox
      '§ dump array in the comboboxes
      For ARRRECidx = 1 To UBound(ARRREC)
         .cboFromDate.AddItem ARRREC(ARRRECidx)
         .cboToDate.AddItem ARRREC(ARRRECidx)
      Next
      .Show
   End With
   Set RECSET = Nothing '§ clean memory
   Me.Hide
End Sub

PS:
it's better to collect the unique dates then to connect to the database and put all the dates in the combobox because the dates are more than once in the records.

[Mas Juliza Alias]

You are such a HERO! It works really well! Thanks a lot! :)

[Guido Geurs]

If you want to work with large DBfiles, it's better to put the DB in an array (much faster , only load once) like :

Private Sub Command1_Click()
Dim RECSET As ADODB.Recordset '§ recordset from database
Dim ARRrecsetRECORDSidx As Integer
Dim ARRdate() As String
Dim ARRdateIdx As Integer
Dim PRESENT As Boolean
'§ put database in array
   Set RECSET = DataEnvironment1.rsCommand1 '§ connect the recordset to the database
   ARRrecset = RECSET.GetRows
      '§ !! the records in the array are HORIZONTAL !! the attributes are VERTICAL
      '§ 1st counter (vertical) => attributes
      '§ 2nd counter (horizontal) => records
'§ put unique dates in the ARRdate
   ReDim ARRdate(0)
   '§ => run through records with 2nd counter
   For ARRrecsetRECORDSidx = 0 To UBound(ARRrecset, 2)
      PRESENT = False
      For ARRdateIdx = 0 To UBound(ARRdate)
         '§ attribute 0 = Date
         If ARRdate(ARRdateIdx) = ARRrecset(0, ARRrecsetRECORDSidx) Then PRESENT = True
      Next
      If PRESENT = False Then
         ReDim Preserve ARRdate(UBound(ARRdate) + 1)
         ARRdate(UBound(ARRdate)) = ARRrecset(0, ARRrecsetRECORDSidx)
      End If
   Next
'§ Fill the Comboboxes
   With frmComboBox
      '§ dump array in the comboboxes
      For ARRdateIdx = 1 To UBound(ARRdate)
         .cboFromDate.AddItem ARRdate(ARRdateIdx)
         .cboToDate.AddItem ARRdate(ARRdateIdx)
      Next
      .Show
   End With
   Set RECSET = Nothing '§ clean memory : records are in array ARRrecset
   Me.Hide
End Sub

To filter the data "Volume" and "Area" (from date in Combobox): run through array and put in textbox:

Private Sub CmdFindFromDate_Click()
Dim ARRrecsetIdx As Integer
'§ check combobox text
   If cboFromDate.Text = "" Then
      MsgBox "Select a date in the combobox"
      Exit Sub
   End If
'§ find Volume and Area with combobox text
   With TxtFromDate
      .Text = ""
      '§ !! the records in the array are HORIZONTAL !! the attributes are VERTICAL
      '§ => run through records with 2nd counter
      '§ 1st counter = vertical (0=Date, 1=Time, 2=Level, 3=Volume, 4=Area)
      '§ 2nd counter = horizontal (records)
      For ARRrecsetIdx = 0 To UBound(ARRrecset, 2)
         '§ attribute 0 = Date
         If ARRrecset(0, ARRrecsetIdx) = cboFromDate.Text Then
            '§ attribute 3 = Volume, attribute 4 = Area
            .Text = .Text & "Volume= " & ARRrecset(3, ARRrecsetIdx) _
                     & "   Area= " & ARRrecset(4, ARRrecsetIdx) & vbNewLine
         End If
      Next
   End With
End Sub

[Mas Juliza Alias]

In the code, which line did you assign the first attribute of the ARRrescet into ARRdate?

[Mas Juliza Alias]

I got it. Brilliant use of 'For' and 'If'. I would never thought of that kind of solution. Thank You...

[Guido Geurs]

Do you know how to work with "Add Watch" and "Watch Window" ?

[Mas Juliza Alias]

Never use it... Why?

[Guido Geurs]

It's a very handy tool in solving problems and to have a view in the structure of the components properties, var's and array's.
I will write a document explaining how it works.