Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: ListBox of table names

    I need a way to view table names in a list box on a form. (Heck, I'm not even sure where to place the code for that!) Build Event?

    Select a table name . . .

    Pass that table name to a query.

    Run the query.

    I'm kind of new to Access 2003.

    Thanks much. . . Rick

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I actually viewd that thread earlier. Honest!

    I have a saved query that when runs returns the table names.

    SELECT Name
    FROM MSysObjects
    WHERE Type IN (1, 4, 6)
    AND Name NOT LIKE "~*"
    AND Name NOT LIKE "MSys*";

    I can't seem to have that query run in a list or combo box automatically or otherwise.

    I placed a combo box on my form. Selected Build Event - Code

    Placed the following . . .

    DoCmd.RunSQL "SelectTableNames"

    Nothing happens.

    I would like these table names to be there each time I open the form. I can't seem to find in the forms Event list an OnOpenForm.

    Thanks . . . Rick

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Got it

    Okay I was able to make the list box work which shows all of the tables.

    Now, how do I select a table name and pass it along to and SQL query?

    Dim tblName as String?

    Something like this.

    Rick

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes, something like that. This type of thing:

    strSQL = "SELECT ... FROM " & tblName & " WHERE..."
    Paul

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try
    PHP Code:
    SELECT MSysObjects.Name
      FROM MSysObjects
         WHERE 
    (((MSysObjects.FlagsNot In (-2147483648,2,-2147483645)) AND    
          ((
    MSysObjects.TypeIn (1,4)))
      
    ORDER BY MSysObjects.Name
    as the RowSource of your ListBox

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    I placed this in the Build Event of the list box?

    Where do I find the RowSource. (I'll keep looking.

    Thanks . . .

  8. #8
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    Is the list box RowSource the query name I have in the data tab of the listbox?

    Thanks . . .

  9. #9
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    yes, or you can write in the open Form event the following

    yourListName.RowSource = "SELECT MSysObjects.Name
    FROM MSysObjects
    WHERE (((MSysObjects.Flags) Not In (-2147483648,2,-2147483645)) AND
    ((MSysObjects.Type) In (1,4)))
    ORDER BY MSysObjects.Name;"

  10. #10
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    To continue . . .

    Okay here is what I have so far.
    Code:
    Private Sub List29_BeforeUpdate(Cancel As Integer)
    DoCmd.RunSQL "SelectTableNames"
    End Sub
    
    Private Sub Page2_Click()
    yourListName.RowSource = "SELECT MSysObjects.Name"
    FROM MSysObjects  
    WHERE (((MSysObjects.Flags) Not In (-2147483648,2,-2147483645)) AND
    ((MSysObjects.Type) In (1,4)))
    ORDER BY MSysObjects.Name;"
    
    End Sub
    When I debug this the bebugger stops at FROM MSysObjects with the Compile error: Sub or function not defined.

    What am I missing here?

    Thanks . . . Rick

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your error is because that's outside the string you started on the row above. Having said that, why create all the extra work? Just make that SQL the rowsource of the listbox in design view. It's not dynamic, so why set it every time? I thought you had this part worked out in post 4.
    Paul

  12. #12
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Sorry . . .

    "Just make that SQL the rowsource of the listbox in design view. It's not dynamic, so why set it every time? I thought you had this part worked out in post 4."

    My assumption is that the part I had worked out was being able to have the table names show up in the listBox.

    Now I'm trying to select a table name from that list and pass it to another query.

    What exactly is RowSource? (of the list box?). There is no named row, just a list of tables names.

    I'm sorry I just don't get this.

    Rick

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I've got to be honest; I'm always suspicious when someone is trying to get table names like this. It often means they've got a design problem (tables for each year, etc). Why don't you give us the "big picture" of what you're trying to accomplish.
    Paul

  14. #14
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Here is what I have.

    One main table with thousands of office names. Many office names are exactly the same, or have a very small differences. This is not a keyed table. It's a flat file database. I run queries that perform calculations based on the office names and dollar amounts in this main table.

    Then, for each of my clients, I have a small table that list maybe 12 office names to be calculated against the main table of thousands of records. This way, if I lose a client I simply delete their table, or add a small table for a new client.

    When the office names in the client table match the same office names in the main table in a specific way calculations are performed only on those matches. It's worked great for 15 years in Paradox. Only in Paradox I have a separate procedure (program) for each client. Not very effecient.

    I'm abandoning Paradox for obvious reasons.

    I'm running XP PRO with MS Office Professional 2003 1 GIG RAM.

    My thinking was this.

    Create a 4 tabbed form for each county. I have about four counties that I work in.

    When I open (Select) a tab, the list box shows me my client tables for that county.

    I select a client table name from the list box and pass that tablename variable(?) to the calculating query.

    Then run the report, which is already designed and working well.

    (Something new to me is that I can select the report and the query runs with all of the calculations shown on the last page (SubReport?) and the main data in the body context of the report.)

    This is the VERY BASIC, but most important part of the works.

    .Take table name from list box
    .. pass table name to query - run query
    ... View, and then print report

    Select next table name form list box . . . etc.

    Whew . . . Thanks.

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    MLS numbers, use MLS numbers, you can thank me later...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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