List Box works great but NOT in the way I need?? 1

[Event2020]

Hi - I am not sure if this is the correct forum to ask the following in but here goes.

I am using Access 2000 and I have a list box that is populated by a text box on a form. The user types an item in the text box (for example the letter A) and all items in the database that begin with the letter A are shown in the List box. This all works great, as my coding leaves a hell of a lot to be desired, I searched the web for an example and used that.

My problem is that when the form is opened, the list box ALREADY displays ALL items in the database until the user types a letter in, then it displays just the found items based around what the user has typed, For example:

Text Box data entry List Box displays
H Hello
Heat
Hotel
HE Hello
Heat
HEl Hello

What I need it to do is display NOTHING until the user enters a letter/s

Here is the SQL behind the list box
SELECT DISTINCTROW [tblTrackTitles].[TrackTitle], [tblMixTitle].[MixTitle], [tblComposers].[Composer], [tblPublishers].[Publisher] FROM tblPublishers INNER JOIN ((tblComposers INNER JOIN (tblTrackTitles INNER JOIN tblTrackInfoSub ON [tblTrackTitles].[TrackTitleID]=[tblTrackInfoSub].[TrackTitleID]) ON [tblComposers].[ComposerID]=[tblTrackInfoSub].[ComposerID]) INNER JOIN (tblMixTitle INNER JOIN tblTrackInfoMain ON [tblMixTitle].[MixID]=[tblTrackInfoMain].[MixID]) ON [tblTrackTitles].[TrackTitleID]=[tblTrackInfoMain].[TrackTitleID]) ON [tblPublishers].[PublisherID]=[tblTrackInfoSub].[PublisherID] WHERE ((([tblTrackTitles].[TrackTitle]) Like [txtSearchExpression]));

txtSearchExpression is just a hidden unbound text box that the SQL refers to and has NO propertys or record source set except it has "*" as its default value. I do not know how this works, all I can say is that it was in the example DB I copied the list box from.

Also there is a On Change placed on the Text box that the user types in and the code is as follows:
------------Code start-------------------
Private Sub txtEntry_Change()
On Error GoTo Err_txtEntry_Change
' This builds the search pattern string as characters
' are added or removed from the entered text. Concactenate an asterisk on the
' end of the entered text and requery the listbox source.

Me!txtSearchExpression = Me!txtEntry.Text & "*"
lstFound.Requery

Exit_txtEntry_Change:
Exit Sub

Err_txtEntry_Change:
MsgBox Err.Description
Resume Exit_txtEntry_Change

End Sub
---------------------Code End--------------------------

Now I know you guys may well need more info than this in order to help me so please just ask for what you need and I will post it straight away.

If you can help me I would be most grateful as I simply do not have enough Access knowledge to work it out for myself.

Thank you for your time,

Event2020

 

[tlbroadbent]

Remove the "*" as the default value of txtSearchExpression. With "*" in the text box the query criteria becomes LIKE "*" and * is a widcard that matches any character. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Event2020]

Hi Terry,

Many thanks for your reply and your suggestion of removing "*" from the text box search string, the list is now populating in the correct way - kindof

What is happening now is this:

When you open the form for the first time, the list box is still populated with ALL items in the DB (some 3000+ in my case)THEN when the user types the first letter in the text box, the list displays its contents in the way I need.

Is there anyway I can have the list box empty when the form is first opened, then when a users types in the text box it performs its task ???

Any Suggestions and your help so far would/are greatfully accepted.

Event2020

 

[CosmoKramer]

Try setting the Enabled property of the list box to No, then put this in your code just after your Requery statement:

lstFound.Enabled = True