Access: Need to display a list of tables in a drop down window

[mansied]

Is there a way that I can have a form in Access show the user a list of the tables that are in Access as a drop down list?
What I want to do is let the user view data based off the actual table it is in.However, also don't want to show ALL the tables, just ones with a name prefix like "Data_*".
I want the user choose the name and then by clicking the bottom can append to a main table .
Thank you very much

 

[Ranman256]

I have a table bound to the listbox/combo.
then I can run this code to empty the bound table (tTableList) and fill it with the list of tables.

Private Sub LoadTables()
Dim db As DAO.Database
Dim tdf As TableDef
Dim i As Integer
Dim sSql As String
Const kDATAtbl = "tTableList"
HrGlass
DoCmd.SetWarnings False
sSql = "DELETE * FROM " & kDATAtbl
DoCmd.RunSQL sSql
Set db = CurrentDb
With db
For Each tdf In .TableDefs

If UCase(tdf.Name) <> UCase(kDATAtbl) And Left(tdf.Name, 1) <> "~" Then
If Not isSystem(tdf.Attributes) Then
sSql = "INSERT INTO " & kDATAtbl & " ( TblName ) Values ('" & tdf.Name & "')"
DoCmd.RunSQL sSql
End If
End If
Next

End With
lstTables.RowSource = kDATAtbl
Set tdf = Nothing
Set db = Nothing
HrGlass False
End Sub

Function isSystem(varAttr As Variant)
' This small function is separated out so
' your app could replace this function with
' one of your own. In reality, there's
' little chance you'd need to change
' this function much.
isSystem = ((varAttr And DB_SYSTEMOBJECT) <> 0)
End Function

 

[theDBguy]

Hi. In the combo Row Source, you could try something like:

SELECT [Name] FROM MSysObjects WHERE [Name] Like "Data_*"

 

[mansied]

I have a table bound to the listbox/combo.
then I can run this code to empty the bound table (tTableList) and fill it with the list of tables.

Private Sub LoadTables()
Dim db As DAO.Database
Dim tdf As TableDef
Dim i As Integer
Dim sSql As String
Const kDATAtbl = "tTableList"
HrGlass
DoCmd.SetWarnings False
sSql = "DELETE * FROM " & kDATAtbl
DoCmd.RunSQL sSql
Set db = CurrentDb
With db
For Each tdf In .TableDefs

If UCase(tdf.Name) <> UCase(kDATAtbl) And Left(tdf.Name, 1) <> "~" Then
If Not isSystem(tdf.Attributes) Then
sSql = "INSERT INTO " & kDATAtbl & " ( TblName ) Values ('" & tdf.Name & "')"
DoCmd.RunSQL sSql
End If
End If
Next

End With
lstTables.RowSource = kDATAtbl
Set tdf = Nothing
Set db = Nothing
HrGlass False
End Sub

Function isSystem(varAttr As Variant)
' This small function is separated out so
' your app could replace this function with
' one of your own. In reality, there's
' little chance you'd need to change
' this function much.
isSystem = ((varAttr And DB_SYSTEMOBJECT) <> 0)
End Function

I am new in VBA .How you add this code to your combobox..can you send me a sample ?I appropriate that

 

[Eugene-LS]

I want the user choose the name and then by clicking the bottom can append to a main table .

Private Sub Form_Load()
Dim sVal$
    sVal = "SELECT [Name] FROM MSysObjects " & _
        "WHERE (Flags=0) AND ([Type]=1) ORDER BY [Name];"
    Me.Combo0.RowSource = sVal
End Sub

 

[Gasman]

Private Sub Form_Load()
Dim sVal$
    sVal = "SELECT [Name] FROM MSysObjects " & _
        "WHERE (Flags=0) AND ([Type]=1) ORDER BY [Name];"
    Me.Combo0.RowSource = sVal
End Sub

That gets every table? O/P just wanted those starting with "Data_" ?

 

[theDBguy]

Take a look...

 

[Eugene-LS]

hat gets every table? O/P just wanted those starting with "Data_" ?

Ok! I didn't understand right away.
Just change my code as shown below:

Private Sub Form_Load()
Dim sVal$
    sVal = "SELECT [Name] FROM MSysObjects " & _
        "WHERE ([Name] Like 'Data_*') ORDER BY [Name];"
    Me.Combo0.RowSource = sVal
End Sub

 

[mansied]

Private Sub Form_Load()
Dim sVal$
    sVal = "SELECT [Name] FROM MSysObjects " & _
        "WHERE (Flags=0) AND ([Type]=1) ORDER BY [Name];"
    Me.Combo0.RowSource = sVal
End Sub

Thank you so much .I appreciate .I have one question?

*- how can I add action to append bottom to append selected table in checkbox ?

 

[Eugene-LS]

*- how can I add action to append bottom to append selected table in checkbox ?

Private Sub Command2_Click()
Dim sVal$
    If Me.Combo0.ListIndex = -1 Then
        MsgBox "Wro-o-o-o-ng!!!", vbExclamation
        Me.Combo0.SetFocus
        Exit Sub
    End If
    sVal = Me.Combo0
    sVal = "INSERT INTO Total " & _
        "( MODEL, SERIAL, [MONTH], OPERATIONAL, PLANNED, " & _
        "SCHEDULED, UNSCHEDULED, INDUCED, ELECTIVE, DESCRIPTION ) " & _
        "SELECT MODEL, SERIAL, Month, OPERATIONAL, PLANNED,  " & _
        "SCHEDULED, UNSCHEDULED, INDUCED, ELECTIVE, DESCRIPTION " & _
        "FROM " & sVal
        
    CurrentDb.Execute sVal
End Sub

 

[mansied]

Private Sub Command2_Click()
Dim sVal$
    If Me.Combo0.ListIndex = -1 Then
        MsgBox "Wro-o-o-o-ng!!!", vbExclamation
        Me.Combo0.SetFocus
        Exit Sub
    End If
    sVal = Me.Combo0
    sVal = "INSERT INTO Total " & _
        "( MODEL, SERIAL, [MONTH], OPERATIONAL, PLANNED, " & _
        "SCHEDULED, UNSCHEDULED, INDUCED, ELECTIVE, DESCRIPTION ) " & _
        "SELECT MODEL, SERIAL, Month, OPERATIONAL, PLANNED,  " & _
        "SCHEDULED, UNSCHEDULED, INDUCED, ELECTIVE, DESCRIPTION " & _
        "FROM " & sVal
       
    CurrentDb.Execute sVal
End Sub

I really appreciate of your reply ..my issue was solved .thank you very much