Okay Next issue with my form

  • Thread starter Thread starter mon
  • Start date Start date
M

mon

I have six different lists (depends on who the client is).
Do I have to make a different form for each or is there
some way I can select which list is to go into the combo
box? Making one big lookup table is not a good option
because that would make a list of about 40 records and
since most users would only use one list out of the six it
would probably drive them crazy.
Thanks Mon
 
You can adjust what is displayed in the list by modifying the RowSource for the list box.
Using a query as the source, you can filter what is displayed by using the criteria
property of the query. Depending on what else you are doing, you can change this through
code or you can simply have the query refer to a value on your form in the query's
criteria which will automatically change what the list displays. Then, all you would have
to do is tell the list to rerun the query (Me.lstMyListbox.Requery).

You can set the RowSource of the list box to the name of a query saved in your database or
you could put the SQL of a query right into the list box, whichever is easier for you. The
second option does have a limitation on the length of the SQL statement. If it is going to
be long and complicated, you will need to make it a saved query and use the query name for
the RowSource. I think the limit is around 255 characters.

To set the RowSource:
If the item is a saved query,
Me.lstMyListbox.RowSource = "qryMyQuery"

If the item is a SQL statement,
Me.lstMyListbox.RowSource = "SELECT * FROM tblMyTable WHERE Field1=3 ORDER BY Field1;"
 
Back
Top