Live search

[tom1252]

http://www.access-programmers.co.uk/forums/showthread.php?t=169417

Just looked at this and wonder how this was created and how can i do it searching through tables but still formed based

 

[John Big Booty]

First question; Have you looked at the form in design view to see how it is constructed?

Second question; Have you had a look at the code behind the various events? Specifically the On Change event of the unbound Text Box txtSearch on frmCustomer?

Once you've done that you should have an idea how this works and how you can implement it in your own DB.

Currently the Sub Form frmCustomerSub has a table as it's Record Source, that could just as well be a query, that is collecting data from a number of tables as you are wanting to do.

 

[Davros]

hi
i am using the live search example created here

http://www.access-programmers.co.uk/...d.php?t=169417

the only problem i have is when i search in the form frmcustomer in the txtsearch field (criteria) i am unable to insert any spaces. so if i want to search for 'bob roberts' i can only search for 'bob' because i am unable to insert a space and type 'roberts'.
any idea what i need to alter in the code to achieve this?

thanks in advance

oh and a great little program

 

[tom1252]

Hi

I have just started work on it but stuck and not sure what to do!

I have created the sub form and put it on to the search page but it is not showing all data.

I have attached that part of the database below

 

[John Big Booty]

hi
i am using the live search example created here

http://www.access-programmers.co.uk/...d.php?t=169417

the only problem i have is when i search in the form frmcustomer in the txtsearch field (criteria) i am unable to insert any spaces. so if i want to search for 'bob roberts' i can only search for 'bob' because i am unable to insert a space and type 'roberts'.
any idea what i need to alter in the code to achieve this?

thanks in advance

oh and a great little program

This is happening because Access drops trailing spaces from the text in the text box when the code in the On Change event of the unbound text box runs. To overcome this you will have to insert a test at the start of the code to detect trailing spaces, if a trailing space is found the code should be stopped.

The test would look something like;

If Len(Me.TextBoxName) <> 0 And InStr(Len(TextBoxName), TextBoxName, " ", vbTextCompare) Then
            Exit Sub
End If

 

[Davros]

Thanks for that
at least i know where to start to fix it.

 

[Davros]

can't seem to get the code you posted to work John Big Booty so i did a reverse solution. i inserted a _ for all the spaces in my text field. this did not work either for some reason. as soon as i typed a _ in the search box the live search field went blank.
should the search filed work with a _ ??

thanks

 

[Davros]

a bit rough i know but the code below ensures that on a live search in the txt search box i can type using spaces

Private Sub txtSearch_Change()
On Error GoTo Error_Trap

Dim vSearchString As String
vSearchString = Me.txtSearch.Text
Me.txtSearch.Value = vSearchString

' the above 3 lines of code ensures i can type spaces into my txt box so i can search for 'bob roberts'

Me.btnReset.SetFocus
Me.txtSearch.SetFocus
Me.ActiveControl.SelStart = Len(Me.ActiveControl & "")
Me.SubCustomer.Form.Filter = Me.cboField & " Like '" & Me.ActiveControl & "*'" 'i have removed a * after like '*"
Me.SubCustomer.Form.FilterOn = True

Error_Exit:
Exit Sub

Error_Trap:
MsgBox ("Error Code:" & Err.number & " Error Description:" & Err.Description)
Resume Error_Exit
End Sub

thanks for the replies and the original coder above

 

[John Big Booty]

OK I've made a couple of changes to the sample DB, and it will now handle spaces.

What I've done is added a new text box into which the criteria is typed, this is then passed into the original Text Box txtSearch which is now hidden.

Hope that helps

 

[tom1252]

Hi can anyone help me i have put the link to part od my db above and the sub form is not showing all the data in the fields any idea why?

 

[John Big Booty]

Hi can anyone help me i have put the link to part od my db above and the sub form is not showing all the data in the fields any idea why?

The problem you are encountering is caused because you have bound the Sub Form to the Main Form. Put the form set in design view, go to the Sub form wrapper/holder and click on the Data Tab, remove the entries in the Link Child Fields and Link Master Fields rows, save your changes, and when you return to form view you will see that all you data is there.

 

[tom1252]

Thanks that worked!

 

[tom1252]

I have almost finished codeing it. There is one little problem at the moment which is when you type in the box the curser goes back to the begining. So in affect you writeing things backwards. E.g. eulb Supposed to be blue. How can you get it to go back to the begining.