Parameters or DLookUp for multiple records?

[brendanj68]

Hi, am setting up a 2003 Db, which has as its 'data' the table structure (Column Name, datatype, etc) of a SQL2K application. What I am trying to do is call-up a particular table name (through a parameter - as below) from a list of table names (source one), and then either via a subform (?) pull in the corrsponding field definitions from that particualr table (source two).

PARAMETERS [Table Name] Text ( 255 );
SELECT DISTINCTROW Employee.Column_Name, Employee.Type, Employee.Length, Employee.PivotalLabel, Employee.ClarityLabel, Employee.ClarityFieldName, Employee.PivotalDescription, LiveTables.Name, LiveTables.ClarityLink
FROM LiveTables LEFT JOIN Employee ON LiveTables.Name = Employee.TableName
WHERE (((LiveTables.Name)=[Table Name]) AND ((LiveTables.ClarityLink)='Yes'));

While this param has some use, I just can't see a way to retrieve the complete list of all the table names as a starter - let alone the linking bit. Is there something I have completely missed/ or a reference point I can go to to pick up on this type of request. Your help v. apprecaited.

Thanks

 

[CautionMP]

brendanj68,
If I understand your questions correctly your ultimate goal is output to the screen? Create a form that shows the data you want (which will show the complete list by default). Then create a control (combo box, list box…) that has the result you want to Filter for ([Table Name]). Then use the OnClick/OnChange event for that control to Filter the recordset, i.e.:

This will be set in the property sheet of the control you create (ControlParameter for this example).
ControlParamater.RowSource = “SELECT Name FROM LiveTables;”
This will be the Event Procedure for the user action:
Private Sub ControlParamater_OnClick()
Me.Filter = “LiveTables.Name=” & ControlParamater
Me.FilterOn = True
End Sub


I tried my best to pull the actual field/table names from your post.

Hope this nudges you in the right direction.

 

[Zion7]

brendanj68,

Not sure myself, about SQL2k, but in Access, You could use the tableDefs collection, like CautionMP said, to fill a listBox. If you use the "FieldList" property of the list box, it will automatically, give you all the field names.

Or as you loop through the Tabledefs collection, yohu can also, loop through the the fields collection, at the same time, creating a "Rowsource" string, to append to the rowsource property of the (allegedly desired) listbox.

Sub TableFields()

Dim tbl As TableDef, a() As Variant, x As Integer, fField As Field

For Each tbl In CurrentDb.TableDefs
    
        For Each fField In tbl.Fields
        ReDim Preserve a(x)
        a(x) = fField.Name
        x = x + 1
        Next
    
Next

x = 0
For x = 0 To UBound(a)
Debug.Print a(x)
Next

End Sub

I use an array here, not obligatory. And you must create the string, for the rowsource...

sRowSource = sRowSource & ";" & a(x)
ListBox.RowSource = Mid(sRowSource, 2)

This would go, inside the Field loop, and use same idea, inside outer loop (tbl.name). This will give you, both collections at once.

Hope this helps, good luck!

 

[Zion7]

brendanj68, sorry, a little convoluded my code, as far as answering your direct question.

I was showing, both collections at once. The loops should be separated & in different procedures.

loop the tables collecion, in the onload event of the form.
loop the fields collection, on the afterupdate event of the combobox, or dblclick of the listbox.

or again, don't use 2nd loop, just use tablename & fieldlist property of whichever control box you use.

...still confusing, I'm sure.
I'll wait for a response, to even see if im on the right track.

good luck, either way!