Search code in a database
 

Hi Everybody,

Could somebody let me know the search code in a database (mdb database : with 1 table only) to write for a search command. I am using ADODC1 connection.

Please also let me know for the above table if i want to add a new contact to the table when i write contact name in a text box it should be automatically changed to upper case even i write in a lower case.

Have a nice day..

Note: I used this code for the search command but it did not work.

Private Sub Command8_Click()
Dim Searchvar As String
Dim sBookMark As String
Searchvar = InputBox("Enter the Name to find")
Searchvar = Trim$(Searchvar) 'remove surplus spaces
If Searchvar <> " " Then 'cancel if nothing entered
With Adodc1.Recordset
sBookMark = .Bookmark
.FindFirst "SName like '" + Searchvar + "*'"
If .NoMatch Then 'record not found
MsgBox "Hey! There is no such record in this Database
.Bookmark = sBookMark
End If
End With
End If
End Sub

When i tried this one there was an error: "Method or Data Member not found" and it highlights this string in the code : ".FindFirst"

user777

Re: Search code in a database
 

Why not just use DAO?

Much Cleaner in my opinion

Dim rst As DAO.Recordset

Dim db As Database

Set db = CurrentDb()

Set rst = db.OpenRecordset("select [field1],[field2] from table where [fieldID] = '" & Me.Something.Value & "'")

rst.Close

Set db = Nothing

End If

Re: Search code in a database
 

Obviously "FindFirst" is not supported (neither "NoMatch"). But let's simulate it:

    .MoveFirst

    .Find "SName like '" & Searchvar & "*'"

    If (.EOF) Then 'record not found

Re: Search code in a database
 

hi to all...

maybe this one will help...here it is:

********** this is an unbound control *************
***** create first and active connection ***********


Private Sub text1_KeyPress(KeyAscii As Integer)
dim RS as New ADODB.Recordset

If KeyAscii = 13 Then

set RS = new adodb.recordset
RS.open "select * from [tablename]",activeconnection,adOpenDynamic,
adLockOptimistic

with RS

.find "[fieldname] = '" & text1.text & "'", , adSearchForward

if not .eof then

msgbox "Record found."

else

msgbox "No Record."

endif

end with



End If

End Sub

Re: Search code in a database
 

hey guys you have answered my question thanks.....

Re: Search code in a database
 

what if i want the result to be displayed on the textbox/listbox, then if i choose the name, the other info about the name that is chosen will change also to their corresponding info.. what will i do in this given code?.. =) hope you can help me guys!.. tnx a lot in advance..