Updating the current field (1 Viewer)

[w11184]

Hi,

I am trying to do something which should be quite simple but I can't get it to work. Basically I just written a code that scrolls through a dataset and when it finds a null field it will change it to "-1" but my code isn't working. Any help would be appreciated.

Function FldTest()

Dim db As Database
Set db = CurrentDb

Dim rsMainSurvey As DAO.Recordset
Set rsMainSurvey = db.OpenRecordset("SELECT * FROM tblMainTable", dbOpenDynaset)

Dim fld As DAO.Field

    rsMainSurvey.MoveFirst
    
    Do While Not rsMainSurvey.EOF
        
        For Each fld In rsMainSurvey.fields
        
            If IsNull(fld) Then
            
                With rsMainSurvey
                .Edit
                !fld.Value = "-1"
                .Update
                End With
                
            End If
    
        Next
    
    rsMainSurvey.MoveNext
    Loop
          
    Debug.Print "Update Complete"
    

End Function

Thanks.

 

[pr2-eugin]

Maybe it is not Null.. Try this as the condition to check..

If IsNull(fld) Or Len(fld & vbNullString) = 0 Then

 

[w11184]

Maybe it is not Null.. Try this as the condition to check..

If IsNull(fld) Or Len(fld & vbNullString) = 0 Then

Thanks but that doesn't work either. The error it is giving me is on the line

!fld.Value = "-1"

And the error is "Item not found in this collection". So I am guessing I have incomplete code somewhere but I can't work out where.

 

[pr2-eugin]

Type conversion might be a problem.. Before updating, see what type the field is and use it accordingly, as you know, you cannot assign String to a Number field..

Also remove the .Value after the !fld and see what happens..

!fld = "-1"

 

[w11184]

Type conversion might be a problem.. Before updating, see what type the field is and use it accordingly, as you know, you cannot assign String to a Number field..

Also remove the .Value after the !fld and see what happens..

!fld = "-1"

Same thing...

 

[w11184]

Also I manage to get it to work when I specifically specify a column.

 

[pr2-eugin]

Yea.. got it.. Well fld is not a part of the recordset is it? It represents each and every field in the recordset that is already defined by the For.. Thus, when you use it along with the 'With' puts the whole confusion.. so change your code as..

For Each fld In rsMainSurvey.Fields
    If IsNull(fld) Then
        With rsMainSurvey
            .Edit
            fld = "-1"
            .Update
        End With
    End If
Next

 

[w11184]

That's great. It's working now thanks very much!