Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Recordset (XP - VB6)

    Hi, I have tailored some code from a Access db for VB6 as I have been asked to make the application just one form that can sit on top of the desktop, without the main Access window etc.

    I am just enquiriing here about recordset grammar. I chose to use a datacontrol to link to the back end .mdb as wasn't completely sure on the CreateObject method etc.

    I have the following code at the mo:

    Private Sub txtSearch_Change()

    Dim strSearch As String, strLookup As String, intRec As Integer
    Dim rs As Object

    Set rs = Me.Data1.Recordset
    rs.MoveFirst
    strSearch = txtSearch.Text

    'clear list
    Do Until lstResults.ListCount = 0
    lstResults.RemoveItem (0)
    Loop

    'check for letter
    rs.FindFirst ("[SURNAME] LIKE '*" & strSearch & "*'")

    'if found then check for more
    If Not rs.NoMatch Then
    Do
    'get value from field (could get more info here
    strLookup = UCase(rs.Fields("Surname").Value & vbTab & " (" & _
    rs.Fields("First Name").Value & ") - " & rs.Fields("Company").Value)
    'add to list box
    lstResults.AddItem strLookup
    'look for next
    rs.FindNext ("[SURNAME] LIKE '*" & strSearch & "*'")
    'keep going till no more found
    Loop Until rs.NoMatch = True
    End If
    If lstResults.ListCount > 0 Then
    lstResults.Selected(0) = True
    lstResults.Selected(0) = False
    End If
    'rs.Close
    Set rs = Nothing

    End Sub

    This populates a listbox with any names in the back end table that have the text in the text box contained within. You can see I have commented out the

    rs.Close

    command as this was causing errors - closing the recordset that the datacontrol had opened when the form runs I think.

    Is this ok?
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Recordset (XP - VB6)

    You need to close a recordset if you open it. In this case, the txtSearch_Change procedure doesn't open the recordset, it only uses one that is already available. So this procedure should not try to close the recordset.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Recordset (XP - VB6)

    Thanks. Would you make a frontend in VB using a datacontrol to refer to the db or would you use the CreateObject method?
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VB Recordset (XP - VB6)

    The Data control has the advantage that you can set a lot of properties interactively in design time, instead of writing code. And you can bind other controls to it.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Recordset (XP - VB6)

    Cool, that makes sense. Thanks Hans. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    I know I am going to have some problems trying to do continous forms/datasheets in VB. Cross that bridge when i come to it <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>
    Thanks,

    pmatz

Posting Permissions

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