Access: Need to display a list of tables in a drop down window (1 Viewer)

mansied

Member
Local time
Today, 08:26
Joined
Oct 15, 2020
Messages
99
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
 

Attachments

  • Test.accdb
    6.4 MB · Views: 208

Ranman256

Well-known member
Local time
Today, 08:26
Joined
Apr 9, 2015
Messages
4,337
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.

Code:
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

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Hi. In the combo Row Source, you could try something like:

SELECT [Name] FROM MSysObjects WHERE [Name] Like "Data_*"
 
Last edited:

mansied

Member
Local time
Today, 08:26
Joined
Oct 15, 2020
Messages
99
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.

Code:
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

Registered User.
Local time
Today, 15:26
Joined
Dec 7, 2018
Messages
481
I want the user choose the name and then by clicking the bottom can append to a main table .
Code:
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
 

Attachments

  • Test_v02.zip
    695.4 KB · Views: 243

Gasman

Enthusiastic Amateur
Local time
Today, 12:26
Joined
Sep 21, 2011
Messages
14,038
Code:
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

I’m here to help
Staff member
Local time
Today, 05:26
Joined
Oct 29, 2018
Messages
21,357
Take a look...
 

Attachments

  • Test(9).zip
    719.9 KB · Views: 243

Eugene-LS

Registered User.
Local time
Today, 15:26
Joined
Dec 7, 2018
Messages
481
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:
Code:
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

Member
Local time
Today, 08:26
Joined
Oct 15, 2020
Messages
99
Code:
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 ?
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:26
Joined
Feb 19, 2002
Messages
42,970
Don't set the combo's RowSource in code. It doesn't change. Just set the RecordSource property in design mode.

If you want to add a filter, add a control to the form. Change the Where Clause of the RowSource to reference that fields.
Where TableName Like Forms!yourform!txtTableName

If you want to give the user the most flexibility, have him put in the wilid cards. Otherwise do it this way:

Where TableName Like "*" & Forms!YourForm!txtTableName & "*"

And finally, you should NOT be doing this at all. Users should NEVER, EVER have direct access to tables. You have no control over what they do and they can change/delete/add anything. That is why we ALWAYS use forms to display data for users. That way we can add code to the form's BeforeUpdate event to validate the data and ensure invalid records do not get added. You can also prevent deletes entirely or validate them and sometimes allow and sometimes deny as appropriate.

What you are doing may appear to give the user flexibility but all it does is expose the data to danger. How many tables do you have? Why can't you just make forms? If you insist on doing this, make all the query you create not updateable by adding aggregation.
 

Eugene-LS

Registered User.
Local time
Today, 15:26
Joined
Dec 7, 2018
Messages
481
*- how can I add action to append bottom to append selected table in checkbox ?
Code:
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
 

Attachments

  • Test_v03.zip
    695 KB · Views: 257

mansied

Member
Local time
Today, 08:26
Joined
Oct 15, 2020
Messages
99
Code:
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
 

Users who are viewing this thread

Top Bottom