Hi everyone,
New to this forum (and VBA ) - but amazed by the amount of information it contains.

One thing I do miss though is topics about user-friendly interfaces...
OK - I know, a world without users etc etc :D but still... What's the point of having a database if no one uses it???

To me, the most important is to find the data I've stored in my DB, and I'm struckling with something as simple as a string search - and it drives me crazy :(

I'm running Access 2003 on XP pro.
Having a plain table with usernames etc as source, a form where I can see the information and all I ask for is a routine, that gives me a window with a line where I can write a name (o part of it) and then the form returns with that specific record.

From my form I double-click on the field [Nombre] and I call following Sub:

Public Sub BuscaUsuario()

Dim Dbs As Database
Dim Rst As DAO.Recordset
Dim BuscaString As String
Dim BNombre1 As String

BuscaString = ""
BuscaString = InputBox("You are looking for...?")

Set Dbs = CurrentDb
BNombre1 = "[Nombre]=" &BuscaString
Set Rst = Me.RecordsetClone
Rst.MoveFirst
If Rst.NoMatch Then
   MsgBox "No match!"
Else
   Me.Bookmark = Rst.Bookmark
End if
Rst.FindFirst (BNombre1)
Mr.Requery

Rst.Close
Dbs.Close

End Sub

I'm using the InBox because that's the only "Pop-Up" function I know in VBA.
I've tried to enter the SQL directly in the Properties for the form, also with a string variable, and very elegant pops-up a window in order to enter a value in the variable - but I can't make it works with VBA
Instead of = I'd like to use LIKE as users rarely know the whole name and sir-name of the person they are looking for.

Anyone here who can say the magic syntax I'm missing? :D

Txs for a great place to find info...
Don Herman

¡Bienvenidos a P2P, Don Herman!

Have you tried...?

BNombre1 = "[Nombre] = '" &BuscaString & "'"

Why? Because BuscaString is a string. They way you have it now, Access is expecting a number.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

¡ Y otro Bienvenidos!

To use LIKE:

BNombre1 = "((table.[Nombre]) LIKE " & "'" & BuscaString & "*'" & ")"

Access uses * as wildcard.

HTH,
Loralee

Oh, if you want to do a search within the string then might as well use the asterisks on BOTH sides...

BNombre1 = "[Nombre] Like '*" &BuscaString & "*'"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division