
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.
Example:
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
Next
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
Next
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
RS.MoveNext
Loop
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
Next
RS.MoveNext
MsgBox Msg
Msg = ""
Loop
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 **
********************************************************