By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,480 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

Table names in combo box

P: n/a
Hi,
I want to use a combo box to select from tables in a database.
(Specifically, tables with a certain string in the name ... e.g MarkBk7l,
Markbk8R etc.) How do I get a list of table into a combo box? I presume I
can limit it to these with a 'WHERE' statement?

Thanks in advance
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"JohnM" <jo**@jmawer.demon.co.uk> wrote in message
news:dj*******************@news.demon.co.uk...
Hi,
I want to use a combo box to select from tables in a database.
(Specifically, tables with a certain string in the name ... e.g MarkBk7l,
Markbk8R etc.) How do I get a list of table into a combo box? I presume
I can limit it to these with a 'WHERE' statement?

Thanks in advance


Two options - the first uses internal system tables which could be subject
to change so theoretically should not be relied on. The second is slightly
more code but is better documented.

1) Set the RowSourceType of the combo to table/query and write a query like
the following:

SELECT MSysObjects.Name AS TableName
FROM MSysObjects
WHERE
((MSysObjects.Type=1) And
(MSysObjects.Flags=0) And
(MSysObjects.Name Like "MarkBk*"))
ORDER BY MSysObjects.Name;
2) Set the RowSourceType of the combo to Value List and use a function to
get the table names. This function could called in the form's open event
where you write Me.cboWhatever.RowSource = GetTableList() The function is
defined as follows:
Public Function GetTableList() As String

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strList As String

Set dbs = CurrentDb

For Each tdf In dbs.TableDefs
If tdf.Name Like "MarkBk*" Then
strList = ";""" & tdf.Name & """" & strList
End If
Next tdf

If Len(strList) > 1 Then
strList = Mid$(strList, 2)
End If

GetTableList = strList

Exit_Handler:
On Error Resume Next
Set tdf = Nothing
Set dbs = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.