Help Using Text Box to fill a Listbox

Hi-

I'm creating a database of music bands with their cds and songs.

I'm trying to program an SQL statement so that I can enter a string of
text in a textbox, press the 'Enter' key, and have it return the
associated records to a listbox. Once the listbox has the records, I
want to select a record, which will open a form associated with the
selected record in the listbox.

For example:

If I enter the letter, "L," in my textbox and press the 'Enter' key,
I'd like the bands that start with the letter, "L," to appear in the
listbox, i.e. Limp Bizkit, Led Zeppelin, etc...

Here's my SQL statent for my listbox:

SELECT DISTINCTROW [Bands].[BandNumber], [Bands].[BandName]
FROM [Bands]
WHERE ((([Bands].[BandName]) Like "[forms]![frmSearch]![txtLetter]*"))
ORDER BY [Bands].[BandName];


Is my criteria for BandName OK? Or am I using it wrong?

Like "[forms]![frmSearch]![txtLetter]*"


So after I figure how to get this to work, I gotta figure out how to
get the selected record to open a form that has general information
about the band.

Thanks for reading my post!!!

Megan

re: Help Using Text Box to fill a Listbox

What you describe is how a Combo Box works with the default setting of Yes
for AutoExpand. It does it automatically, and has the advantage of catching
misspellings as it scrolls to the next matching entry as you type. That is,
set its "Limit to List" property to Yes, and make its RowSource a query that
returns just one (Unique Fields) entry for each of the groups in your
database.

I suppose it might be an interesting exercise to re-implement the
functionality using a Text Box, but there are many interesting exercises
that don't require re-inventing what Access provides.

Larry Linson
Microsoft Access MVP

"Megan" <meganrobertson22@hotmail.com> wrote in message
news:5c14c12b.0406211811.2f0e3bfc@posting.google.c om...[color=blue]
> Hi-
>
> I'm creating a database of music bands with their cds and songs.
>
> I'm trying to program an SQL statement so that I can enter a string of
> text in a textbox, press the 'Enter' key, and have it return the
> associated records to a listbox. Once the listbox has the records, I
> want to select a record, which will open a form associated with the
> selected record in the listbox.
>
> For example:
>
> If I enter the letter, "L," in my textbox and press the 'Enter' key,
> I'd like the bands that start with the letter, "L," to appear in the
> listbox, i.e. Limp Bizkit, Led Zeppelin, etc...
>
> Here's my SQL statent for my listbox:
>
> SELECT DISTINCTROW [Bands].[BandNumber], [Bands].[BandName]
> FROM [Bands]
> WHERE ((([Bands].[BandName]) Like "[forms]![frmSearch]![txtLetter]*"))
> ORDER BY [Bands].[BandName];
>
>
> Is my criteria for BandName OK? Or am I using it wrong?
>
> Like "[forms]![frmSearch]![txtLetter]*"
>
>
> So after I figure how to get this to work, I gotta figure out how to
> get the selected record to open a form that has general information
> about the band.
>
> Thanks for reading my post!!!
>
> Megan[/color]

re: Help Using Text Box to fill a Listbox

Hi Larry-

First let me say thanks for reading my post and replying.

Maybe you misunderstood what I meant, or maybe I wasn't clear enough.
I just want to enter the first letter of a band, i.e. "S" for Simpson,
Jessica and Spears, Brittany. I don't want to spell-out the entire
band name. I'd just like to enter a letter into a text box and then
see all of the bands that start with that letter. I don't want the
band names to automatically appear in the text box like it would in a
combo box. Gosh, I thought this might be easy to do.

I was doing some searching and came across this example:

SELECT fld FROM table WHERE fld Like
Forms!theform.textbox.Text & "*" ORDER BY fld

Use the text box's Change event to sync with the list box:

Me.listbox.Requery

NOTE: I apologize to the person who wrote this code. I forgot where I
saw it, but I wrote down his/ her example. I usually like to credit
the person I learned from. I'll try to find out where I saw this code.

I tried this code, but after I enter a letter into the text box and
press "Enter" the focus moves to the next control, and nothing appears
in my listbox.

Thanks,

Megan


"Larry Linson" <bouncer@localhost.not> wrote in message news:<hFNBc.20313$Yb1.5082@nwrddc02.gnilink.net>.. .[color=blue]
> What you describe is how a Combo Box works with the default setting of Yes
> for AutoExpand. It does it automatically, and has the advantage of catching
> misspellings as it scrolls to the next matching entry as you type. That is,
> set its "Limit to List" property to Yes, and make its RowSource a query that
> returns just one (Unique Fields) entry for each of the groups in your
> database.
>
> I suppose it might be an interesting exercise to re-implement the
> functionality using a Text Box, but there are many interesting exercises
> that don't require re-inventing what Access provides.
>
> Larry Linson
> Microsoft Access MVP
>
> "Megan" <meganrobertson22@hotmail.com> wrote in message
> news:5c14c12b.0406211811.2f0e3bfc@posting.google.c om...[color=green]
> > Hi-
> >
> > I'm creating a database of music bands with their cds and songs.
> >
> > I'm trying to program an SQL statement so that I can enter a string of
> > text in a textbox, press the 'Enter' key, and have it return the
> > associated records to a listbox. Once the listbox has the records, I
> > want to select a record, which will open a form associated with the
> > selected record in the listbox.
> >
> > For example:
> >
> > If I enter the letter, "L," in my textbox and press the 'Enter' key,
> > I'd like the bands that start with the letter, "L," to appear in the
> > listbox, i.e. Limp Bizkit, Led Zeppelin, etc...
> >
> > Here's my SQL statent for my listbox:
> >
> > SELECT DISTINCTROW [Bands].[BandNumber], [Bands].[BandName]
> > FROM [Bands]
> > WHERE ((([Bands].[BandName]) Like "[forms]![frmSearch]![txtLetter]*"))
> > ORDER BY [Bands].[BandName];
> >
> >
> > Is my criteria for BandName OK? Or am I using it wrong?
> >
> > Like "[forms]![frmSearch]![txtLetter]*"
> >
> >
> > So after I figure how to get this to work, I gotta figure out how to
> > get the selected record to open a form that has general information
> > about the band.
> >
> > Thanks for reading my post!!!
> >
> > Megan[/color][/color]

re: Help Using Text Box to fill a Listbox

Hi everybody-

I just wanted to say that I got this working the way I wanted. I can
type in a letter in a text box, hit "Enter," and have the listbox
populated by bands starting with that letter.

It didn't work well or as planned using the On Change event of the
text box. So I tried the After Update Event of the text box, and it
works as I hoped.

Here's the code:

SELECT fld FROM table WHERE fld Like
Forms!theform.textbox.Text & "*" ORDER BY fld

Use the text box's After Update event to sync with the list box:

Me.listbox.Requery

NOTE: this is not my code. I found it in a post in
microsoft.public.access.formscoding. It was posted by "Marshall
Barton" opn 3-11-2004. Shout out to you Marsh!!!

http://groups.google.com/groups?q=te...4ax.com&rnum=7


Megan


meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0406211811.2f0e3bfc@posting.google. com>...[color=blue]
> Hi-
>
> I'm creating a database of music bands with their cds and songs.
>
> I'm trying to program an SQL statement so that I can enter a string of
> text in a textbox, press the 'Enter' key, and have it return the
> associated records to a listbox. Once the listbox has the records, I
> want to select a record, which will open a form associated with the
> selected record in the listbox.
>
> For example:
>
> If I enter the letter, "L," in my textbox and press the 'Enter' key,
> I'd like the bands that start with the letter, "L," to appear in the
> listbox, i.e. Limp Bizkit, Led Zeppelin, etc...
>
> Here's my SQL statent for my listbox:
>
> SELECT DISTINCTROW [Bands].[BandNumber], [Bands].[BandName]
> FROM [Bands]
> WHERE ((([Bands].[BandName]) Like "[forms]![frmSearch]![txtLetter]*"))
> ORDER BY [Bands].[BandName];
>
>
> Is my criteria for BandName OK? Or am I using it wrong?
>
> Like "[forms]![frmSearch]![txtLetter]*"
>
>
> So after I figure how to get this to work, I gotta figure out how to
> get the selected record to open a form that has general information
> about the band.
>
> Thanks for reading my post!!!
>
> Megan[/color]

re: Help Using Text Box to fill a Listbox

"Megan" <meganrobertson22@hotmail.com> wrote in message
news:5c14c12b.0406220811.6d5af206@posting.google.c om...
[color=blue]
> I was doing some searching and came across this example:
>
> SELECT fld FROM table WHERE fld Like
> Forms!theform.textbox.Text & "*" ORDER BY fld
>
> Use the text box's Change event to sync with the list box:
>
> Me.listbox.Requery
>
> NOTE: I apologize to the person who wrote this code. I forgot where I
> saw it, but I wrote down his/ her example. I usually like to credit
> the person I learned from. I'll try to find out where I saw this code.
>
> I tried this code, but after I enter a letter into the text box and
> press "Enter" the focus moves to the next control, and nothing appears
> in my listbox.[/color]

Does your query return the correct results when run in the query builder?
That's where you should begin your quest to get things working.

Are you sure the listbox rowsource is set correctly?

Are you sure you are referencing the .Text property of the textbox
specifically in the query? Nothing will happen in the Change Event
if you're referencing the .Value property.

re: Help Using Text Box to fill a Listbox

Hey RKC-

thanks for the suggestion! I tried it your way, and it works even
better than my solution! And it does more!

Thanks a bunch!

Megan


"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message news:<eE_Bc.110801$j24.890@twister.nyroc.rr.com>.. .[color=blue]
> "Megan" <meganrobertson22@hotmail.com> wrote in message
> news:5c14c12b.0406220811.6d5af206@posting.google.c om...
>
>
> Does your query return the correct results when run in the query builder?
> That's where you should begin your quest to get things working.
>
> Are you sure the listbox rowsource is set correctly?
>
> Are you sure you are referencing the .Text property of the textbox
> specifically in the query? Nothing will happen in the Change Event
> if you're referencing the .Value property.[/color]

re: Help Using Text Box to fill a Listbox

Hi-

I've encountered a new problem. After I type letters in my text box to
populate my listbox, I click my command button to open a form with the
selected record and I get a pop-up box that asks for the criteria of
my listbox query.

Enter Parameter Value:
Forms!frmSearch.txtLetter.Text

If I hit "OK" or "Cancel" the form opens up alright with the correct
record selected in my listbox.

Here's some of the code behind my command button. I have 2 listboxes
on my form. After I type in "L" then "I" for "LI" in my text box,
txtLetter, the listbox is populated with the bands: Limp Bizkit and
Linkin Park. If I select either band, then click my command button to
open a form, then I get the "Enter Parameter Box" pops up.

I use a nestled IF statement to determine which listbox is being used.
Here's the part that runs if I select a Band Name:

ElseIf Me.lstAssignedHearings.ItemsSelected.Count > 0 Then
strg = Int(Me.lstBandNames)
code = "[BandNumber] = " & strg & " "
DoCmd.Close
DoCmd.OpenForm FormName:="frmAlbums", wherecondition:=code
End If

I'm trying to figure out why that Parameter Box pops up?

Thanks,

Megan




meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0406220954.7243b038@posting.google. com>...[color=blue]
> Hi everybody-
>
> I just wanted to say that I got this working the way I wanted. I can
> type in a letter in a text box, hit "Enter," and have the listbox
> populated by bands starting with that letter.
>
> It didn't work well or as planned using the On Change event of the
> text box. So I tried the After Update Event of the text box, and it
> works as I hoped.
>
> Here's the code:
>
> SELECT fld FROM table WHERE fld Like
> Forms!theform.textbox.Text & "*" ORDER BY fld
>
> Use the text box's After Update event to sync with the list box:
>
> Me.listbox.Requery
>
> NOTE: this is not my code. I found it in a post in
> microsoft.public.access.formscoding. It was posted by "Marshall
> Barton" opn 3-11-2004. Shout out to you Marsh!!!
>
> http://groups.google.com/groups?q=te...4ax.com&rnum=7
>
>
> Megan
>
>
> meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0406211811.2f0e3bfc@posting.google. com>...[color=green]
> > Hi-
> >
> > I'm creating a database of music bands with their cds and songs.
> >
> > I'm trying to program an SQL statement so that I can enter a string of
> > text in a textbox, press the 'Enter' key, and have it return the
> > associated records to a listbox. Once the listbox has the records, I
> > want to select a record, which will open a form associated with the
> > selected record in the listbox.
> >
> > For example:
> >
> > If I enter the letter, "L," in my textbox and press the 'Enter' key,
> > I'd like the bands that start with the letter, "L," to appear in the
> > listbox, i.e. Limp Bizkit, Led Zeppelin, etc...
> >
> > Here's my SQL statent for my listbox:
> >
> > SELECT DISTINCTROW [Bands].[BandNumber], [Bands].[BandName]
> > FROM [Bands]
> > WHERE ((([Bands].[BandName]) Like "[forms]![frmSearch]![txtLetter]*"))
> > ORDER BY [Bands].[BandName];
> >
> >
> > Is my criteria for BandName OK? Or am I using it wrong?
> >
> > Like "[forms]![frmSearch]![txtLetter]*"
> >
> >
> > So after I figure how to get this to work, I gotta figure out how to
> > get the selected record to open a form that has general information
> > about the band.
> >
> > Thanks for reading my post!!!
> >
> > Megan[/color][/color]

re: Help Using Text Box to fill a Listbox

> I've encountered a new problem. After I type letters in my text box to[color=blue]
> populate my listbox, I click my command button to open a form with the
> selected record and I get a pop-up box that asks for the criteria of
> my listbox query.
>
> Enter Parameter Value:
> Forms!frmSearch.txtLetter.Text
>
> If I hit "OK" or "Cancel" the form opens up alright with the correct
> record selected in my listbox.
>
> Here's some of the code behind my command button. I have 2 listboxes
> on my form. After I type in "L" then "I" for "LI" in my text box,
> txtLetter, the listbox is populated with the bands: Limp Bizkit and
> Linkin Park. If I select either band, then click my command button to
> open a form, then I get the "Enter Parameter Box" pops up.
>
> I use a nestled IF statement to determine which listbox is being used.
> Here's the part that runs if I select a Band Name:
>
> ElseIf Me.lstAssignedHearings.ItemsSelected.Count > 0 Then
> strg = Int(Me.lstBandNames)
> code = "[BandNumber] = " & strg & " "
> DoCmd.Close
> DoCmd.OpenForm FormName:="frmAlbums", wherecondition:=code
> End If
>
> I'm trying to figure out why that Parameter Box pops up?[/color]

If you're not using the Multi-select property of the listbox, use a
combobox and save yourself a LOT of headaches. Otherwise, use the
code at www.mvps.org to iterate through the ItemsSelected collection
of the listbox to create your filter/where condition.

re: Help Using Text Box to fill a Listbox

Problem: I get an "Enter Parameter Value" box after I fill a listbox,
select a record, and use a command button to open the selected record
in a form.

Solution: Well, I found a way around this, but I don't think it is the
best way to handle it. I added this line of code:

Me.lstBandNames.RowSource = ""

I added it to my nestled If statement:

ElseIf Me.lstBandNames.ItemsSelected.Count > 0 Then
strg = Int(Me.lstBandNames)
code = "[BandNumber] = " & strg & " "
Me.lstBandNames.RowSource
DoCmd.Close
DoCmd.OpenForm FormName:="frmAlbums", wherecondition:=code
End If


Even though this works, I was wondering if there is a better way to do
this, or if my code is just bad. :-)


Thanks,

Megan


meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0406250944.4153106f@posting.google. com>...[color=blue]
> Hi-
>
> I've encountered a new problem. After I type letters in my text box to
> populate my listbox, I click my command button to open a form with the
> selected record and I get a pop-up box that asks for the criteria of
> my listbox query.
>
> Enter Parameter Value:
> Forms!frmSearch.txtLetter.Text
>
> If I hit "OK" or "Cancel" the form opens up alright with the correct
> record selected in my listbox.
>
> Here's some of the code behind my command button. I have 2 listboxes
> on my form. After I type in "L" then "I" for "LI" in my text box,
> txtLetter, the listbox is populated with the bands: Limp Bizkit and
> Linkin Park. If I select either band, then click my command button to
> open a form, then I get the "Enter Parameter Box" pops up.
>
> I use a nestled IF statement to determine which listbox is being used.
> Here's the part that runs if I select a Band Name:
>
> ElseIf Me.lstAssignedHearings.ItemsSelected.Count > 0 Then
> strg = Int(Me.lstBandNames)
> code = "[BandNumber] = " & strg & " "
> DoCmd.Close
> DoCmd.OpenForm FormName:="frmAlbums", wherecondition:=code
> End If
>
> I'm trying to figure out why that Parameter Box pops up?
>
> Thanks,
>
> Megan
>
>
>
>
> meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0406220954.7243b038@posting.google. com>...[color=green]
> > Hi everybody-
> >
> > I just wanted to say that I got this working the way I wanted. I can
> > type in a letter in a text box, hit "Enter," and have the listbox
> > populated by bands starting with that letter.
> >
> > It didn't work well or as planned using the On Change event of the
> > text box. So I tried the After Update Event of the text box, and it
> > works as I hoped.
> >
> > Here's the code:
> >
> > SELECT fld FROM table WHERE fld Like
> > Forms!theform.textbox.Text & "*" ORDER BY fld
> >
> > Use the text box's After Update event to sync with the list box:
> >
> > Me.listbox.Requery
> >
> > NOTE: this is not my code. I found it in a post in
> > microsoft.public.access.formscoding. It was posted by "Marshall
> > Barton" opn 3-11-2004. Shout out to you Marsh!!!
> >
> > http://groups.google.com/groups?q=te...4ax.com&rnum=7
> >
> >
> > Megan
> >
> >
> > meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0406211811.2f0e3bfc@posting.google. com>...[color=darkred]
> > > Hi-
> > >
> > > I'm creating a database of music bands with their cds and songs.
> > >
> > > I'm trying to program an SQL statement so that I can enter a string of
> > > text in a textbox, press the 'Enter' key, and have it return the
> > > associated records to a listbox. Once the listbox has the records, I
> > > want to select a record, which will open a form associated with the
> > > selected record in the listbox.
> > >
> > > For example:
> > >
> > > If I enter the letter, "L," in my textbox and press the 'Enter' key,
> > > I'd like the bands that start with the letter, "L," to appear in the
> > > listbox, i.e. Limp Bizkit, Led Zeppelin, etc...
> > >
> > > Here's my SQL statent for my listbox:
> > >
> > > SELECT DISTINCTROW [Bands].[BandNumber], [Bands].[BandName]
> > > FROM [Bands]
> > > WHERE ((([Bands].[BandName]) Like "[forms]![frmSearch]![txtLetter]*"))
> > > ORDER BY [Bands].[BandName];
> > >
> > >
> > > Is my criteria for BandName OK? Or am I using it wrong?
> > >
> > > Like "[forms]![frmSearch]![txtLetter]*"
> > >
> > >
> > > So after I figure how to get this to work, I gotta figure out how to
> > > get the selected record to open a form that has general information
> > > about the band.
> > >
> > > Thanks for reading my post!!!
> > >
> > > Megan[/color][/color][/color]

re: Help Using Text Box to fill a Listbox

Thanks everybody for your time and for reading my post!

I found a solution to my problem. I don't know if this is the best way
to do this, but I kind of tricked the Enter Parameter Value box by
setting the rowsource to "" in my code.

I added the following line of code to my nested IF statement:

Me.lstBandNames.RowSource = ""


BEFORE:

ElseIf Me.lstBandNames.ItemsSelected.Count > 0 Then
strg = Int(Me.lstBandNames)
code = "[BandNumber] = " & strg & " "
DoCmd.Close
DoCmd.OpenForm FormName:="frmAlbums", wherecondition:=code
End If


AFTER:

ElseIf Me.lstBandNames.ItemsSelected.Count > 0 Then
strg = Int(Me.lstBandNames)
code = "[BandNumber] = " & strg & " "
Me.lstBandNames.Rowsource = ""
DoCmd.Close
DoCmd.OpenForm FormName:="frmAlbums", wherecondition:=code
End If


I don't know if this is the best way to do what I want to do, but I'm
new to listboxes and VBA code. I'm reading about "multi-select
listboxes" and "FOR" loops. So hopefully, I'll get better soon.

Thanks everybody. I posted my solution because I hope this might be
able to help someone else!

Megan


meganrobertson22@hotmail.com (Megan) wrote in message news:<5c14c12b.0406250944.4153106f@posting.google. com>...[color=blue]
> Hi-
>
> I've encountered a new problem. After I type letters in my text box to
> populate my listbox, I click my command button to open a form with the
> selected record and I get a pop-up box that asks for the criteria of
> my listbox query.
>
> Enter Parameter Value:
> Forms!frmSearch.txtLetter.Text
>
> If I hit "OK" or "Cancel" the form opens up alright with the correct
> record selected in my listbox.
>
> Here's some of the code behind my command button. I have 2 listboxes
> on my form. After I type in "L" then "I" for "LI" in my text box,
> txtLetter, the listbox is populated with the bands: Limp Bizkit and
> Linkin Park. If I select either band, then click my command button to
> open a form, then I get the "Enter Parameter Box" pops up.
>
> I use a nestled IF statement to determine which listbox is being used.
> Here's the part that runs if I select a Band Name:
>
> ElseIf Me.lstBandNames.ItemsSelected.Count > 0 Then
> strg = Int(Me.lstBandNames)
> code = "[BandNumber] = " & strg & " "
> DoCmd.Close
> DoCmd.OpenForm FormName:="frmAlbums", wherecondition:=code
> End If
>
> I'm trying to figure out why that Parameter Box pops up?
>
> Thanks,
>
> Megan
>
>
>[/color]