Filter as you type text box (1 Viewer)

Gismo · Jul 17, 2017

Hi All, I am struggling to get my search as you type text box working.
Below is the code I used. it gives me error on "fLiveSearch Me.txtsearch, Me.1stItems, strFullList, strFilteredList, Me.txtCount, it does not recognize Me.1stItems. which I am not sure where it comes from or what it should do

Private Sub txtSearch_Change()
Dim strFullList As String
Dim strFilteredList As String

If blnSpace = False Then
Me.Refresh
strFullList = "SELECT ID, PartNo, Description FROM StockMaster ORDER BY First;"
strFilteredList = "SELECT ID, PartNo, Description FROM StockMaster WHERE [PartNo] LIKE ""*" & Me.txtsearch.Value & _
"*"" OR [Description] LIKE ""*" & Me.txtsearch.Value & "*"" ORDER BY [PartNo]"

fLiveSearch Me.txtsearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount
End If
End Sub

isladogs · Jul 17, 2017

You'll need to provide details of the fLiveSearch function for anyone to help you

Also is it me.1stItems or me.LstItems?

Gismo · Jul 17, 2017

I used: Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)

Gismo · Jul 17, 2017

If you have any other alternative coding could you please help me with that?

isladogs · Jul 17, 2017

You didn't answer this

Also is it me.1stItems or me.LstItems?

Check whether you mistyped the listbox name in your code as that would explain your error

Probably it should read

Code:

fLiveSearch Me.txtsearch, Me.LstItems, strFullList, strFilteredList, Me.txtCount

I'm out for the rest of the day so won't be able to respond further if you need more assistance. Hopefully someone else will step in if necessary

Gismo · Jul 17, 2017

yes sorry it is LstItems. I checked all my spelling as well

jdraw · Jul 17, 2017

Please post the code for the fLiveSearch function and/or a copy of your database.

isladogs · Jul 17, 2017

So does that mean it's working now?

Sent from my iPhone using Tapatalk

Gismo · Jul 21, 2017

Hi All, Please help with below code: Access does not recognize "Filter"
Private Sub SearchDescription_Change()
Me.StockDatasheet.Filter = Description Like " & chr(34) & Me.SearchDescription.text & chr(34) & " * ""
Me.StockDatasheet.FilterOn = True
End Sub

Minty · Jul 21, 2017

It should be Me.Filter and Me.FilterOn , and I doubt you want to turn it on every time you type a character...

Gismo · Jul 21, 2017

Thank you, I removed the filter on but I still get an error on the Me.Filter.StockDataSheet

Minty · Jul 21, 2017

Filter is a form level property, that is why you should only use Me.Filter = ...
Me. refers to the current form Object, so you are effectively saying
Set MyForms Filter to "blah blah blah"

Me.Filter.Stockdatsheet doesn't mean anything to Access.
What are you trying to do?

Gismo · Jul 21, 2017

I want to filter as you type. Just not getting it to work any way around

Minty · Jul 21, 2017

So what is the complete code you are currently using? Including the code for any functions you are using - as per jDraws request in post #7

Gismo · Jul 21, 2017

I used: Function fLiveSearch(ctlSearchBox As TextBox, ctlFilter As Control, _
strFullSQL As String, strFilteredSQL As String, Optional ctlCountLabel As Control)

Deleted this already as I am not getting it to work. do you have another option for me?
basically what I want to do is the user needs to type on the header (Name) of the field in the data sheet (top Form) and It should update the sub form below with record in the datasheet is selected. the result of the search I want to show at the bottom of the screen so now visible typing will be shown in the text box over the header. is there a sample data base similar to this that I could have a look at?

jdraw · Jul 21, 2017

I don't mean to be a pain, but you seem to be telling us how you did something that didn't work so you tossed it. I'm still trying to understand what the something is in plain English.

I don't think the user -or most users - said ---I want to type on the header of the field in the datasheet...

He/she/they may have said -- I need to find Employee records by typing only a few characters of that person's surname.

Base on Google search I think this is the link to fLiveSearch function and database example.

AndrewS · Aug 7, 2017

I use the flivesearch function from OpenGate Software a lot. Like others, I'm finding it difficult to understand exactly what you want to do and what bit of it isn't working.

I think you have a form with a textbox called txtsearch in the header and a subform called LstItems in the detail area of the form.

You want the user to type in the textbox and the datasheet will display only records matching what the user has typed in? flivesearch does that and the instructions for using the flivesearch are in the code.

You posted this code in the first post in this thread

Code:

Private Sub txtSearch_Change()
Dim strFullList As String
Dim strFilteredList As String

If blnSpace = False Then
Me.Refresh 
strFullList = "SELECT ID, PartNo, Description FROM StockMaster ORDER BY First;"
strFilteredList = "SELECT ID, PartNo, Description FROM StockMaster WHERE [PartNo] LIKE ""*" & Me.txtsearch.Value & _
"*"" OR [Description] LIKE ""*" & Me.txtsearch.Value & "*"" ORDER BY [PartNo]"


fLiveSearch Me.txtsearch, Me.lstItems, strFullList, strFilteredList, Me.txtCount
End If
End Sub

Possible things that might be wrong:

You haven't dim'd or defined the variable blnSpace. If it's set to true, none of this code will run. I'm not sure what it's for. Comment out the If and Endif lines and see if that makes it work.
If you run the SQL in strFullList (create a query, go to SQL view and paste it in), does it work?
Are LstItems and txtCount actually on your form?
Have you added the flivesearch function to a global module? It's not a built-in Access function - you have to define it.

You asked if there is a sample database you could have a look at. Yes. If you've got the flivesearch code, you must have downloaded it from Open Gates Software. If you haven't downloaded that to get the flivesearch code, then my last bullet point above is probably the main problem.

Gismo · Aug 7, 2017

Hi AndrewS, I have the Flivesearch in my DB, not that clued up with access so not sure what to do with the blnSpace, what the syntax would be or where to insert it

AndrewS · Aug 8, 2017

I've just looked again at the OpenGate Software sample database, and blnspace is a variable, dim'd right at the top of the code for frmSearchExample, and defined in the keyPress event of the txtSearch textbox.

It is to ignore the spacebar being pressed. Personally, I don't know why if you're searching for text you'd want to ignore the space bar, as, as soon as you enter two words, the search will always fail. If you know that the field(s) to be searched will never contain spaces, then fine, but to me it seems an odd starting point for OpenGate to have used.

As I suggested, try commenting the if-endif lines.

You say you're not "clued up with Access" - with the greatest of respect, you probably need to get a bit more "clued up." If you haven't, I'd suggest buying a good Access book. You'll probably be able to pick up a second hand one for an older version like Access 2010 or 2013 for not much money. It'll provide you with the basic understanding that'll let you understand what's going on in sample databases/sample code like the SearchAsYouType sample, and let you pick them apart to use what's useful for you. That is basically what I did: I'd used Access briefly in the 97 days to link a Paradox for DOS database to a SQL server system, but my expertise was in the Paradox for DOS side. That is now dead (simply doesn't work in latest versions of Windows), so I had to become familiar with Access quickly. A book for the basics, trial and error, and picking apart others' code got me to where I am now. Which is still pretty much beginners level compared to many on here.

Please accept my apologies if I've underestimated your level of familiarity with Access.

But I think the flivesearch function will do what you seem to want it to, so it's a matter of fitting it to your form and data.

Gismo · Aug 9, 2017

search as you type seems to be ok, changed all the table names and fields, no errors on that except for "An unexpected error has occurred: Object does not support propery or method, error 438, Line 180. no sure what it is because I don't have 180 lines in this VDB. thanx for the advise

Filter as you type text box (1 Viewer)

Registered User.

MVP / VIP

Registered User.

Registered User.

MVP / VIP

Registered User.

Super Moderator

MVP / VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread