Field Objects 
Author Message
 Field Objects

I believe I need help on an error handling situation using Access 97. Table
One has a field called Test_NM which contains a list of all possible fields
which may appear in Table Two. I have an outer loop in my program which
loops through Table One, reading a Test_NM, one record at a time. With this
name I Set it to a Field name in Table Two :

         MParmFld = rstARAR.Fields("test_nm") 'Parameter name to check for
        strQ = "Q_" & MParmFld
        Set Fld1 = rstPARM.Fields(MParmFld)
        Set Fld3 = rstPARM.Fields(strQ)

If the field exists in Table Two, the program continues sucessfully on. But
if the field does not exist in Table Two an error is generated. My error
handler handles this error the first time it occurs, and the outer loop
reads the next record (next Text_NM) in Table One. When the error occurs the
second time the program fails (run-time error 3265, item not found in this
collection).

Can my error handler only handle one instance of an error?

I think I am going to go another route: I think I can test Table Two to see
if Test_NM is a member of the fields collection? If it is, the program can
continue on. Is this the best route to go?

Thanks,
Rusty

(The whole code is below)

Option Explicit
Dim Db As Database
Dim rstPARM As Recordset, rstARAR As Recordset
Dim Td1 As TableDef, Td2 As TableDef
Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
Dim intCount As Integer
Dim MParm As Variant, MQParm As Variant
Dim MValue As Double
Dim strTableName As String
Dim strRSRTableName As String
Dim strValid As String, strQ As String
Dim MParmFld As String, MQParmFld As String, Qual1 As String

Private Sub cmdExit_Click()
    Set Db = Nothing    'this releases memory from the Db object
    Unload Me           'unloads the form
End Sub

Private Sub cmdFind_Click()
    'On Error GoTo 100
    On Error GoTo Starthere
    Set Db = Workspaces(0).OpenDatabase("E:\Data97\Data97.mdb")
    Set rstARAR = Db.OpenRecordset("RSR_Groundwater_Criteria") 'Table One
    Set rstPARM = Db.OpenRecordset("vocs")    'open Table Two
    strValid = "A"
    Set Fld2 = rstARAR.Fields("value")
    Qual1 = "A"    'Define qualifier to use for RSR
    rstPARM.MoveFirst
'Starthere:
    Do While Not rstARAR.EOF
         MParmFld = rstARAR.Fields("test_nm") 'Parameter name to check for
        strQ = "Q_" & MParmFld
        Set Fld1 = rstPARM.Fields(MParmFld)
        Set Fld3 = rstPARM.Fields(strQ)
         MValue = Fld2.Value
         Do While Not rstPARM.EOF
            MParm = Fld1.Value
            MQParm = Fld3.Value
            If MParm >= MValue Then
                If InStr(MQParm, Qual1) = 0 Or IsNull(InStr(MQParm, Qual1))
Then
                    rstPARM.Edit
                    Fld3.Value = Fld3.Value & Qual1
                    rstPARM.Update
                End If
            End If
            rstPARM.MoveNext ' Go to next Data record
         Loop
'100      rstARAR.MoveNext ' Go to next ARAR record
Starthere:
    rstARAR.MoveNext ' Go to next ARAR record
    rstPARM.MoveFirst
    Loop
    rstPARM.Close
    rstARAR.Close
Resume Starthere
End Sub



Wed, 07 Mar 2001 03:00:00 GMT  
 Field Objects
Your error-handler isn't a real error-handler. I think it works better if you change

  On Error GoTo Starthere

into:

  On Error GoTo Error_Handler

and change the last lines into:

Starthere:
     rstARAR.MoveNext ' Go to next ARAR record
     rstPARM.MoveFirst
   Loop
   rstPARM.Close
   rstARAR.Close
Exit Sub

Error_Handler:
  Resume Starthere
End Sub


Quote:
> I believe I need help on an error handling situation using Access 97. Table
> One has a field called Test_NM which contains a list of all possible fields
> which may appear in Table Two. I have an outer loop in my program which
> loops through Table One, reading a Test_NM, one record at a time. With this
> name I Set it to a Field name in Table Two :

>          MParmFld = rstARAR.Fields("test_nm") 'Parameter name to check for
>         strQ = "Q_" & MParmFld
>         Set Fld1 = rstPARM.Fields(MParmFld)
>         Set Fld3 = rstPARM.Fields(strQ)

> If the field exists in Table Two, the program continues sucessfully on. But
> if the field does not exist in Table Two an error is generated. My error
> handler handles this error the first time it occurs, and the outer loop
> reads the next record (next Text_NM) in Table One. When the error occurs the
> second time the program fails (run-time error 3265, item not found in this
> collection).

> Can my error handler only handle one instance of an error?

> I think I am going to go another route: I think I can test Table Two to see
> if Test_NM is a member of the fields collection? If it is, the program can
> continue on. Is this the best route to go?

> Thanks,
> Rusty

> (The whole code is below)

> Option Explicit
> Dim Db As Database
> Dim rstPARM As Recordset, rstARAR As Recordset
> Dim Td1 As TableDef, Td2 As TableDef
> Dim Fld1 As Field, Fld2 As Field, Fld3 As Field
> Dim intCount As Integer
> Dim MParm As Variant, MQParm As Variant
> Dim MValue As Double
> Dim strTableName As String
> Dim strRSRTableName As String
> Dim strValid As String, strQ As String
> Dim MParmFld As String, MQParmFld As String, Qual1 As String

> Private Sub cmdExit_Click()
>     Set Db = Nothing    'this releases memory from the Db object
>     Unload Me           'unloads the form
> End Sub

> Private Sub cmdFind_Click()
>     'On Error GoTo 100
>     On Error GoTo Starthere
>     Set Db = Workspaces(0).OpenDatabase("E:\Data97\Data97.mdb")
>     Set rstARAR = Db.OpenRecordset("RSR_Groundwater_Criteria") 'Table One
>     Set rstPARM = Db.OpenRecordset("vocs")    'open Table Two
>     strValid = "A"
>     Set Fld2 = rstARAR.Fields("value")
>     Qual1 = "A"    'Define qualifier to use for RSR
>     rstPARM.MoveFirst
> 'Starthere:
>     Do While Not rstARAR.EOF
>          MParmFld = rstARAR.Fields("test_nm") 'Parameter name to check for
>         strQ = "Q_" & MParmFld
>         Set Fld1 = rstPARM.Fields(MParmFld)
>         Set Fld3 = rstPARM.Fields(strQ)
>          MValue = Fld2.Value
>          Do While Not rstPARM.EOF
>             MParm = Fld1.Value
>             MQParm = Fld3.Value
>             If MParm >= MValue Then
>                 If InStr(MQParm, Qual1) = 0 Or IsNull(InStr(MQParm, Qual1))
> Then
>                     rstPARM.Edit
>                     Fld3.Value = Fld3.Value & Qual1
>                     rstPARM.Update
>                 End If
>             End If
>             rstPARM.MoveNext ' Go to next Data record
>          Loop
> '100      rstARAR.MoveNext ' Go to next ARAR record
> Starthere:
>     rstARAR.MoveNext ' Go to next ARAR record
>     rstPARM.MoveFirst
>     Loop
>     rstPARM.Close
>     rstARAR.Close
> Resume Starthere
> End Sub



Wed, 07 Mar 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Change Field object to bind to another field

2. Set Field Object

3. Performance problems when reading schema data from field objects

4. Getting the caption property from a field object in VBA

5. Change CollatingOrder for a Field object

6. Word Fields object problem

7. Fields object and Footnotes

8. File Field object

9. field object

10. Problems printing Field Object OLE (Word)

11. Trying to copy DAO.Field object between TableDefs

12. How to set a User Defined Property for a Field object

 

 
Powered by phpBB® Forum Software