VB4-32 / Access 2.0 - Fields Collection Problem 
Author Message
 VB4-32 / Access 2.0 - Fields Collection Problem

I am trying to build a database browser that will allow for the user
to select an MDB file and list the available tables, fields, and field
values and then generate a query on the fly.
I have no problems putting the table names, field names, or values
into listboxes. I can even build a query based on the value selected
in the values list...IF that field is of the STRING type.
In the SQL statement I am building, I am concatenating the single
quotes into the statement. This is fine for STRING data types, but if
the user selects a field of another data type, the SQL statement is
invalid. I understand this but can't seem to find a way to return the
data type of the selected field.

List1 contains the table names
List2 contains the field names
List3 contains the field values
I am using the variables Listx.List(Listx.ListIndex) for selections.

Sub cmdListTables_Click()
' Fill List1 with the table names
        If Not Left(DB.TableDefs(i).Name, 4) = "MSys" Then
                List1.AddItem DB.TableDefs(i).Name
End Sub

Sub cmdListFields_Click()
' Put the field names into List2

        ' Make sure we have selected a table
        If Not List1.ListIndex = -1 Then

            Set TD = DB.TableDefs(List1.List(List1.ListIndex))

            For i = 0 To TD.Fields.Count - 1
                List2.AddItem TD.Fields(i).Name
            End If
End Sub

Sub cmdListValues_Click()
' Make sure we have a field selected
If Not List2.ListIndex = -1 Then

SQL = "SELECT [" & List2.List(List2.ListIndex) & "] FROM [" & TD.Name
& "]

Set RS = DB.OpenRecordset(SQL, dbOpenDynaset)

Do While Not RS.EOF
' Check for null values before trying to add them to the list
        If Not IsNull(RS(0)) Then
            List3.AddItem RS(0)
        End If
End Sub

Ok, now that I have the values in List3, I want to be able to select
one of them, build an SQL statement to create a recordset, and show
the details of that record. Doing this directly is not a problem, but
I need to check the TYPE of the field first so I will know whether or
not to use the single quotes in the SQL statement.

Currently I have this to display the record in a message box:

SQL = "SELECT * FROM [" & List1.List(List1.ListIndex) & "]"
SQL = SQL & " WHERE [" & List2.List(List2.ListIndex) & "]"
SQL = SQL & " = '" & List3.List(List3.ListIndex) & "'"
Set RS = DB.OpenRecordset(SQL)
Do Until RS.EOF
    For i = 0 To RS.Fields.Count - 1
        Msg = Msg & RS.Fields(i) & vbCrLf
    MsgBox Msg
    Msg = ""

If the selected field is not of the STRING datatype, it crashes.
So, I thought I would store the field name in a variable and check
it's type, but for some reason, I can't do it. I have checked the
cariable assignment in the debug windows and it is fine, but I can't
use it in the code for checking the datatype!

' This prints the field name in the debug window
TheField = List2.List(List2.ListIndex)
Debug.Print TheField

But, this will not work unless I hardcode the fieldname into it:
Select Case TD.Fields(" & TheField & ").Type
Nor will this:
Select Case TD.Fields![" & TheField & "].Type
But this will:
Select Case TD.Fields("LastName").Type
and this will:
Select Case TD.Fields![LastName].Type

Please, if anyone has a solution to this, let me in on it. I am losing
my mind !!

Thanks in advance,
Ed Phillippe

**  Southern Indiana Bass Fishing / Outdoor Software  **
**     http://www.*-*-*.com/ ~dlion/mypage.htm    **

Fri, 19 Mar 1999 03:00:00 GMT  
 VB4-32 / Access 2.0 - Fields Collection Problem

<stuff deleted>

>' This prints the field name in the debug window
>TheField = List2.List(List2.ListIndex)
>Debug.Print TheField
>But, this will not work unless I hardcode the fieldname into it:
>Select Case TD.Fields(" & TheField & ").Type
>Nor will this:
>Select Case TD.Fields![" & TheField & "].Type
>But this will:
>Select Case TD.Fields("LastName").Type
>and this will:
>Select Case TD.Fields![LastName].Type

It's no surprise that this doesn't work.  You have

   Select Case TD.Fields(" & TheField & ").Type

Your database probably doesn't have a field named " & TheField & ".  You want
to have this instead:

   Select Case TD.Fields(TheField).Type

Bob Rossney

Tue, 23 Mar 1999 03:00:00 GMT  
 [ 2 post ] 

 Relevant Pages 

1. VB4 32 and ms Access 2.0

2. VB4-32, Access 2.0, And CompactDatabase

3. VB4 - 32 -> Access 2.0 Security

4. Access 2.0 vb4 32 bit

5. Access 2.0 System.mda file with VB4 16 and 32

6. Unable to open Access 2.0 database from VB4-32

7. VB4.0 (32-bit) access 7.0 empty datetime field

8. VB 4.0 32 bit Access 2.0 access

9. VB 4.0 32 bit Access 2.0 access

10. VB4.0 Win 32/Pro - Access 7.0 Security problem

11. VB4-32 & Access DB Seek problem

12. VB4/32, Access 7, NT351SP3 - DB Problems


Powered by phpBB® Forum Software