Update all Null Fields to a specific number using VBA

[KP_SoCal]

Hi all. I'm looking for a block of code that will update all null fields in a table to a specific number such as 9999 for instance.

Under normal circumstances I would just create an update query with several iif statements to accommodate this. Or I could create a table that has each field set to a default of zero and then delete and append new data as needed.

My problem is that I have a table that fluctuates in the number of fields. For instance, in one run I may have Field 1 thru Field 24, while a second run may return Field 1 thru Field 50 (this is due to some cross table queries I generate). So I need a function that will loop through each field and replace null values with a value that I specify. Does anyone have any ideas how this can be accomplished?

Thanks so much in advance...

 

[Galaxiom]

This can be done but I wonder why you would.
I suspect you might be trying to work around something that can be done more elegantly.

 

[Galaxiom]

But why are you substituting a fixed value in place of a Null?
Normally one would deal with Nulls as encountered, usually with the Nz function.

Substituting a string in place of null is just adding unnecessary storage.

 

[Galaxiom]

Would it work for you to set a default value of zero in the table?
That way there won't be any nulls when new records are added.

 

[KP_SoCal]

Does anyone else have any alternative suggestions? The reason why I'm looking to accomplish this through a VBA function is because I have several tables involved whose number of fields change frequently. It would be counter productive for me to manually go back and set the default value to zero each time the table was recreated.

 

[c_smithwick]

Try the following:

Public Sub changeNulls(strTable As String)
   
  Dim rst As DAO.Recordset
  Dim fld As Field
   
  Set rst = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)
   
  With rst
   
    Do While Not .EOF
   
      For Each fld In .Fields
   
        If IsNull(fld.Value) Then
          .Edit
   
            Select Case fld.Type
   
              Case dbByte, dbBigInt, dbDecimal, dbDouble, dbFloat, dbInteger, dbLong, dbSingle, dbNumeric
                fld.Value = 9999
     
              Case dbChar, dbText, dbMemo
                fld.Value = ""
     
              Case Else
                'Do Nothing
     
            End Select
   
          .Update
        End If
   
      Next
   
      .MoveNext
    Loop
   
    .Close
  End With
   
  Set rst = Nothing
   
End Sub

 

[KP_SoCal]

Thanks for the reply. I placed your code in a module, but couldn't get it execute properly. Am I missing a key step? Sorry, I'm a super novice when it comes to VBA. I attached the database that I'm working with.

Thanks again for your help!

 

[jdraw]

Thanks for the reply. I placed your code in a module, but couldn't get it execute properly. Am I missing a key step? Sorry, I'm a super novice when it comes to VBA. I attached the database that I'm working with.

Thanks again for your help!

I looked at your database and vba.
You need to change to this. It works fine for table1 replacing NULL with 9999.

In vba, you substitute your "real" table for the variable(strTable) in the sub procedure.
changeNulls(strTable As String)

Public Sub ExecuteCode()
Call changeNulls("TABLE1")
MsgBox "Worked!"
End Sub

 

[KP_SoCal]

jdraw, that did it. Thanks! Now my question is, is there a way to apply this to all tables in my Db by writing only one sub routine? I have about 15 tables.

Below is an example of where I'm wanted to head, but obviously the way I have it written below will currently not work.

Public Sub ExecuteCode()
Call changeNulls("TABLE1, TABLE2, TABLE3, TABLEA, TABLEB")
MsgBox "Worked!"
End Sub

Worst case scenario, I could always do something like this, which is not as clean but it works.

Public Sub ExecuteCode()
Call changeNulls("TABLE1")
Call changeNulls("TABLE2")
Call changeNulls("TABLE3")
Call changeNulls("TABLEA")
Call changeNulls("TABLEB")
MsgBox "Worked!"
End Sub

 

[c_smithwick]

Try the below mod:

Public Sub changeNulls()
   
  Dim rst As DAO.Recordset
  Dim fld As Field
  Dim tdf As TableDef
   
  For Each tdf In CurrentDb.TableDefs
    Set rst = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
   
    With rst
   
      Do While Not .EOF
   
        For Each fld In .Fields
   
          If IsNull(fld.Value) Then
            .Edit
   
              Select Case fld.Type
   
                Case dbByte, dbBigInt, dbDecimal, dbDouble, dbFloat, dbInteger, dbLong, dbSingle, dbNumeric
                  fld.Value = 9999
     
                Case dbChar, dbText, dbMemo
                  fld.Value = ""
     
                Case Else
                  'Do Nothing
     
              End Select
   
            .Update
          End If
   
        Next
   
        .MoveNext
      Loop
   
      .Close
    End With
   
    Set rst = Nothing
  Next
   
End Sub

 

[KP_SoCal]

When I try and run this, it gives me an error message, "Cannot update. Database or object is read-only."

When I run the debugger, it highlights the

.Edit

that is right below the

If IsNull(fld.Value) Then

line. Any ideas?

 

[Galaxiom]

The reason why I'm looking to accomplish this through a VBA function is because I have several tables involved whose number of fields change frequently. It would be counter productive for me to manually go back and set the default value to zero each time the table was recreated.

Creating and deleting tables is best avoided. Much better to clear the records from an existing table and append new records. This allows the properties of the fields, including the format and default value, to be controlled. In a Make table query Access guesses the format.

Moreover, objects in Access are indexed with a number. Each time an object is created a new number is used. Eventually Access runs out of index numbers and no new objects can be created until the persistent objects are exported to a new database. I have seen the number 768 mentioned have not been able to confirm this.

Remember too that added then deleted records or tables will persist in the database until it is compacted. Temporary records and tables are best created in a separate linked database.

 

[c_smithwick]

Need to check to make sure the table is not a system object or hidden object. Add code to test and make sure tdf.Attribute <> dbSystemObject or tdf.Attribute <> dbHiddenObject before attempting to Edit