Creating list box OF tables within database

Hi, I'm not sure how to phrase or look for the exact nature of my question, so maybe I will explain.

I'm a bit of a newbie to advanced Access, and I am trying to create a form that can be used by other people at my company to run a query that I have been updating and running. This query is a basic duplicates query that compares a list of subscribers to any one of a number of campaign lists of emails and spits back records common in both based on email address.

Since there are many campaigns that are different and have email addresses within them, I am trying to create a form that allows the user to select a campaign (each campaign is a separate table), press a button, and the query will run and they can export the final information.

I know you can easily create a list box of fields within one table, but is there a way that access can make a list box of the tables within a database?

Not sure if I explained that correctly, but thanks for your help in advance!

Comments


  • OK, what you need to first do, is add your listbox in design view, choose the 2nd option, which is something like "I will enter my own values", then hit finish so you have an empty box. Just as a double-check, right-click and properties the listbox and ensure the "Row Source Type" is set to "Value List", only way it works. Now you need to add this code, hit alt + F11 to get the VBE window open, find the code for your form (on left-hand side look for name of your form e.g. Form_CampaignsForm, it may be in a subfolder) and paste this:

    [code]
    Private Sub Form_Load()

    Dim db As Database, tbl As TableDef
    Set db = CurrentDb

    For Each tbl In db.TableDefs
    If Left$(tbl.Name, 4) <> "MSys" Then
    List50.RowSource = tbl.Name & "; " & List50.RowSource
    End If
    Next

    End Sub
    [/code]

    Simply change wherever it says List50 to the name of your ListBox!

    To explain how it works, we use the Form_Load event so it does this once form is opened, you may assign it to a button or other control if you prefer, but I see no reason to.
    Next 2 lines just set db as current database and tbl as a table.
    The 'For Each' Loop then goes through each table in database and if the name of the table does NOT begin with MSys, then it adds the table name to the listbox.
    The reason for not including MSys tables is that these are system tables and so usually not wanted, just take out the if statement if you do wish to include them.
    Only problem I've found is that it puts each name above the previous one, so your list ends up being Z - A instead of A - Z, but that's easily altered, just put them into an array or something first.
    Use exact same method for comboboxes too. Hope this helps, let me know if you need any further guidance, Dai.


    ------------------------------------------
    Do or do not, there is no try. |
    ------------------------------------------
  • Thanks for the help, but I seem to have trouble getting the form to appear on the left. It shows my database, but has no dropdown options that might contain objects. Any idea what I am doing wrong?

    Thanks!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories