Hi,

In Access 2003, I want to make a form where I can pick a table in a
combobox and list all of the fields (or perhaps just some) from it. I
have done multilist boxes where the user picks something from the
listbox on the left and it moves to the right list box, etc... This
time I want to list all or just some of the fields from a table in the
left hand list box.

I then want to populate a recordset or temporary table with the names
of the selected fields and the name of the table they came with. Now,
I haven't thought everything through yet as there will be
relationships between the client table and the reservation table, the
reservation table and the travel agent table , etc so I would also
need to include the key fields from the tables (but not necessarily
list them in the list boxes).

Is there already something that does this, either free or for
purchase? I am trying to get away from hard-coding the table and
field names in case they change down the road.

It will probably be just for my use when clients want data extracted
for their use or someone elses use. I may eventually include it in the
application.

Thanks and Happy 2011!

-paulw

On Sun, 02 Jan 2011 14:13:04 -0700, PW
<emailaddyinsig (AT) ifIremember (DOT) com> wrote:

You can go through the fields collection of a tabledef to fill in the
listboxes. You could then assemble a query using VBA code from the
listboxes to extract the data.

This would probably take me an hour or three to create but then I am
familiar with the collections. (And I have Access 97 help which does
a much better job of allowing me to search for this kind of stuff than
any newer version of Access help.)

Finally, for example, populate an Excel spreadsheet
Modules: Transferring Records to Excel with Automation
http://www.mvps.org/access/modules/mdl0035.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

Here is some code from my documentation tool. It takes the field names and other properties and puts them into a table. You can extract tables from the tableDefs collection the same way or you can use a query against the MSysObjects table.

Sub Create_tblTableFields()

Dim db As DAO.Database
Dim tblLoop As DAO.TableDef
Dim fldLoop As DAO.Field
Dim TD1 As DAO.TableDef
Dim QD1 As DAO.QueryDef
Dim TempSet1 As DAO.Recordset
Dim strDatabase As String
Dim ThisDB As DAO.Database
Dim CountTables As Integer

On Error GoTo Create_tblTableFields_Error

On Error GoTo Err_Create_tblTableFields
'strDatabase = "C:\hartman\LinkDB2.mdb"
strDatabase = Forms!frmPrintDoc!txtDBName

CountTables = 0
Set ThisDB = CurrentDb()
If strDatabase = "" Then
Set db = CurrentDb()
Else
Set db = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
End If

db.Containers.Refresh

Set QD1 = ThisDB.QueryDefs!QdeltblTableFields
QD1.Execute
Set TD1 = ThisDB.TableDefs!tblTableFields
Set TempSet1 = TD1.OpenRecordset

' Loop through TableDefs collection.
For Each tblLoop In db.TableDefs
' Enumerate Fields collection of each
' TableDef object.
CountTables = CountTables + 1
Forms!frmPrintDoc!txtTableCount = CountTables
Forms!frmPrintDoc!txtTableName = tblLoop.Name
Forms!frmPrintDoc.Repaint

If Left(tblLoop.Name, 4) = "MSys" Or Left(tblLoop.Name, 2) = "xx" Or Left(tblLoop.Name, 2) = "zz" _
Or Left(tblLoop.Name, 1) = "~" Then
Else
For Each fldLoop In tblLoop.Fields

TempSet1.AddNew
TempSet1!TableName = tblLoop.Name
TempSet1!FieldName = fldLoop.Name
TempSet1!OrdinalPosition = fldLoop.OrdinalPosition
TempSet1!AllowZeroLength = fldLoop.AllowZeroLength
TempSet1!DefaultValue = fldLoop.DefaultValue
TempSet1!Size = fldLoop.Size
TempSet1!Required = fldLoop.Required
TempSet1!Type = fldLoop.Type
TempSet1!ValidationRule = fldLoop.ValidationRule
TempSet1!Attributes = fldLoop.Attributes
On Error Resume Next ' the following property is only available when it is not null
TempSet1!Description = fldLoop.Properties("Description")
On Error GoTo Err_Create_tblTableFields
TempSet1!FieldType = GetType(fldLoop.Type)
TempSet1!Caption = fldLoop.Properties("Caption")
If fldLoop.Attributes And dbAutoIncrField Then 'performs bitwise operation
TempSet1!AutoNum = True
TempSet1!Required = True
Else
TempSet1!AutoNum = False
End If
TempSet1.Update

Next fldLoop
End If
Next tblLoop

Exit_Create_tblTableFields:
db.Close
Exit Sub

Err_Create_tblTableFields:
Select Case Err.Number
Case 3043
MsgBox "Please select a valid database", vbOKOnly
Case 91 ' db was not opened so it cannot be closed.
Exit Sub
Case Else
MsgBox Err.Number & "-" & Err.Description
End Select
Resume Exit_Create_tblTableFields

On Error GoTo 0
Exit Sub

Create_tblTableFields_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Create_tblTableFields of Module DocumentCollections"
End Sub

Submitted via EggHeadCafe
Microsoft Silverlight For Beginners
http://www.eggheadcafe.com/training-...lverlight.aspx

On Sun, 02 Jan 2011 14:56:06 -0700, Tony Toews
<ttoews (AT) telusplanet (DOT) net> wrote:


Thanks Tony. I have done lots of automation to Excel with Visual
FoxPro and have not tried to figure how to do it in VBA (some people
can find what they need in the Excel help file but I never could!).

Putting what we need from the extract to Excel may work out great in
addition to a CSV file or whatever.

-paul

On Mon, 03 Jan 2011 05:21:26 GMT, Pat Hartman
<patriciahartman (AT) att (DOT) net> wrote:

Looking good Pat. Thanks!

-paulw

Tony Toews <ttoews (AT) telusplanet (DOT) net> wrote in
news:trs1i6p7ehovnqcaa8i6his9s78akfs1e7 (AT) 4ax (DOT) com:

I'm not keeping up with the discussion, but if you just want a list
of fields in a listbox, you can set the listbox type to FieldList
(instead of Value List or Table/Query) and then set the Row Source
to an appropriate SQL string that includes the fields you want
listed.

I've done this in the past, and I would just say it's probably not
worth the effort.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/