[RESOLVED] Query brings back two times the normal results

[vagos7]

Hello all! I'm using a query to join some fields from 2 tables but in one of the cases i'm doing it it brings back the results and repeats them, thus feeling twice the fexgrid.It's like(result1,result2,result3,result1,result2,result3)

The code for searching is as follows:

Private Sub supplier1()
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & App.Path & "\warehouses.mdb"

cn.Open
Dim strSQL As String

strSQL = "SELECT warehouse1.item_name,warehouse1.item_id,quantity,supplier_name,date_incoming ,id_deltioy, quantity_sum FROM warehouse1,Sum1 WHERE warehouse1.supplier_name='" & Text1.Text & "' AND Sum1.supplier='" & Text1.Text & "'"
Set rs = New ADODB.Recordset

rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Call FillFromRecordset_FlexGrid(FlexGrid1, rs, True)

FlexGrid1.Visible = True
Label2.Visible = True

rs.Close
Set rs = Nothing



Text1.Text = ""

End Sub

The code for the filling is as follows(thanks to si_the_geek)

Public Sub FillFromRecordset_FlexGrid(p_ctlFlexGrid As Control, _
                                      p_objRecordset As Object, _
                                      p_booFieldNamesAsHeaders As Boolean)
'Fill an MSFlexGrid control with values from a recordset
'By Si_the_geek, VBForums

'Parameters:
'  p_ctlFlexGrid            - The FlexGrid control to fill
'  p_objRecordset           - The Recordset to get the data from (can be ADO/DAO)
'  p_booFieldNamesAsHeaders - True to show field names as column headers

'Example usage:
'  Call FillFromRecordset_FlexGrid(MSFlexGrid1, objRS, True)


Dim lngCol As Long
Dim lngRow As Long
Dim booOldRedraw As Boolean

  With p_ctlFlexGrid
      'Set up the required number of columns
    .Cols = p_objRecordset.Fields.Count
    .FixedCols = 0

      'If specified, show field names as headers
    If p_booFieldNamesAsHeaders Then
      .FixedRows = 1
      For lngCol = 0 To p_objRecordset.Fields.Count - 1
        .TextMatrix(0, lngCol) = p_objRecordset.Fields(lngCol).Name
      Next lngCol
    End If

      'remove all rows except any headers, and the first data row (cannot be removed)
    .Rows = .FixedRows + 1

      'Turn off screen updates (much faster to fill the data)
    booOldRedraw = .Redraw
    .Redraw = False
      'Check if there is any data
    If p_objRecordset.EOF Then
        'if there is no data, only allow the required blank row, and hide it (height=0)
      .AddItem ""
      .RemoveItem .FixedRows
      .RowHeight(.FixedRows) = 0
    Else

        'We have data, add it one row at a time
        '(nb: there are various ways to do this, this way is quick, and easy to read)
      lngRow = .Rows
      Do While Not p_objRecordset.EOF
          'Add the row (empty)
        .AddItem ""
          'Set the values once cell at a time (avoids problems with Nulls and data containing grid delimiters)
        For lngCol = 0 To p_objRecordset.Fields.Count - 1
            'if you want to format the text for some columns differenly, you can _
            'use If/Else or Select Case here - but its better to do that in your SQL _
            'statement, as it is more efficient, and allows this sub to be re-used easily
          .TextMatrix(lngRow, lngCol) = p_objRecordset.Fields(lngCol).Value & ""
        Next lngCol
          'Increment our row counter
        lngRow = lngRow + 1
          'Move to the next row of data
        p_objRecordset.MoveNext
      Loop

        'Remove the blank row we left at the top
      .RemoveItem .FixedRows

    End If

      'Re-enable screen updates (if was previously enabled)
    .Redraw = booOldRedraw

      'Force a redraw of the grid
    .Refresh
  End With

End Sub

Thanks in advance for anyone that answers !

[GaryMazzone]

I think the problem is your SQL Query:


strSQL = "SELECT warehouse1.item_name,warehouse1.item_id,quantity,supplier_name,date_incoming ,id_deltioy, quantity_sum FROM warehouse1,Sum1 WHERE warehouse1.supplier_name='" & Text1.Text & "' AND Sum1.supplier='" & Text1.Text & "'"

Try like this:
strSQL = "SELECT warehouse1.item_name,warehouse1.item_id,quantity,supplier_name,date_incoming ,id_deltioy, quantity_sum FROM warehouse1 Inner Join Sum1 On warehouse1.field = Sum1 Field
WHERE warehouse1.supplier_name='" & Text1.Text & "' AND Sum1.supplier='" & Text1
.Text & "'"

The field needs to be the link between the two tables.

[vagos7]

Thanks for your help, worked like a charm !!