Searching in the form

[seeter]

Hi, at first I'm sorry for my English but about the problem. I have a form and column with a serial number and other informations. I want make form this way: when I change part of serial number it will immediately find rest of information in the form. Thank you.

[maximus]

well certainly possible there are many ways to do it using RedcordSets or Dlookup. I can tell u more specifically if will provide me with more details.

[azal]

one of the ways (not the best) is this... hope it helps..

[seeter]

That's it azal, thank you. Just tell me how did you do that...

[azal]

you will need a form, I called it "searchForm" with text box contral called it searchBox, and a subform named "Customer" bounded to tabel or query that holds the data I want to filter.

then in the event searchBox_Onchange() but this code:
Dim myFormFilter As String
Me.Customer.Form.FilterOn = True
If Me.SearchBox.Text = "'" Then
Me.SearchBox.Text = ""
End If
myFormFilter = "[name] Like '*" & Me.SearchBox.Text & "*'"
Me.Customer.Form.Filter = myFormFilter

and that is it....

you might need only one form to do the job, but the problem Onchange event repostion the cursor (set the focus) to the begging of the text while you typing, i haven't spent eanough time to solve that. but I am sure there is a way.

good luck

[seeter]

Thank you, now I understrand. I would like to ask you for something else. This number, I am searching, consist of two parts, it is like this: 123456 / 2. This number in the table is divided into two columns and in the form it search just in the first column. I would like make it, that in the search box you find number 123456 and in the second box or field with the list of all numbers which belong to the first number. Like this:
123456/1
123456/3
123456/8 ...
thank you.

[azal]

Ok, if got you, you want something like that; while you are typing 12.. it displays all the numbers starts with 12 and its second part /1 or /2 etc..

If so you need only to change the filter statement to search for the number statrs with what you typing be using one star * at the begining.

Another way to do somthing like this, Concatenate the two columns in the query in one like that:
full number: firstPart & secondPart
it will result in one number like 123456/2

then use the previous attached search.

if needed I'll do it and attach it..

[seeter]

Not exactly. I changed searching filter instead of [name] to [No1], it's a first number. But I want this: When I type in the first box that first number (No1) it will make a list of all numbers starting wit for ex. 123456 but having different second number (No2) for ex. /1, /2, /4,.../8. And when i type second number in the second box, for ex. /2, it will show information about number 123456/2. It is searchind according to two numbers. I modified your database and added some numbers in the table. Thank you for reply.

[azal]

I see.....


Private Sub SearchBox_Change()
Dim myFormFilter As String
Me.Customer.Form.FilterOn = True

myFormFilter = "[No1] Like '" & Me.SearchBox.Text & "*'"
Me.Customer.Form.Filter = myFormFilter

End Sub
Private Sub SearchBox2_Change()
myFormFilter = "[No2] Like '" & Me.SearchBox2.Text & "'"
Me.Customer.Form.Filter = myFormFilter
End Sub
======

copy the code above and replace the existing code in the form you sent to me.

what i did is the following:
==added SearchBox2_change()
==removed the first star * from SearchBox_Change() why??
because the star is used like this:
* text : means find a word ends with text
text*: find a word starts with text
*text* : find a word contains text
text (without star): find a word exactly like text



in your case you want a record where NO1 starts with 123456 (example)
so that, I used star at the end
and you want a recortd where NO2 = 1 (example)
so that i used No star.

good luck

[seeter]

Hi,
I tried your code, but it is not still working. When I use your code, it's like that two SearchBoxes are independent. When I type first number 123456 it find out all entries starting with same number, but different the other. Like 123456/1 , 123456/4, 123456/8 ... But when i type in second Search Box it find out all entries with seme second number. Like 123456/2, 353624/2 , 492957/2 ... But these two search boxes are not independent. When I use first search box I find out set of entries with the same first number, for ex: 123456 and using second search box i choose one entry from this set of entries. It means that i need combine second search box with that first. When I type some number in second one there is still valid condition from first search box. I tried to use some logical expressins like AND or + and I tried use it in code, but it didn't work. I wrote it wrong way at last reply, so I am sorry. Thank you for help.

[maximus]

I have a table Table 1 with two Fields Id and ID 2. Number in the format like 2435/2 is stored in this two Field left of / in ID an Right of / in ID_2

I have two text boxes in my form. I have used then to filter my form.I type the fist part in Text8 and the secon in Text10.(Typing / is not required as that is included in the code).

You can Filter the form:
1) Using the First Number
2) Using the second number
3) Both of thenm together


Private Sub Command12_Click()
On Error GoTo Err_Command12_Click
Dim strCriteria As String

If Not IsNull(Me.Text8) Then
If IsNull(Me.Text10) Then
strCriteria = "ID='" & Me.Text8 & "'"
End If
End If
If Not IsNull(Me.Text10) Then
If IsNull(Me.Text8) Then
strCriteria = "ID2='/" & Me.Text10 & "'"
End If
End If
If Not IsNull(Me.Text8) Then
If Not IsNull(Me.Text10) Then
strCriteria = "ID='" & Me.Text8 & "' And ID2='/" & Me.Text10 & "'"
End If
End If
If IsNull(Me.Text10) Then
If IsNull(Me.Text8) Then
MsgBox "Please Type at least one of the criteria"
End If
End If

If Len(strCriteria) > 0 Then
DoCmd.ApplyFilter , strCriteria
End If



Exit_Command12_Click:
Exit Sub

Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click

End Sub

I am attaching a sample. Check form Table1

[seeter]

Well, I used part of your code to modify azal's. But when I type second number all entries disappear and when I delete second number suddenly the entry appears. So i decided there is problem with updating filter. Could you help me? Thank you...


Option Compare Database
Private Sub exitFilter_Click()
Me.Customer.Form.FilterOn = False
Me.SearchBox.SetFocus
Me.SearchBox.Text = ""
Me.SearchBox2.Text = ""
End Sub

Private Sub SearchBox_Change()
Dim myFormFilter As String
Me.Customer.Form.FilterOn = True

myFormFilter = "[No1] Like '" & Me.SearchBox.Text & "*'"
Me.Customer.Form.Filter = myFormFilter

End Sub

Private Sub SearchBox2_Change()

Dim myFormFilter As String

Me.Customer.Form.FilterOn = True

myFormFilter = "[No1] = '" & Me.SearchBox & "' And [No2] = '" & Me.SearchBox2 & "'"

Me.Customer.Form.Filter = myFormFilter

End Sub