Searching arrays in access 2002

[Matt_Hirst]

I have an app that creates a lot on linked mysql tables in my access front-end.

What I am wanting to do is add a form with a combo box, a text box and a button. As soon as the form is loaded the combo box is populated with the fields held in all of the tables - need the names to be unique, thus if the same name appears in more than one table it should only appear once in the combo box.

The text box will then allow entry of some text and when the button is pressed the text is searched for across the field highlighted in the combo box.

Now I have made the form but I can't figure out the best way to get the field names to be in the combo box. Suppose what I am wanting to do is do a search across all of the tables and get the field names, once the field name is retrieved assign this to an array. When assigning it to the array the array needs checking to see if it already exists and if it does then ignore this.

So questions are:

* How do I search an array in access 2002?
* How do I sort an array in access 2002?

regards,

Matt

 

[raskew]

Hi -

Now I have made the form but I can't figure out the best way to get the field names to be in the combo box.

1) Copy/paste the following code to a new standard module, name immaterial so long as it's not the name of one of the subs.

2) In your form, name your combo box 'cboFields'

3) From your form's design view, click on the OnOpen event in properties:

add:

Private Sub Form_Open(Cancel As Integer)
Dim strSQL As String

Call GetField2Description
strSQL = " SELECT DISTINCT FieldName FROM tblFields ORDER BY FieldName;"
With Me.cboFields
   .RowSource = strSQL
   .Requery
   .SetFocus
   .dropdown
End With

End Sub

4) Save the form, then reopen it. What'll happen is:
- tblFields will be created/recreated and populated with info re your tables and fields.
- cboFields.rowsource will be created
- cboFields will dropdown, displaying all unique field names in all the tables of your database.

HTH - Bob

PS - You'll want to take a look at tblFields.

Sub GetField2Description()


'**********************************************************
'Purpose:   1) Deletes and recreates a table (tblFields)
'           2) Queries table MSysObjects to return names of
'              all tables in the database
'           3) Populates tblFields
'Coded by:  raskew
'Inputs:    From debug window:
'           Call GetField2Description
'Output:    See tblFields
'**********************************************************

Dim db As Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim NameHold As String, strSQL As String
Dim typehold As String, SizeHold As String
Dim fielddescription As String, tName As String
Dim n As Long, i As Long
Dim fld As Field
n = 0
Set db = CurrentDb
' Trap for any errors.
    On Error Resume Next
tName = "tblFields"

'Does table "tblFields" exist?  If true, delete it;
db.Execute "DROP TABLE " & tName & ";"

'Create new tblTable
db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));"

strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
   ' Get number of records in recordset
   rs.MoveLast
   n = rs.RecordCount
   rs.MoveFirst
End If

Set rs2 = db.OpenRecordset("tblFields")

For i = 0 To n - 1
  fielddescription = " "
  Set td = db.TableDefs(i)
    'Skip over any MSys objects
    If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then
       NameHold = rs!Name
       On Error Resume Next
       For Each fld In td.Fields
          fielddescription = fld.Name
          typehold = FieldType(fld.Type)
          SizeHold = fld.Size
          rs2.AddNew
          rs2!Object = NameHold
          rs2!FieldName = fielddescription
          rs2!FieldType = typehold
          rs2!FieldSize = SizeHold
          rs2!FieldAttributes = fld.Attributes
          rs2!FldDescription = fld.Properties("description")
          rs2.Update
       Next fld
  
       Resume Next
    End If
    rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Sub

Function FieldType(intType As Integer) As String

Select Case intType
    Case dbBoolean
        FieldType = "dbBoolean"    '1
    Case dbByte
        FieldType = "dbByte"       '2
    Case dbInteger
        FieldType = "dbInteger"    '3
    Case dbLong
        FieldType = "dbLong"       '4
    Case dbCurrency
        FieldType = "dbCurrency"   '5
    Case dbSingle
        FieldType = "dbSingle"     '6
    Case dbDouble
        FieldType = "dbDouble"     '7
    Case dbDate
        FieldType = "dbDate"       '8
    Case dbBinary
        FieldType = "dbBinary"     '9
    Case dbText
        FieldType = "dbText"       '10
    Case dbLongBinary
        FieldType = "dbLongBinary" '11
    Case dbMemo
        FieldType = "dbMemo"       '12
    Case dbGUID
        FieldType = "dbGUID"       '15
End Select

End Function