Help with inputbox

[coryt]

I have a form with a subform which the user inputs data for various contracts. On the form I have a cmd button. When this button is clicked, I want an input box to appear asking the user to select a table. Now for the funky stuff. I want the imput box to show a combo box instead of a text box. I would also like the combo box to show the Table names from a seperate database.

I thought about creating my own form and having that appear as my input box, that would be fine. But my main problem is having the table names from the other database, show in the combo box.

Is there an easy way to do this.?

Thanks in advance.

 

[Mile-O]

You'll definitely have to make your own form for this.

On the database whose tables you want to include in the combobox, ensure that you are selecting from its table MSysObjects.

(This table can be found by going to Tools -> Options -> Show Hidden and Show System Objects)

There's a specific identifier for tables although I don't actually know it offhand. This way you can query that table for your tables for the RowSource of your combobox.

 

[coryt]

I noticed that the "ParrentID" for tables seem to be 251658241.

Can you explain to my how I would "query that table for my tables for the RowSource of your combobox."

Thanks Mile.

 

[Mile-O]

Link the table into your database and then build a query (this can simply be the combo's RowSource) on the linked table.

Simply, right click on the tables and select link table.

Find the path of the other database.

Select the table from that database.

Hey presto! A linked table.

 

[coryt]

Forget it Mile! Im an idiot. I hid the objects again and didn't realize it.

 

[coryt]

Now for my next trick.

How can I just show the table names and not the other stuff from that column?

 

[needhelp]

Getting Table Names

The way that I would attack this problem is to cycle through the TableDefs collection.

On your form, set your ComboBox's "Row Source Type" to "Value List" and add the following code to your form:

Private Sub Form_Open(Cancel As Integer)

Dim i As Integer 'Integer to cycle through all tables
Dim Source As String 'String to hold the ComboBox's RowSource

'Loop through every table in the Current Database
For i = 0 To CurrentDb.TableDefs.count - 1
'If the table doesn't appear to be a 'System Table', add it to the RowSource.
If InStr(CurrentDb.TableDefs(i).name, "MSys") = 0 Then Source = Source & "'" & CurrentDb.TableDefs(i).name & "';"
Next i

'Assign the RowSource of the ComboBox
[Your ComboBox Name].RowSource = Source

End Sub

There are two problems with this code. One, it can take a few seconds to complete if you have several tables in your database. Also, if you have a table with "msys" in the name this code will not add it to the ComboBox.

Hope this helps!