Check Table For Field

[dandoescode]

I want to check a table, the name of which is input into a form for a given field, if the field exists fine, if not I want to add it. I'm getting an object required 424 error, if someone sees an obvious error or just knows of a better way to do this it would be extremely helpful. Also the code included is just a snippet, the rest of the code works without it.

Dim myfield As Field
exists = 0


For Each myfield In rstTwo.Fields
    If myfield.Name = "Used" Then
        exists = 1
    End If
Next myfield


If exists = 0 Then

DoCmd.RunSQL ("ALTER TABLE " & tableTwo & " ADD Used integer ;")

End If

[dandoescode]

Realized that I should include the entire forms code anyway.

Private Sub Detail_Click()


' Initialize variables

Dim tableOne, tableTwo, fieldName  As String
Dim valueOne, valueTwo As String
Dim db As Database
Dim rstOne, rstTwo, rstData As DAO.Recordset
Dim match As Boolean
Set db = CurrentDb()
tableOne = Me.tableOne
tableTwo = Me.tableTwo
fieldName = Me.fieldName



Dim myfield As Field
exists = 0


For Each myfield In rstTwo.Fields
    If myfield.Name = "Used" Then
        exists = 1
    End If
Next myfield


If exists = 0 Then

DoCmd.RunSQL ("ALTER TABLE " & tableTwo & " ADD Used integer ;")

End If




Set rstOne = db.OpenRecordset(tableOne, dbOpenDynaset)
Set rstTwo = db.OpenRecordset(tableTwo, dbOpenDynaset)
Set rstData = db.OpenRecordset("tableDiffData", dbOpenDynaset)
match = False
LastId = 0



        ' We will implement 3 do loops
        ' The first do loop will input all table one entries and table two order preserving matches
        ' The second loop will add entries not used in the first loop to the end of the input table

        
            

        ' Setup Do Loop to cycle through the entries of the first table
        
        Do While Not rstOne.EOF And match = False
        
        
        
        ' Grab values from the first entry in both tables
        valueOne = rstOne.Fields(fieldName)
        idOne = rstOne.id
        rstTwo.MoveFirst
        valueTwo = rstTwo.Fields(fieldName)
        idTwo = rstTwo.id
        
        ' If the string values match, then write them into the output table, we dont have to worry about preserving order
        ' since this is the first entry
        
                 If valueOne = valueTwo Then
                                            
                rstData.AddNew
                rstData![TableOneValue] = valueOne
                rstData![tableOneId] = idOne
                rstData![tableTwoId] = idTwo
                rstData.Update
                
              
                match = True
                LastId = idTwo
                                           
                                             
                End If
                
                
        rstTwo.MoveNext
                                     
        
        
        ' Setup Do Lookp to look for the first match between the entry in the first table, and the entries in the second table
        
        Do While match = False And Not rstTwo.EOF
        
        
        
        
        valueTwo = rstTwo.Fields(fieldName)
        
        
        
        
        ' If we have a match then we want to check that the match preserves order, if it does then we want to write it to the output
        ' If there is no match that is order preserving we leave the field blank
      
        If valueOne = valueTwo Then
        idTwo = rstTwo.id
                                        
                                       
                                        
                                        
        If LastId < idTwo + 1 Then
                                        
                                        
                                        
                                        
        rstData.AddNew
        rstData![TableOneValue] = valueOne
        rstData![tableOneId] = idOne
        rstData![tableTwoId] = idTwo
        rstData![tableLast] = LastId
                                     
                                     
        rstData.Update
        LastId = idTwo
        match = True
                    
        End If
        
        
        
                                    
                                     
        End If
        
        
        
        
        
        
        rstTwo.MoveNext
        
        
        
        Loop
        
        
        
        
        
        
        ' Set match back to false, and move on to the next entry in the first coloumn
        
        match = False
        
        rstOne.MoveNext
        
        Loop

    
    
    
    
    
    
    








Set rstOne = Nothing
Set rstTwo = Nothing
Set db = Nothing

End Sub

The error found is in

For Each myfield In rstTwo.Fields

[orange]

This

Dim rstOne, rstTwo, rstData As DAO.Recordset

does not

Dim rstOne as a Dao.Recordset
Dim rstTwo as a Dao.Recordset

You must explicitly Dim your variables.
If you do not explicitly Dim variables, they will default to Variant.

See Post #13 here
https://www.accessforums.net/showthr...mber-in-access