As you have stated in one of your previous message, SQL-Server doesn't know
nothing about the value of any controls in your frontend. So, when you are
dynamically building a sql string containing the value of a control, this
value must be put inside the sql string in the VBA code; before sending the
string to the sql-server. This is what I've done in my previous exemple:
s = "SELECT Clients.CliLN, Clients.CliFN, Clients.CliMI " _
& "FROM Clients WHERE (Clients.CliLN LIKE N'" _
& Replace (LEFT([Me!TextBox], 3), "'", "''") + "%') "
In this example, I've also took the precaution of doubling any embedded
single quote by using the replace function but you can remove it:
s = "SELECT Clients.CliLN, Clients.CliFN, Clients.CliMI " _
& "FROM Clients WHERE (Clients.CliLN LIKE N'" _
& LEFT([Me!TextBox], 3) + "%') "
I'm also assuming that Clients.CliLN is an unicode string (probably
nvarchar); hence the use of the N' before the constant in order to have a
comparaison between two unicode strings. The percent sign (%) is also added
locally in the VBA code; for example, notice the difference with:
s = "SELECT Clients.CliLN, Clients.CliFN, Clients.CliMI " _
& "FROM Clients WHERE (Clients.CliLN LIKE N'" _
& LEFT([Me!TextBox], 3) + "' + N'%') "
The best way of doing this is to display the sql string to make sure that
everything is OK. You can use debug.print to print it to the
debugging/immediate window and run it directly on the sql-server using
cut&paste.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
el zorro said:
Oops-- sorry. It must drive you nuts trying to help some of us idiots out
here...
I can't get that code to work, though. As a test, this code works, but it
returns people who's first names begin with the same 3 letters as their
last
name.
ClientList.RowSource = "SELECT CliLN, CliFN, CliMI" _
& "FROM dbo.Clients WHERE (CliLN LIKE LEFT(CliFN, 3) + N'%') " _
& "ORDER BY CliLN, CliFN, CliMI"
Not particularly useful, but if I could just figure out how to replace
"CliFN" in the LEFT statement with the string entered in my form TextBox,
I
think I'd be home free. But I've tried:
LEFT(Me!TextBox, 3) + N'%')
and it doesn't work. Am I hopeless?
Sylvain Lafontaine said:
The use of the message box here is only as a demonstrating/debugging
purpose
on how to correctly write the sql string that must be used as the
RowSource
of the control.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Thanks, Sylvain. I think your solution would show the user the names in
the
message box, but the user needs to select the name from the list box so
that
other things can happen programatically.
As it works now, a user looking for, say, "Martinez" user can navigate
through the list to all names beginning with the letter M, by typing M
in
the
list box control. I guess that's a basic Access feature. THe problem is
that
there might be 300 clients whose last name begins with M, so the user
scrolls
to locate the name-- and it's a lot of scrolling. I am trying to
replace
the
scrolling with a search feature-- let the user looking for "Martinez"
type
"Mar," to get closer to the spot on the list, and then find Martinez.
:
Write something like:
Dim s as string
s = "SELECT Clients.CliLN, Clients.CliFN, Clients.CliMI " _
& "FROM Clients WHERE (Clients.CliLN LIKE N'" _
& Replace (LEFT([Me!TextBox], 3), "'", "''") + "%') "
MsgBox s
ClientList.RowSource = s
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
The test message was "False". Maybe I'm going about this all wrong.
I
just
want to be able to search a list box on a form for last names that
begin
with
the three letters the user types into a text box on the form.
In Access mdb I could do this by referencing the text box inthe
query
underlying the List Box. But in adp/SQL Server, the view undertying
the
text
box does not seem to recognize input from the form. So, I'm trying
to
put
the
reference to the text box in the SQL statement that populates the
form,
but
it isn't working. As I say,I'mprobably going about this all wrong.
There
must
be a way to search a list box-- I'm sure people do this all the
time.
Any
thoughts?
:
Display the result of the sql string in a message box and you will
see
where
your error is.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
I'm stumped. I hae an adp front end connected to SQL Server back
end. I
have
a user form with a list box ("ClientList") that has a large
number
of
names
in it. I am trying to allow the users to type the name, or the
first
three
letters of the name, into an unbound text box ("TextBox") on the
form.
Then
they will hit a control button that will re-assign the RowSource
for
ClientList, giving them all the names that begin with the first
three
letters
they have entered. I have tried this code for the On Click event
of
the
control button:
ClientList.RowSource = "SELECT Clients.CliLN, Clients.CliFN,
Clients.CliMI
" _
& "FROM Clients WHERE (Clients.CliLN LIKE LEFT([Me!TextBox], 3) +
N'%')
"
But this doesnt work. Pls help! Thanks!
