Null Handler for multiple fields

[Xproterg]

Evening all,

The VBA/Access question of the day is: Is there a way to get VBA to automatically populate fields with "Undefined" or "Unknown" or "N/A" when those fields do not contain any data. I really don't want to have to NULL define every single field individually or rename the fields and run a loop. If anyone knows a way to do this, I could really use the input and would be very greatful for the knowledge. Thanks!

 

[DCrake]

Your suggestion would only work for text fields, to do this you go to the table properties and select the text field and go to the properties of the field and in the Default Value option enter you "Unknown", Not Defined", "N/A" expression. Remember to consider the length of the field and you default value.

 

[Xproterg]

This didn't work how I planned, unfortunately. I went to new record, typed in a value for one textbox, and the rest came up unknown when I clicked over to the next box. Any help?

 

[jdraw]

You really don't need to populate such fields with a text string. The fields already have a Null value.
You could set up a query and have it show "unknown" or whatever, if the filed is Null.

Select
iif(IsNull(myField1),"unknown",myField1),
iif(IsNull(myField2),"unknown",myField2),
iif(IsNull(myField3),"unknown",myField3),
....
iif(IsNull(myFieldn),"unknown",myFieldn)
From MyTable

 

[Xproterg]

Are you suggesting that I change my record source to that? Will that work?

eg:

SELECT iif(IsNull(myField1),"unknown",myField1), iif(IsNull(myField2),"unknown",myField2), iif(IsNull(myField3),"unknown",myField3), iif(IsNull(myFieldn),"unknown",myFieldn) FROM MyTable

Assuming the syntax is valid, would you suggest that I replace my record source with this, or just have this run as a function DoCMD.RunSQL via a command button?

I can see this being a problem if I set it as a record source, as it might set the value of new records before they're completely filled out.

What is your advice jdraw?

NOTE to SELF: When assigning names to controls, use a standard control name, such as control1 or break down into textbox1 and listbox1... that way I can use for statements and have loops do all of my word processing for me.

 

[jdraw]

You can create a query and try it. It's only reading the data. See if it gives you what you need.......

 

[Xproterg]

It seems as though I'm hitting deadends all over the place. The closest I've come to a generic, one-size-fits-all, null/empty string handler is as follow:

Dim db As DAO.database
Dim rst As DAO.Recordset
Dim fld As Field
Dim prm As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Emergency", dbOpenDynaset)
prm = CurrentDb.TableDefs("Emergency").Indexes("PrimaryKey").Fields(0).Name
For Each fld In rst.Fields
If fld.Name = prm Then GoTo fail Else GoTo uber
uber:
db.Execute "update Emergency set " & fld.Name & "='Unknown' where " & fld.Name & "=''"
fail:
Next fld

The above code does not produce any errors; however, it also doesn't do what it's supposed to do, and just makes the monitor blink. No fields are updated.

NOTE: The if then statement is to bypass errors generated when trying to update fields that cannot be updated, eg the primary key.

 

[Rabbie]

It seems as though I'm hitting deadends all over the place. The closest I've come to a generic, one-size-fits-all, null/empty string handler is as follow:

Dim db As DAO.database
Dim rst As DAO.Recordset
Dim fld As Field
 
Set db = CurrentDb
Set rst = db.OpenRecordset("Emergency", dbOpenDynaset)
For Each fld In rst.Fields
On Error GoTo fail
db.Execute "update Emergency set " & fld.Name & "='Unknown' where " & fld.Name & "=''"
fail:
Next

The above code does not produce any errors; however, it also doesn't do what it's supposed to do.

NOTE: The if then statement is to bypass errors generated when trying to update fields that cannot be updated, such as a primary key.

What IF statement

 

[Xproterg]

What IF statement

lol I had to change it... it took me a while to be able to figure out how to get the name of the primary key, as that is pretty much the only field that will produce an error. (The statement is there now)

Error or not, no fields update. Suggestions?

 

[vbaInet]

It can be simply done in the Format property of the textbox or control:

@;"Unknown"

 

[Xproterg]

Just FYI, for anyone who is interested in a solid piece of code that can get rid of the NULL error frustration, I ended up using two peices of advice from two different threads.

Thank you everyone, here is the code:

Private Sub Denullifier_Click()

Dim ctr As Control
For Each ctr In Me.Detail.Controls
   If ctr.ControlType = acTextBox Then
      Select Case ctr.Tag
        Case "fill"
            ctr.Format = "@;Unknown"
        Case Else
            ' nothing
        End Select
    End If
Next ctr

DoCmd.GoToRecord , , acNewRec
End Sub