Searching records using a Combo box

[Ernie]

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.

[Arthur Wood]

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.

[Ernie]

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.
>