Results 1 to 4 of 4

Thread: [RESOLVED] Searching for records show in a table?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    May 2005
    Location
    England
    Posts
    283

    Resolved [RESOLVED] Searching for records show in a table?

    I know how to search for records and display the result in textboxes but how do you display multiple results in a table, eg. MSFlexigrid

    Thanks

    GTJ

  2. #2
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Searching for records show in a table?

    Quote Originally Posted by greythej
    I know how to search for records and display the result in textboxes but how do you display multiple results in a table, eg. MSFlexigrid

    Thanks

    GTJ
    Try this:

    VB Code:
    1. Option Explicit
    2.  
    3. ' You will need an Access DB with Data
    4. ' Add a Reference to Microsoft Active X Data Objects 2.X Library
    5. ' Add a MSFlex Control, a Command Button and
    6. ' a Microsoft Common Dialog Control X.0
    7. ' to your form leave the default names
    8. ' Copy the following code into the code section of the form
    9.  
    10. Dim strDataBaseName As String
    11. Dim strDBCursorType As String
    12. Dim strDBLockType As String
    13. Dim strDBOptions As String
    14. Dim rs As adodb.Recordset
    15. Dim cn As adodb.Connection
    16. Dim a As Long
    17. Dim strSQL As String
    18. Dim strTable As String
    19.  
    20. Private Sub Command1_Click()
    21.     Call GetFile
    22. End Sub
    23.  
    24. Private Sub Command2_Click()
    25. On Error GoTo Command2_Click_Error
    26. Dim intMax As Integer
    27. Dim a As Integer
    28. Dim b As Integer
    29. Dim sUserInfo As String
    30.  
    31. strDBCursorType = adOpenDynamic  'CursorType
    32. strDBLockType = adLockOptimistic   'LockType
    33. strDBOptions = adCmdText         'Options
    34.  
    35. TheTop:
    36.  
    37. Set cn = New adodb.Connection
    38.  
    39. cn.Open ConnectString()
    40.    
    41.     With cn
    42.         .CommandTimeout = 0
    43.         .CursorLocation = adUseClient
    44.     End With
    45.  
    46.     Set rs = New adodb.Recordset       'Creates record set
    47.         strSQL = "SELECT * FROM Transactions;" 'Change SQL to Tables name
    48.         rs.Open strSQL, cn, strDBCursorType, strDBLockType, strDBOptions
    49.  
    50.     If rs.EOF Then
    51.         MsgBox "The criteria didn't return any records, try again."
    52.     Else
    53.         intMax = rs.Fields.Count - 1
    54.         MSFlexColumnSetup (intMax)
    55.    
    56.         With Me.StatusBar1
    57.             .Panels(1).AutoSize = sbrContents
    58.             .Panels(1).Text = rs.RecordCount & " - Total Records"
    59.         End With
    60.        
    61.         For a = 1 To rs.RecordCount
    62.             For b = 0 To intMax
    63.                 sUserInfo = sUserInfo & rs.Fields(b).Value & vbTab
    64.             Next b
    65.                
    66.             Me.MSFlexGrid1.AddItem sUserInfo
    67.             rs.MoveNext
    68.             sUserInfo = vbNullString
    69.         Next a
    70.     End If
    71.  
    72. rs.Close
    73. Set rs = Nothing
    74. cn.Close
    75. Set cn = Nothing
    76.  
    77. On Error GoTo 0
    78. Exit Sub
    79.  
    80. Command2_Click_Error:
    81.     If Err.Number = -2147467259 Then
    82.         MsgBox "Make sure the the MSFlex Database File is in the Path of the Application:" & vbCrLf & vbCrLf & _
    83.                         App.Path, vbCritical, "File not found"
    84.    
    85.     GetFile
    86.     GoTo TheTop
    87.     Else
    88.         MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command2_Click of Form " & Me.Name
    89.     End If
    90. End Sub
    91.  
    92. Private Sub Command3_Click()
    93.     Unload Me
    94. End Sub
    95.  
    96. Private Function ConnectString()
    97.                
    98.     ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    99.                     "Data Source=" & strTable & _
    100.                     ";Jet OLEDB:Engine Type=5;"
    101.  
    102. End Function
    103.  
    104. Private Sub Form_Load()
    105. On Error GoTo Form_Load_Error
    106.  
    107.     strTable = App.Path & "\MSFlex.mdb"
    108.    
    109.     With Me.StatusBar1.Panels
    110.         .Add
    111.         .Item(2).AutoSize = sbrSpring
    112.         .Item(2).Text = "Target Database: " & Mid(strTable, InStrRev(strTable, "\") + 1)
    113.     End With
    114.  
    115.     With Me.MSFlexGrid1
    116.         .FixedCols = 0
    117.         .FixedRows = 1
    118.         .Rows = 1
    119.         .Cols = 1
    120.     End With
    121.    
    122.     Me.Command1.Caption = "Find Database"
    123.     Me.Command2.Caption = "Load FlexGrid"
    124.     Me.Command3.Caption = "Exit"
    125.    
    126.    
    127.     Call FormResize
    128.     Me.WindowState = vbNormal
    129.  
    130. On Error GoTo 0
    131. Exit Sub
    132.  
    133. Form_Load_Error:
    134.  
    135. MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of Form " & Me.Name
    136. End Sub
    137.  
    138. Private Sub Form_Resize()
    139.     Call FormResize
    140. End Sub
    141.  
    142. Private Sub FormResize()
    143. Dim sglTop As Single
    144.  
    145.     With Me.Command1
    146.             .Move 100, 10
    147.             Me.Command2.Move .Left + .Width, 10
    148.             Me.Command3.Move Me.Command2.Left + Me.Command2.Width, 10
    149.             sglTop = .Top + .Height
    150.     End With
    151.    
    152.     If Me.WindowState <> vbMinimized Then
    153.         With Me.MSFlexGrid1
    154.             .Move 0, sglTop, Me.ScaleWidth, Me.ScaleHeight - sglTop
    155.         End With
    156.     End If
    157. End Sub
    158.  
    159. Private Sub GetFile()
    160.    
    161.     With Me.CommonDialog1
    162.         .InitDir = App.Path
    163.         .FileName = strTable
    164.         .DialogTitle = "Select Sample Database"
    165.         .CancelError = False
    166.         .Filter = "Access Database (*.MDB)|*.mdb"
    167.         .ShowOpen
    168.     End With
    169.    
    170.     strTable = Me.CommonDialog1.FileName
    171.    
    172.     With Me.StatusBar1.Panels
    173.         .Item(2).Text = "Target Database: " & Mid(strTable, InStrRev(strTable, "\") + 1)
    174.     End With
    175.  
    176. End Sub
    177.  
    178. Private Sub MSFlexColumnSetup(intNumCol As Integer)
    179. Dim a As Integer
    180.  
    181.     With Me.MSFlexGrid1
    182.         .Cols = .Cols + intNumCol
    183.  
    184.         For a = 0 To intNumCol
    185.             .Row = 0
    186.             .Col = a
    187.             .ColWidth(a) = 2000
    188.             .Text = rs.Fields(a).Name
    189.         Next a
    190.     End With
    191. End Sub
    Last edited by Mark Gambo; Jun 23rd, 2005 at 09:53 PM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  3. #3
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Searching for records show in a table?

    I attached a complete project including an Access DB
    Attached Files Attached Files
    Last edited by Mark Gambo; Jun 23rd, 2005 at 09:50 PM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  4. #4
    VB Addict Pradeep1210's Avatar
    Join Date
    Apr 2004
    Location
    Inside the CPU...
    Posts
    6,614

    Re: Searching for records show in a table?

    Quote Originally Posted by greythej
    I know how to search for records and display the result in textboxes but how do you display multiple results in a table, eg. MSFlexigrid

    Thanks

    GTJ
    The flexgrid is not data-aware. Instead use MSHFlexGrid control to show data directly from database.
    This is the simplest way to populate the grid:
    VB Code:
    1. Private Sub cmdSearch_Click()
    2.     rs.Filter = "Field1='value1'"  '<--etc. Replace with your search condition here. Apply the filter
    3.     Set MSHFlexGri1.Recordset = rs
    4. End Sub

    Pradeep
    Pradeep, Microsoft MVP (Visual Basic)
    Please appreciate posts that have helped you by clicking icon on the left of the post.
    "A problem well stated is a problem half solved." Charles F. Kettering

    Read articles on My Blog 101 LINQ Samples JSON Validator XML Schema Validator "How Do I" videos on MSDN VB.NET and C# Comparison Good Coding Practices VBForums Reputation Saver String Enum Super Simple Tetris Game


    (2010-2013)
    NB: I do not answer coding questions via PM. If you want my help, then make a post and PM me it's link. If I can help, trust me I will...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width