Tabs and list box code

[tMitch]

Hi -

I have a form with 3 tab controls. Within each tab there is an unbound listbox that lists tables. I want each list box within the tabs to select only specific tables relevant to the tab heading, but can't figure out the correct code. I'm able to select the tables using "tbl.Name Like..." in the Form Onload event procedure, but that changes all the list boxes. I tried with the OnClick procedure for the specific tab, but that's not working.

Any suggestions? I've tried varies searches on the forum with no luck.

I'm not experienced with writing code and seem to have gotten in over my head.

Thanks.

 

[WayneRyan]

tMitch,

I need a lot more info here. Each form on the Tab has a listbox. Each
listbox gets its data from a table. Do the tables change? Does some
selection criteria change?

An unbound Listbox (or any listbox) will always return the same set
of data, unless something changes it. Do you want to change it?

Wayne

 

[tMitch]

Hi Wayne -

Thanks for responding. I copied the code from elsewhere, so don't really know how to change it. The original was for a listbox in a form which lists all tables in the database (except "Msys", etc). The user then selects a table to view.

Rather than having to select from numerous tables, I want to organize the form by having tabs with different topics (ProjectTypes) and then having the listbox within each tab only list tables relevant to that topic (project). So, I want the listbox within the tab to list only tables with a certain prefix (tbl.Name like...). Only I can't figure out how to write the code.

I suppose I could skip the tabs and use a combobox or list box to select project type to limit the listbox, but again I have the same code writing problem. Typically I would do this with a query, but the listbox is not linked to a query.

Hope this makes sense. If you can help, that would be great.

Thank you!

 

[WayneRyan]

tmitch,

First, make the queries that you will need for each listbox. Use
the Criteria section under the table name (in the query) to put:
Like 'Performance%'

Get your form in Design View. Right-click on one of your listboxes
and select properties. Go to the Data Tab. Set the RowSource to
the proper query (you can use the drop-down).

That's it.

Wayne

 

[tMitch]

The problem (for me) is that the form is based on code which I copied from elsewhere (this forum probably). I don't know how to change that so that the list box is populated through queries where I can do what you suggested.

Is there somewhere in this code where I can say poplulate list box only with tables like "xxx*" and do so for each different list box? For instance, if the tab control (page?) = Proj1, then listbox tbl names = Proj1xxx; if tab control (page)= Proj2, then listbox tblnames = Proj2xxx.

Or is there a better way to be going about this?

Here is code:

Sub Refresh_Table()

Dim db As Database
Dim tbl As TableDef
Dim intI As Integer
Dim intNumTbls As Integer
Dim strList As String

Set db = DBEngine(0)(0)

db.TableDefs.Refresh
strList = ""
intNumTbls = db.TableDefs.Count

For intI = 0 To intNumTbls - 1
Set tbl = db.TableDefs(intI)

If tbl.Name Like "Usys*" Or tbl.Name Like "Msys*" Or tbl.Name Like "tbl*" Or tbl.Name Like "*Log" Then

Else
strList = strList & tbl.Name & ";"
End If
Next intI

Me!lstTables.RowSourceType = "Value List"
Me!lstTables.RowSource = strList
Me!lstTables = Me!lstTables.ItemData(0)

End Sub

Thank you!!