Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Red face Unanswered: Get Table name from a form

    Ok this is what I'm trying to do:

    I have a database with about 30 tables (which are actually linked to Excel spredsheets). I need to be able to query each table and make a report only showing certain information for it. I started by doing individual queries for each table and individual reports for each query. That slowed the program down ALLOT! and made it crash all the time because it became so large. I figured out how to get a query for a list of table names using the MSysObjects table but I need to know how to have a user open a form and select a table name and have a query run on that table based on the user's selection. Ex. I have Tables called class A, class B, and class C. In each table is a list of student names, ages, and...height. I want a user to be able to open a form and in a drop down menu (or whatever) select class c and hit a command button that will start a query which will then produce a report listing only the students in that class over the age of 18 and with there name and age only appearing. PLEASE PLEASE HELP!!!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Dealing directly with the MSysObjects table is not recommended. You can place a listbox or a combobox on your form and set its RowSourceType property to Table/Query and its RowSource property to
    Code:
    SELECT MSysObjects.Name, MSysObjects.Flags, MSysObjects.Type
    FROM MSysObjects
    WHERE (((MSysObjects.Flags)=0) AND ((MSysObjects.Type)=1))
    ORDER BY MSysObjects.Name;
    From there you can retrieve the name of a table selected in the list and do whatever you want with it.

    A better and safer solution would be to work with the TableDefs collection.

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •