Results 1 to 10 of 10

Thread: [RESOLVED] Select Multiple Records in Access

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2014
    Posts
    66

    Resolved [RESOLVED] Select Multiple Records in Access

    Hello All,

    I am using msflexgrid to showing user selected records from access.
    Code:
    rs.Open "Select Test from M_Test where investigationNo ='" & Text17.Text & "'", cn, 3, 2
    The value of Text17 is "2"... In access 5 rows having investigation no 2....

    It was selecting first row only....
    How can i select All rows (having investigation no 2)....

    Please Help me...

    Thank you..
    Attached Images Attached Images  

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Select Multiple Records in Access

    The query you are showing should return all those records that match, assuming of course that is a text field.
    Your image shows a lot of stuff that would not be returned by that query so not sure what you are doing.

  3. #3
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Select Multiple Records in Access

    The code could be set out like

    Code:
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    
    Private Sub Form_Load()
        Dim ConnectionString As String
        Set cn = New ADODB.Connection
         Set rs = New ADODB.Recordset
          cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
              "Data Source= " & App.Path & "\Database.mdb"
           cn.Open ConnectionString
            rs.CursorLocation = adUseClient
    End Sub
    
    Private Sub Form_Terminate()
        cn.Close
    End Sub
    
    Private Sub Command1_Click()
     rs.Open "M_Test", cn, adOpenKeyset, adLockPessimistic, adCmdTable
     ssql = "Select Test FROM M_Test where investigationNo = '" & Text17.Text & " '"
     Set rs = cn.Execute(ssql)
      MsgBox (rs.GetString)
    End Sub
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Apr 2014
    Posts
    66

    Re: Select Multiple Records in Access

    Thank You Mr.Nightwalker , It works great

    But how can i add records(5 records) into flexgrid???

    Thanks Again Sir..
    Attached Images Attached Images   

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Select Multiple Records in Access

    Can't read your code in the image, copy and paste the actual code rather than an image of it.

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Apr 2014
    Posts
    66

    Re: Select Multiple Records in Access

    Code:
    Private Sub Command3_Click()
    
    Set rs = New ADODB.Recordset
    
    rs.Open "M_Test", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    ssql = "Select Test FROM M_Test where investigationNo = '" & Text17.Text & " '"
    Set rs = cn.Execute(ssql)
    
    MSFlexGrid1.AddItem Label22.Caption & vbTab & (rs.GetString)
    
    End Sub
    It works..But All of the records assembled(combined) in one row in msflexgrid...
    Name:  flex.jpg
Views: 118
Size:  8.4 KB

    how can i get separate rows for each tests...
    Thank you

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Select Multiple Records in Access

    You would want to loop through the returned recordset
    Code:
    Do Until RS.EOF
        ' add the row to the grid using RS!Test  rather than .Getstring
        Rs.MoveNext
    Loop
    You also should not be using the RS.Open there as it is just a waste of time and space. You do nothing with it and then abandon it and create a new recordset with the same var. Leaving VB to deal with the orphaned recordset you opened using rs.open.
    Last edited by DataMiser; May 16th, 2014 at 01:25 AM.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Apr 2014
    Posts
    66

    Re: Select Multiple Records in Access

    Ohh..... thank you Mr.Miser....
    I forget it..

    Thanks agian

  9. #9
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    Re: Select Multiple Records in Access

    Quote Originally Posted by DataMiser View Post
    You also should not be using the RS.Open there as it is just a waste of time and space. You do nothing with it and then abandon it and create a new recordset with the same var. Leaving VB to deal with the orphaned recordset you opened using rs.open.
    It is interesting that it allows for the retrieval or updating of data despite opening the recordset.
    when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
    If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
    https://get.cryptobrowser.site/30/4111672

  10. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Select Multiple Records in Access

    Quote Originally Posted by Nightwalker83 View Post
    It is interesting that it allows for the retrieval or updating of data despite opening the recordset.
    The SET statement creates a new recordset with the results of the execute. The other recordset is still there but rs no longer points to it.
    Basically you end up with a chunk on memory allocated with data stuffed in it and no way to access it.

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