Searching records using a Combo box


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Searching records using a Combo box

  1. #1
    Ernie Guest

    Searching records using a Combo box


    I am tryimg to search the records in a table by a value I select in a combo
    box. I can populate the combo box with records from the name field using
    the ADO data control and binding the data combo box to it. I also created
    a text box that gets an address from the same table. I can't for the life
    of me get that address text box to update the address to the corresponding
    name I select with the combo box.

    I could accomplish it by coding instead of using ADO controls however, I
    run into a problem whenever a name has an apostrophe in it. It doesn't matter
    to me which way I do it I just need to get this done for it is a project
    I am working on for work and I have a demo deadline. Any help is appreciated.
    The following is a sample of how I accomplished what I want with code but
    that also has the flaw:

    Private Sub cmbName_Click()

    SiteName = Me.cmbName.Text

    'Query tblSite based on value in cmbSite and fill in text boxes
    SiteCmd.CommandText = "SELECT * FROM tblSite WHERE Name = " & "'" & SiteName
    & "'"
    SiteRS.CursorLocation = adUseClient
    SiteRS.Open SiteCmd, , adOpenDynamic, adLockOptimistic
    Me.txtAddress = SiteRS!Address
    Me.txtCity = SiteRS!City
    SiteRS.Close
    Set SiteRS = Nothing

    End Sub

    Private Sub Form_Load()

    OpenDB

    'Populate cmbSite with values from tblSite
    SiteCmd.CommandText = "SELECT * FROM tblSite"
    SiteRS.CursorLocation = adUseClient
    SiteRS.Open SiteCmd, , adOpenDynamic, adLockOptimistic

    Dim Index As Integer
    Dim Count As Integer

    Count = SiteRS.RecordCount

    SiteRS.MoveFirst

    For Index = 0 To Count
    If SiteRS.EOF <> True Then

    Me.cmbName.AddItem SiteRS!Name, Index
    SiteRS.MoveNext

    End If

    Next Index
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    SiteRS.Close
    Set SiteRS = Nothing

    End Sub

    I open my connections in a module.

  2. #2
    Arthur Wood Guest

    Re: Searching records using a Combo box


    Ernie, SQL recognizes a '' as a single ' for query purposes, SO...


    Use the Replace function in VB6 to replace ANY and ALL occurrences of ' in
    the original string with '', like this:


    SiteCmd.CommandText = "SELECT * FROM tblSite WHERE Name = " & "'" & Replace(SiteName,"'","''")
    & "'"

    and see it that solves the problem.

    Arthur Wood





    "Ernie" <krolloc@adelphia.net> wrote:
    >
    >I am tryimg to search the records in a table by a value I select in a combo
    >box. I can populate the combo box with records from the name field using
    >the ADO data control and binding the data combo box to it. I also created
    >a text box that gets an address from the same table. I can't for the life
    >of me get that address text box to update the address to the corresponding
    >name I select with the combo box.
    >
    >I could accomplish it by coding instead of using ADO controls however, I
    >run into a problem whenever a name has an apostrophe in it. It doesn't matter
    >to me which way I do it I just need to get this done for it is a project
    >I am working on for work and I have a demo deadline. Any help is appreciated.
    >The following is a sample of how I accomplished what I want with code but
    >that also has the flaw:
    >
    >Private Sub cmbName_Click()
    >
    >SiteName = Me.cmbName.Text
    >
    >'Query tblSite based on value in cmbSite and fill in text boxes
    >SiteCmd.CommandText = "SELECT * FROM tblSite WHERE Name = " & "'" & SiteName
    >& "'"
    >SiteRS.CursorLocation = adUseClient
    >SiteRS.Open SiteCmd, , adOpenDynamic, adLockOptimistic
    >Me.txtAddress = SiteRS!Address
    >Me.txtCity = SiteRS!City
    >SiteRS.Close
    >Set SiteRS = Nothing
    >
    >End Sub
    >
    >Private Sub Form_Load()
    >
    >OpenDB
    >
    >'Populate cmbSite with values from tblSite
    >SiteCmd.CommandText = "SELECT * FROM tblSite"
    >SiteRS.CursorLocation = adUseClient
    >SiteRS.Open SiteCmd, , adOpenDynamic, adLockOptimistic
    >
    >Dim Index As Integer
    >Dim Count As Integer
    >
    >Count = SiteRS.RecordCount
    >
    >SiteRS.MoveFirst
    >
    >For Index = 0 To Count
    > If SiteRS.EOF <> True Then
    >
    > Me.cmbName.AddItem SiteRS!Name, Index
    > SiteRS.MoveNext
    >
    > End If
    >
    >Next Index
    >''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    >
    >SiteRS.Close
    >Set SiteRS = Nothing
    >
    >End Sub
    >
    >I open my connections in a module.



  3. #3
    Ernie Guest

    Re: Searching records using a Combo box


    Thanks Arthur, that did help alot. Many thanks to you.

    "Arthur Wood" <wooda@nospam.com> wrote:
    >
    >Ernie, SQL recognizes a '' as a single ' for query purposes, SO...
    >
    >
    >Use the Replace function in VB6 to replace ANY and ALL occurrences of '

    in
    >the original string with '', like this:
    >
    >
    >SiteCmd.CommandText = "SELECT * FROM tblSite WHERE Name = " & "'" & Replace(SiteName,"'","''")
    >& "'"
    >
    >and see it that solves the problem.
    >
    >Arthur Wood
    >
    >
    >
    >
    >
    >"Ernie" <krolloc@adelphia.net> wrote:
    >>
    >>I am tryimg to search the records in a table by a value I select in a combo
    >>box. I can populate the combo box with records from the name field using
    >>the ADO data control and binding the data combo box to it. I also created
    >>a text box that gets an address from the same table. I can't for the life
    >>of me get that address text box to update the address to the corresponding
    >>name I select with the combo box.
    >>
    >>I could accomplish it by coding instead of using ADO controls however,

    I
    >>run into a problem whenever a name has an apostrophe in it. It doesn't

    matter
    >>to me which way I do it I just need to get this done for it is a project
    >>I am working on for work and I have a demo deadline. Any help is appreciated.
    >>The following is a sample of how I accomplished what I want with code but
    >>that also has the flaw:
    >>
    >>Private Sub cmbName_Click()
    >>
    >>SiteName = Me.cmbName.Text
    >>
    >>'Query tblSite based on value in cmbSite and fill in text boxes
    >>SiteCmd.CommandText = "SELECT * FROM tblSite WHERE Name = " & "'" & SiteName
    >>& "'"
    >>SiteRS.CursorLocation = adUseClient
    >>SiteRS.Open SiteCmd, , adOpenDynamic, adLockOptimistic
    >>Me.txtAddress = SiteRS!Address
    >>Me.txtCity = SiteRS!City
    >>SiteRS.Close
    >>Set SiteRS = Nothing
    >>
    >>End Sub
    >>
    >>Private Sub Form_Load()
    >>
    >>OpenDB
    >>
    >>'Populate cmbSite with values from tblSite
    >>SiteCmd.CommandText = "SELECT * FROM tblSite"
    >>SiteRS.CursorLocation = adUseClient
    >>SiteRS.Open SiteCmd, , adOpenDynamic, adLockOptimistic
    >>
    >>Dim Index As Integer
    >>Dim Count As Integer
    >>
    >>Count = SiteRS.RecordCount
    >>
    >>SiteRS.MoveFirst
    >>
    >>For Index = 0 To Count
    >> If SiteRS.EOF <> True Then
    >>
    >> Me.cmbName.AddItem SiteRS!Name, Index
    >> SiteRS.MoveNext
    >>
    >> End If
    >>
    >>Next Index
    >>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    >>
    >>SiteRS.Close
    >>Set SiteRS = Nothing
    >>
    >>End Sub
    >>
    >>I open my connections in a module.

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center