A simple DB Lookup/Search
Anita
combobox.
But I'm plagued by Type-Mismatch errors, etc.
This is my code:
Private Sub Command1_Click()
Dim strSQL As String
Dim DB As Database
Dim RS As Recordset
Set DB = OpenDatabase("C:\DB.mdb")
strSQL = "SELECT * FROM tblRecipients WHERE Recipient LIKE '*Text4.text*'"
'Joe Smith, etc
Set RS = DB.OpenRecordset(strSQL)
Text1.text = RS!Recipient
Text2.text = RS!Email
Text3.text = RS!Title
RS.Close
DB.Close
End Sub
Any suggestions appreciated.
ralph
>I'm struggling to perform a simple lookup based on a value selected in a
>combobox.
>But I'm plagued by Type-Mismatch errors, etc.
>
>This is my code:
>
>Private Sub Command1_Click()
>Dim strSQL As String
>Dim DB As Database
>Dim RS As Recordset
>Set DB = OpenDatabase("C:\DB.mdb")
>strSQL = "SELECT * FROM tblRecipients WHERE Recipient LIKE '*Text4.text*'"
>'Joe Smith, etc
Like uses the % wildcard character.
Also you need to replace the literal name of the control.property with
its value.
It is often easier (at least during intial development) to build your
search string separately:
Dim sSearch As String
sSearch = "%" & Trim(Text4.Text) & "%"
strSql = "SELECT * FROM customer WHERE customerName LIKE '" &
strSearch & "'"
-ralph
ralph
wrote:
Ooops!
Skip this ...
>Like uses the % wildcard character.
This is the real problem ...
>Also you need to replace the literal name of the control.property with
>its value.
Sorry, I often confuse myself over the d*mn wildcard differences (VB
Like, SQL Like, DAO, Access Matches, ADO, ... ), and often work with
both data access libraries at the same time forcing me to do something
like the following out of shear defense.
' set the wildcard character for library of interest
Dim sWild As String : sWild = "*" ' or sWild = "%"
Dim sFind As String
sFind = sWild & Trim(Control.Text) & sWild
strSql = "Select * From Table Where Field LIKE '" & sFind & "'"
On error I'll go back and just swap out wildcards till something fits.
<g> I did a quick modify to post my previous reply.
[That's my story, and I'm sticking to it.]
-ralph