[AccessD] Put fieldname into a variable

jeffrey.demulling at usbank.com jeffrey.demulling at usbank.com
Thu Oct 23 12:49:49 CDT 2003


Hopefully this might help you get started.  Sorry if I am not understanding
your question correctly.

Sub elementsexist()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim myfield As String
Dim mytype As String

Set fld = Nothing
Set tdf = Nothing
Set rst = Nothing
Set db = Nothing



Set db = DBEngine(0)(0)
db.TableDefs.Refresh
Set rst = db.OpenRecordset("tempverify")
Set tdf = db.TableDefs!tempimport

db.TableDefs.Refresh
tdf.Fields.Refresh

For Each fld In tdf.Fields
    myfield = fld.Name
    If fld.Type = 16 Then
        mytype = "dbBigInt"
    ElseIf fld.Type = 9 Then
        mytype = "dbBinary"
    ElseIf fld.Type = 1 Then
        mytype = "dbBoolean"
    ElseIf fld.Type = 2 Then
        mytype = "dbByte"
    ElseIf fld.Type = 18 Then
        mytype = "dbChar"
    ElseIf fld.Type = 5 Then
        mytype = "dbCurrency"
    ElseIf fld.Type = 8 Then
        mytype = "dbDate"
    ElseIf fld.Type = 20 Then
        mytype = "dbDecimal"
    ElseIf fld.Type = 7 Then
        mytype = "dbDouble"
    ElseIf fld.Type = 21 Then
        mytype = "dbFloat"
    ElseIf fld.Type = 15 Then
        mytype = "dbGUID"
    ElseIf fld.Type = 3 Then
        mytype = "dbInteger"
    ElseIf fld.Type = 4 Then
        mytype = "dbLong"
    ElseIf fld.Type = 11 Then
        mytype = "dbLongBinary"
    ElseIf fld.Type = 12 Then
        mytype = "dbMemo"
    ElseIf fld.Type = 19 Then
        mytype = "dbNumeric"
    ElseIf fld.Type = 6 Then
        mytype = "dbSingle"
    ElseIf fld.Type = 10 Then
        mytype = "dbText"
    ElseIf fld.Type = 22 Then
        mytype = "dbTime"
    ElseIf fld.Type = 23 Then
        mytype = "dbTimeStamp"
    ElseIf fld.Type = 17 Then
        mytype = "dbVarBinary"
    Else
        mytype = "Unknown"
    End If
    rst.MoveFirst
    Do Until rst.EOF
        If rst.Fields("Element") = myfield Then
            rst.Edit
            rst.Fields("Exists") = "Yes"
            rst.Fields("ActualType") = mytype
            rst.Update
            Exit Do
        End If
        rst.MoveNext
    Loop
Next fld

Set fld = Nothing
Set tdf = Nothing
Set rst = Nothing
Set db = Nothing
End Sub
Sub setokstatus()
Dim db As Database
Dim rst As Recordset
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("tempverify")
rst.MoveFirst
Do Until rst.EOF
    If rst.Fields("Exists") = "Yes" Then
        If rst.Fields("CorrectType") = rst.Fields("ActualType") Then
            rst.Edit
            rst.Fields("Ok") = "Yes"
            rst.Update
        Else
            rst.Edit
            rst.Fields("Ok") = "No - Type"
            rst.Update
        End If
    Else
        rst.Edit
        rst.Fields("Ok") = "No - Exists"
        rst.Update
    End If
    rst.MoveNext
Loop
Set rst = Nothing
Set db = Nothing
End Sub


                                                                                                                                 
                    "Mark Boyd"                                                                                                  
                    <MarkBoyd at mcbeeassociates.        To:     AccessD at databaseadvisors.com                                       
                    com>                              cc:                                                                        
                    Sent by:                          Subject:     [AccessD] Put fieldname into a variable                       
                    accessd-bounces at databasead                                                                                   
                    visors.com                                                                                                   
                                                                                                                                 
                                                                                                                                 
                    10/23/2003 12:27 PM                                                                                          
                    Please respond to "Access                                                                                    
                    Developers discussion and                                                                                    
                    problem solving"                                                                                             
                                                                                                                                 
                                                                                                                                 




I would like to put a table's fieldname into a variable, and run code
like the following:

(I've omitted most of the code because of space)

Dim fld as Field

Dim i as Integer



Do until rst.EOF = True

     i = i + 1

     fld = rst![Field" & i & "]

     ... do processing

Loop



Any ideas how this can be done?



Mark Boyd

Sr. Systems Analyst

McBee Associates, Inc.



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com








More information about the AccessD mailing list