MSFlexgrid Data Retrieval With ADO....

[vaishali]

Hi All,

For Saving a Form i have to MAke Two Tables in Database.
Inward_local & Inward_item as One to MAny Relationship.

While in TAble 1 Primary Key = Receipt_no
While in Table 2 i made Receipt_no + Sr_no = Primary Key

For Item_details I Have used MS Flexgrids. Working Fine For Save MEthod Using Textmatrix

am Fetching Records With ADO & Displaying on Form Working Fine

Problem :

How do I fetch Records From Flexgrids against Particular Receipt_no

Pls Guide

Thanks

[si_the_geek]

I'm not too clear on what you are trying to acheive, could you explain in more detail?

[vaishali]

i Have attached a form,
How to Open a Record on a Form Using ADO

as I have two tables in Access databases for Storing Database

so How di Open a Reocrd on A Form Using ADO
How do i fill Flexgrid with Data in Table

Ur Help is Highly Apperciicated

[dee-u]

Haven't looked into your attachment but in your sql you could add the WHERE Clause so that only records that meets your condition would be returned...

[ganeshmoorthy]

i couldnt open the attachement, i have some problem of downloading into my system. if you provide the code here i might help you

[ganeshmoorthy]

specify the msflex.col and .row of the msflex and use .text method to display the value into the grid.

ex :
do while not rs.eof
msflex1.col=1
msflex1.row=1
msflex1.text=rs!ecode
msflex1.col=2
msflex1.row=1
msflex1.text=rs!ename
msflex1.col=3
msflex1.row=1
msflex1.text=rs!desig
rs.movenext
loop
and so on

[vaishali]

Hi Ganesh,

rs.Open "Item_desc_local", cn, adOpenKeyset, adLockPessimistic, adCmdTable 'opening the recordset explained in the notes
 
    'For X = 1 To (MDIchild3.MSF7.Rows)
    X = 1
    MSF7.TextMatrix(X, 0) = rs.Fields("Receipt_no")
    MSF7.TextMatrix(X, 1) = rs.Fields("Sr_no")
    MSF7.TextMatrix(X, 2) = rs.Fields("Item_code")
    MSF7.TextMatrix(X, 3) = rs.Fields("Item_name")
   MSF7.TextMatrix(X, 4) = rs.Fields("Item_type")
    MSF7.TextMatrix(X, 5) = rs.Fields("Item_class")
    MSF7.TextMatrix(X, 6) = rs.Fields("Product_code")
    MSF7.TextMatrix(X, 7) = rs.Fields("Product_srno")
    MSF7.TextMatrix(X, 8) = rs.Fields("Case_no")
    MSF7.TextMatrix(X, 9) = rs.Fields("Tot_pkgs")
    MSF7.TextMatrix(X, 10) = rs.Fields("Tot_qty")
    MSF7.TextMatrix(X, 11) = rs.Fields("Item_location")
    MSF7.TextMatrix(X, 12) = rs.Fields("Unit_cost")
    MSF7.TextMatrix(X, 13) = rs.Fields("Total_cost")
    MSF7.TextMatrix(X, 14) = rs.Fields("Received_by")
    MSF7.TextMatrix(X, 15) = rs.Fields("Checked_by")
    MSF7.TextMatrix(X, 16) = rs.Fields("Warehouse_name")
    MSF7.TextMatrix(X, 17) = rs.Fields("Remarks")
 
    Next
    'MSF7.Rows = MSF7.Rows + 1

Its Working Fine for First Row then but its not working
But How Do i Check Condtion
Because, Receipt_no+ Sr_no # Primary Key

Receipt_no = 1+ (SrNo) 1 One Row
1 +(Srno) 2 IInd Row
1 +(Srno) 3 III rd Row

So How Do i Check the False Condition

False Condtion When Receipt_no Changes

Thanks

[ganeshmoorthy]

i hope you have to increase x=x+1
and set the row=row+1
i couldnt understand the last

Receipt_no = 1+ (SrNo) 1 One Row
1 +(Srno) 2 IInd Row
1 +(Srno) 3 III rd Row

please explain

[ganeshmoorthy]

put all your display code in the loop and let me know

[vaishali]

am Saving Database in TWo Tables
ie. Inward_local Receipt_no # P Key
Inward_items Receipt_no & Sr_no -> # P Key

Invoice Details are Storing in Inward_local Database
While Item_details are Storing in Inward_items as Aganist one Receipt_no Many Items Can Inwarded. One to Many Relationship.

i Wants to Fetch the Records From Inward_items Into Flexgrids to the Correspodning Receipt_no Where Receipt_no & Sr_no

Suppose Against Receipt_no There 3 Items are Inwarded

3 rows should be filled

i Used A Fillflex() Function for that

[ganeshmoorthy]

use the for loop like

ictr=rs.recordcount

for i=1 to ictr

n

[ganeshmoorthy]

use the for loop like

x=rs.recordcount
msf7.row=1

for i=1 to x
MSF7.TextMatrix(X, 0) = rs.Fields("Receipt_no")
MSF7.TextMatrix(X, 1) = rs.Fields("Sr_no")
MSF7.TextMatrix(X, 2) = rs.Fields("Item_code")
MSF7.TextMatrix(X, 3) = rs.Fields("Item_name")
MSF7.TextMatrix(X, 4) = rs.Fields("Item_type")
MSF7.TextMatrix(X, 5) = rs.Fields("Item_class")
MSF7.TextMatrix(X, 6) = rs.Fields("Product_code")
MSF7.TextMatrix(X, 7) = rs.Fields("Product_srno")
MSF7.TextMatrix(X, 8) = rs.Fields("Case_no")
MSF7.TextMatrix(X, 9) = rs.Fields("Tot_pkgs")
MSF7.TextMatrix(X, 10) = rs.Fields("Tot_qty")
MSF7.TextMatrix(X, 11) = rs.Fields("Item_location")
MSF7.TextMatrix(X, 12) = rs.Fields("Unit_cost")
MSF7.TextMatrix(X, 13) = rs.Fields("Total_cost")
MSF7.TextMatrix(X, 14) = rs.Fields("Received_by")
MSF7.TextMatrix(X, 15) = rs.Fields("Checked_by")
MSF7.TextMatrix(X, 16) = rs.Fields("Warehouse_name")
MSF7.TextMatrix(X, 17) = rs.Fields("Remarks")
msf7.row=msf7.row+1
next

[vaishali]

Dear Ganesh,

For i=1 to X

Where X is Not all the Record in A Table
but X = Suppose Receipt_no 1 Has 5 Items ie
Receipt_no 1 SerialNo 1
Receipt_no 1 SerialNo 2
Receipt_no 1 SerialNo 3
Receipt_no 1 SerialNo 4
Receipt_no 1 SerialNo 5

Recordcount will give all records but i want only Records Against Corresponding Receipt_no 1
When Receipt_no 2 Then Condition should fail

Yes Sir,

[ganeshmoorthy]

for that case use the sql query like

select * from <tablename> where <fieldname>=<value>

so that it will return only the 5 records which satisfies the conditio, then you can use the x=rs.recordcound method.

could you show ur sql statement so that i can help

[vaishali]

[Highlight=VB]
Select * from Inward_items Where Receipt+no = txtreceipt_no.text
[Highlight=VB]

But How do i Show in Flexgrid

[si_the_geek]

Here's a Sub I recently wrote, you can just put this into your project and use it as in the "'Example usage" comments.

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

[vaishali]

Thanks Super Moderator

I will Do it........