Database Help(I know someone knows this

[Chris_SE]

Okay, I am currently in the middle of a database project. Heres the problem. I need to be able to search through a database and say puts the results into a listbox. I also need a way for the user to be able to click the results and load that dataset. Does anyone understand what I'm going for? Kindof like looking up an item in a help file

If I'm reading this correctly, you want to retrieve the table and query definitions from a given database, show them in a listbox, and show the data in some form afterwards.

This will retrieve the tables. By using Querydefs instead of tabledefs you should be able to get the querys. You'll have to modify them so that only the select queries will show up:

Sub List_Box_Tables(LstBox As ListBox, DataBaseName As String, Optional ExclusionString As String)
'This subroutine will initialize a ListBox with the tables
' belonging to DataBaseName. Table names containing ExclusionString will
' NOT be listed.
Dim DBS As Database, TBL As TableDef, cnt As Integer, Textline As String, i As Integer


On Error GoTo Xit_List_Box_Tables
Set DBS = OpenDatabase(DataBaseName)

cnt = DBS.TableDefs.Count - 1
While cnt >= 0
Textline = DBS.TableDefs(cnt).Name
If Len(ExclusionString) > 0 Then
If InStr(Textline, ExclusionString) = 0 Then
If Text_In_List(LstBox, Textline) < 0 Then
LstBox.AddItem Textline
End If
End If
Else
If Text_In_List(LstBox, Textline) < 0 Then
LstBox.AddItem Textline
End If
End If
cnt = cnt - 1
Wend
Xit_List_Box_Tables:
End Sub


As far as showing the data, you'll have to set up a list box, or combo box, or something similiar based on the selected item.

Good Luck
DerFarm

[Chris_SE]

thanks

oops, just realized I forgot to include the Text_In_List box. This is used to guarrantee that the same line doesn't get into the box twice. For your purposes (getting queries and tables) it doesn't matter, but I use the above routine to add things without always clearing it.



Function Text_In_List(LstBox As ListBox, Txtline As String) As Integer
'This routine will return the index of the matching entry to a ListBox.
' A -1 indicates no match

Dim varitm As Variant, result As Integer, i As Integer

i = LstBox.ListCount - 1
On Error GoTo Xit_Text_In_List
For result = i To 0 Step (-1)
If LstBox.List(result) = Txtline Then GoTo Xit_Text_In_List
Next result

Xit_Text_In_List:
Text_In_List = result
End Function


Good Luck
DerFarm

[Mad Compie]

The LB_FINDSTRING message for the SendMessage API works much faster and easier to look up a Listbox:

'This project needs a ListBox, named List1 and a TextBox, named Text1
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Any) As Long
Const LB_FINDSTRING = &H18F
Private Sub Form_Load()
    'Add some items to the listbox
    With List1
        .AddItem "Computer"
        .AddItem "Screen"
        .AddItem "Modem"
        .AddItem "Printer"
        .AddItem "Scanner"
        .AddItem "Sound Blaster"
        .AddItem "Keyboard"
        .AddItem "CD-Rom"
        .AddItem "Mouse"
    End With
End Sub
Private Sub Text1_Change()
    'Retrieve the item's listindex
    List1.ListIndex = SendMessage(List1.hwnd, LB_FINDSTRING, -1, ByVal CStr(Text1.Text))
End Sub

WHOOO BOY! were you ever right, Compie.

makes it easier to program to.


Thanks
DerFarm