search in text

[miziri]

Hi,
question two part :


1-I have filed call co_name type text ,I want when i enter any info it search ,if it find it show all other related info.like phone,address,code.
if that name is not there massage box show"that name not avaiable do want to add "

2-IF not found
permit to add as new record.



example":
I will enter into text in form like DKH when I enter autotical show me
phone,and address and code.



thanks

[maximus]

Let me get you straight you want to search text if result is found want to see it and if not want to enter a new record.

Study the sample that I attaching:

There are four names entered in the member table. typing one of these in the text box and clicking on check data will load the name on to the List Box. No I have set the Sql conditio to like so if you type m all names with m will appear. Coz when dealing with names or strings similarity is evitable. To make it specifin adjust strSql in the onclick event of Check data button as strSQL = "SELECT * FROM member WHERE member_Name= " & "'" & strCon & "';" doing this you have to type the name exactly how its has been entered.

Try typing Z or H or O as no records are found you will get a prompt to enter data.

Explanation:

I have created a recordset count the records if recort count is not equal to zero we use an sql string to populate the list box if zero msgbox is displayed.

Enter data uses sql to append (Insert data into member table) on inserting a new data the list box is populated with all the entries in the member table.

if this solves you problem mark this thread solved.

[miziri]

Hi Max,
I will see that attached file and see it ,I can see it it close to my idea but in my case
when enter cowboy check data it show it,in my case there other fileds related with cowboy like I want to show he dateofbirth and address too.
I will if I can modfity it second i do not want to appear in list in when appear in forms i mean when i enter name in text onclick or enter it show it or tell me not found u want add as new record.

thx brother

[maximus]

First of all I was very surprised that you were not able to modify the code to suit the requirement. The Concept was obiously very clear anyways here is what I have done.

1) Check code attached to the AfterEvent of Text Box Type User Name.
2) If Name is Found DOB and address is populated.

Here are the codes:

Private Sub Text0_AfterUpdate()
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intTable_RecordCount As Integer
Dim strCon As String
strCon = Me.Text0
strSQL = "SELECT * FROM member WHERE member_Name='" & strCon & "';"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not (rs.EOF) Then
rs.MoveLast
intTable_RecordCount = rs.RecordCount
Me.Text2 = rs!DOB
Me.Text4 = rs!Address
Me.Command6.Enabled = False
Else
MsgBox "Enter a New record"
Me.Command6.Enabled = True
End If

Set rs = Nothing

End Sub


Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim strSQL As String
strSQL = "Insert Into member(member_name,DOB,Address) Values(" & "'" & Me.Text0 & "'," & "#" & Me.Text2 & "#," & "'" & Me.Text4 & "');"
MsgBox strSQL
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True



Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub


Command button Insert Dat is only enabled if the recordset has a record count of zero. So you canno re-enter a data that is already entered.

Please Note: the Fields that can be displayed on the form depends on the strSQL string that you create so please if you had the Idea that only one field can be displayed, just change it.

Attaching the code on the onclick event is not a very good idea. But you are free to try. the code is attached to the afterupdate even of the text box and thus any action taken after you have entered the name will run the code. My suggestion press enter or click dob.


if this solves your problem mark this thread solved.

[miziri]

Hi Bhia Max,
I am from Iraq ,Many thnx for that code to be frank ith you
I add ask many peoples in Internet no one answer me that you u answer me now,
u realy understood my need .
friend last techical question?I MS access MDB file made with office 2003 wit its not work with with ms-access 2007.

thx

[maximus]

To the best of my knowledge You can run Access 2003 mdb file with Ms Access 2007. Infact access 2007 gives you the option to open an mdb file retaining its original format Like Access 95/97, Access 2000, Access 2003 and so on. You can also convert the it to an access 2007 accdb instructions below:

1. Open Microsoft Access 2007
2. Click the Microsoft Office button. This button (shown in the image on this page) appears in the upper left corner of the Access 2007 window.
3. Click Open.
4. Select the database you'd like to convert and open it.
5. Click the Microsoft Office button again.
6. Point to Save As.
7. Select Access 2007 Database from the section entitled "Save the database in another format".
8. Enter an approrpiate filename in the dialog box.
9. Click Save and your database will be saved in the new format.

if you have a mdb file Access 2003 conversion is not important as you can make modifications to database design. but it is absolutely necessary if you have mdb of Access 95/97.

So in short it will work if it doesn't let me know. read this article for more information.
http://technet.microsoft.com/en-us/l.../cc178973.aspx

[maximus]

if your problem is solved please mark this thread solved.