Search coding

[cavern]

hello everyone, i need a tips about "search coding"

im using adodc connection:

Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\sistempengurusanrumahsewa.mdb;Persist Security Info=False"
Adodc1.RecordSource = "Select * from Owner"
Set DataGrid1.DataSource = Adodc1

im trying to make "search" button to search a Name in my datagrid1.
can anyone guide me a search coding?
im confused.

below is my search coding, it doesnt work. :(

Private Sub cmdSearch_Click()

Adodc1.Recordset.DataSource
If Combo1.Text = "Name" Then
ds = "[Name] '" + txtName.Text + "'"
.FindFirst ds
 If .NoMatch Then
 MsgBox "no name"
 Else
 DataGrid1.Recordset.Filter = "[Name] '" & txtName.Text & "'"
 End If

End Sub

[cavern]

i've tried with this "search" coding:

Private Sub cmdSearch_Click()

Dim strMatNo As String
txtName = InputBox("Enter Name")
With Adodc1.Recordset
       .MoveFirst
       .Find "[Name] = '" & txtName & " ' "
       If .EOF Then
           .MoveFirst
           MsgBox "record not found"
       End If
End With

End Sub

my table contain Dateregistration,Title,Name,Nationality,Ic,Telnumber,Hpnumber,Owneraddress,Email
,Homeaddress,City,State,Gender,Numberofrooms,residential,Monthlypayment.

the problem now is how to search all information in my form?
it only shows Name.
anyone pls guide me.

[cavern]

and my textbox are

txtDate, txtTitle, txtName, txtNationality, txtIc, txtTel, txtHp, txtOwneraddress, txtEmail, txtHome, txtCity, txtState, txtGender, txtNumber, txtResidential, txtMonthly.
pls help me.

:(

[AndreRet]

Try the following -

Adodc1.RecordSource = "Select * from Owner WHERE YourNameFieldNameHere = '" & txtName.Text & "'"

'You can also try the following which will return all names starting with your search criteria...
Adodc1.RecordSource = "Select * from Owner WHERE YourNameFieldNameHere LIKE '" & txtName.Text & "%'"

[cavern]

sir, i dont understand, sory im beginner.
im trying to make "search" trough my adodc1 which will show all the information in my form.

[cavern]

sir, i dont understand, sory im beginner.
im trying to make "search" trough my adodc1 which will show all the information in my form.

[AndreRet]

Your code as below. Replace it with my code above where the RED is highlighted.:)

Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\sistempengurusanrumahsewa.mdb;Persist Security Info=False"
Adodc1.RecordSource = "Select * from Owner"
Set DataGrid1.DataSource = Adodc1

[cavern]

sir can u gv me an example, sory im confused. forgive me :(
did u mean replace the coding:

Adodc1.RecordSource = "Select * from Owner WHERE YourNameFieldNameHere LIKE '" & txtName.Text & "%'"

what is "WHERE YourNameFieldNameHere LIKE '" & txtName.Text & "%'" ?

[AndreRet]

It is part of your select statement. Change "YourNameFieldNameHere" with whatever name you have in your database table where the data must be searched. It looks like you have "Name" in your table as the field?

[cavern]

yes sir,
i have txtDate, txtTitle, txtName, txtNationality, txtIc, txtTel, txtHp, txtOwneraddress, txtEmail, txtHome, txtCity, txtState, txtGender, txtNumber, txtResidential, txtMonthly.

is it possible to search all of it?

[AndreRet]

You don't have to search all of them. Search one field will return the value of all other data in that field.

[cavern]

sir, can u check n correct my coding?
below is my coding :

Private Sub cmdSearch_Click()
On Error Resume Next
Dim key As Integer, str As String
key = InputBox("Enter the Student No whose details u want to know: ")
Set rs = Nothing
str = "select * from Owner where Dateregistration=" & key
rs.Open str, adoconn, adOpenForwardOnly, adLockReadOnly
txtDate.Text = rs(0)
cmbTitle.Text = rs(1)
txtName.Text = rs(2)
txtNationality.Text = rs(3)
txtIc.Text = rs(4)
txtAddress.Text = rs(5)

Set rs = Nothing
str = "select * from Owner"
rs.Open str, adoconn, adOpenDynamic, adLockPessimistic


End Sub

my problem is now how to "search" by name?
it only search for number(integer)
thank you sir.

[AndreRet]

I gave you the solution in the above posts, yet you have not used them.

This is what your code should look like -

Private Sub cmdSearch_Click()
On Error Resume Next
Dim key As Integer, str As String
key = InputBox("Enter the Student No to search for: ")
Set rs = Nothing
str = "SELECT * FROM Owner WHERE Dateregistration = '" & key & "'"
'I see you have used Dateregistration as a field to search from....
'but yet you are asking for a student number. Is this correct?????
'You can also use the following select statement -
'str = "SELECT * FROMOwner WHEREDateregistration LIKE '" & key & "%'"
rs.Open str, adoconn, adOpenStatic, adLockOptimistic
txtDate.Text = rs(0) 'Or use txtDate.Text = rs!Date (whatever your date field name is)
cmbTitle.Text = rs(1)
txtName.Text = rs(2)
txtNationality.Text = rs(3)
txtIc.Text = rs(4)
txtAddress.Text = rs(5)
 
rs.Close
'str = "select * from Owner" Remove this, this will reset your recordset
'rs.Open str, adoconn, adOpenDynamic, adLockPessimistic
End Sub

:)

[cavern]

sir i mean:

Private Sub cmdSearch_Click()
On Error Resume Next
Dim key As Integer, str As String
key = InputBox("Enter Name ")
' im searching for name to show all information in my textbox
' the problem is cant search for a name, it only search for integer.
Set rs = Nothing
str = "SELECT * FROM Owner WHERE Name = '" & key & "'"
'im looking for the Name in database to show all the information in my form.

rs.Open str, adoconn, adOpenStatic, adLockOptimistic
txtDate.Text = rs(0) 'Or use txtDate.Text = rs!Date (whatever your date field name is)
cmbTitle.Text = rs(1)
txtName.Text = rs(2)
txtNationality.Text = rs(3)
txtIc.Text = rs(4)
txtAddress.Text = rs(5)
 
rs.Close

End Sub

sir can u correct my coding so that able to search by name?
thank you sir.

[AndreRet]

Have a look at the code below. Please mark your other two posts as solved, they are duplications of this one. You will see at the bottom of the page there is a label "Solve This Thread".

Private Sub cmdSearch_Click()
On Error Resume Next
Dim key As String, str As String 'Change integer to string
key = InputBox("Enter Name ")
' im searching for name to show all information in my textbox
' the problem is cant search for a name, it only search for integer.
Set rs = Nothing
str = "SELECT * FROM Owner WHERE Name = '" & key & "'"
'im looking for the Name in database to show all the information in my form.
 
rs.Open str, adoconn, adOpenStatic, adLockOptimistic

'Also add error trapping, if there is no records available, it will generate an error
If rs.BOF = True Or rs.EOF = True Then
MsgBox "No Student found with that name."

Exit Sub
Else
txtDate.Text = rs(0) 'Or use txtDate.Text = rs!Date (whatever your date field name is)
cmbTitle.Text = rs(1)
txtName.Text = rs(2)
txtNationality.Text = rs(3)
txtIc.Text = rs(4)
txtAddress.Text = rs(5)
 
rs.Close
End If
 
End Sub

[cavern]

Thank you sir!!!
problem solved.

sorry for the double posting.
i'll mark it solved.
once again, thank you sir!! :) :)

[AndreRet]

It was a pleasure.:)