Odd complaint about encountering NULL values

[mdlueck]

Greetings,

I have generic validation in my forms. The same validation is involked for add / edit record forms.

If I use the add form and try adding a null record, the validation correctly indicates the error.

If I blank out the fields on the edit form and try committing, the validation routine bombs when it tries to ingest the values from the form fields.

Now, this is the same validation routine that correctly identified when I tried submitting a blank add form. What is different between the add forms default state and deleting the contents of fields on the edit form? To me I see it as the same. However to Access / VBA... ggggrrrrr....

Code blows up here:

  'Receive fields from the UI
  Me.userid = MePointer.flduserid.Value
  Me.username = MePointer.fldusername.Value
  Me.active = MePointer.fldactive.Value

MePointer is a form variable of the calling form having passed Me ByRef. That is how I am able to use the same validation class for both the add/edit forms.

Sample error log generated

Date: 20110831 Time: 16:17:36 UserID: c_mlueck
AppErrorMsg: Class: clsObjUIValidationAuthTbl, Function: Validate()
Error Source: Fandango_FE
Error Number: 94
Error Description: Invalid use of Null
MessageText: Error not found.

Is there multiple NULL values a field can have? One when it is left in its default (unpopulated) state, and another when I delete the contents of the field's value?

 

[Galaxiom]

There is the difference between Null and the Null String ("" or vbNullString).

 

[mdlueck]

So that difference shows up in "1) having never had a value vs 2) deleting the field's value"?

My code works with scenario #1 and blows up with #2. How can I safely encounter the #2 scenario?

To me the two seem like the same thing. (shrug)

Thanks

 

[JANR]

Len(Control & vbNullString) = 0 <--- Nothing in control

JR

 

[CBrighton]

A field with the contents deleted holds the zero length string "" rather than null.

You could catch this on the after update event to set it back to null:

If FieldName = "" then
    FieldName = Null
End If

Or you could just check for zero length strings as well as checking for nulls.

 

[mdlueck]

It sounds like I need to test the controls BEFORE pulling their values into the Validation class, correct?

I was hoping to simply pull in the values, then test for invalid values. That is where I am getting bitten. The code is able to pull in a field which never had a value, but is unable to pull in a field which had its value blanked out.

JANR: I guess I will attempt your example. Thanks!

 

[mdlueck]

This works as a work-around:

  'Receive fields from the UI
  If Len(MePointer.fldtitle.Value & vbNullString) = 0 Then
    Me.title = ""
  Else
    Me.title = MePointer.fldtitle.Value
  End If

Not as slick as a one-liner...

Thank you!

 

[CBrighton]

You could always create a function to do it for you, then have a single line calling the function in your code if it matters to you.

Plus it would mean that the same function can be called every time you want to check for null / zero length strings.

It would just need to be passed the control name (e.g. fldtitle), the form name (as Me is unlikely to work in a module) and the field name (e.g. title), this would make it usable on any control & any field on any table.

 

[mdlueck]

You could always create a function...

Good reminder.

And the Me.fieldname is a class, not a form.

MePointer contains a pointer to the form which the field data is being extracted from.

This same validation class works for both the Add / Edit forms for a particular dataset, thus the need to use a pointer to the form.

So I will look into making a worker function which is global and tucked into a module. Thank you!

 

[mdlueck]

Works!!! And the solution per CBrighton's suggestion:

Program sample use:

  'Receive fields from the UI
  Me.userid = uiutils_GetValueFromControl(MePointer.flduserid)
  Me.username = uiutils_GetValueFromControl(MePointer.fldusername)
  Me.active = uiutils_GetValueFromControl(MePointer.fldactive)

The shared API in a module:

'Generic API to safely suck the value out of GUI controls
Function uiutils_GetValueFromControl(ByRef varControlValue As Variant) As Variant
  On Error GoTo Err_uiutils_GetValueFromControl

  'Needed to not receive an error trying to retrieve NULL
  If Len(varControlValue.Value & vbNullString) = 0 Then
    uiutils_GetValueFromControl = ""
  Else
    'Coast is clear, suck away! :-)
    uiutils_GetValueFromControl = varControlValue.Value
  End If

Exit_uiutils_GetValueFromControl:
  Exit Function

Err_uiutils_GetValueFromControl:
  Call errorhandler_MsgBox("Module: modshared_uiutils, Function: uiutils_GetValueFromControl()")
  uiutils_GetValueFromControl = ""
  Resume Exit_uiutils_GetValueFromControl

End Function

 

[mdlueck]

There exists an odd relationship between form field controls and numeric datatypes. Thus it was necessary to update my checker code to handle String vs Numeric variables. Updated code as follows:

'Generic API to safely suck the value out of GUI controls
Function uiutils_GetValueFromControl(ByRef varControlValue As Variant, ByVal strDataType As String) As Variant
  On Error GoTo Err_uiutils_GetValueFromControl

  'Needed to not receive an error trying to retrieve NULL
  If Len(varControlValue.Value & vbNullString) = 0 Then
    If strDataType = "N" Then
      uiutils_GetValueFromControl = 0
    ElseIf strDataType = "S" Then
      uiutils_GetValueFromControl = ""
    Else
      Call errorhandler_MsgBox("Module: modshared_uiutils, Function: uiutils_GetValueFromControl(), Failed: Unsupported strDataType arg received!")
      uiutils_GetValueFromControl = ""
      Exit Function
    End If
  Else
    'Coast is clear, suck away! :-)
    uiutils_GetValueFromControl = varControlValue.Value
  End If

Exit_uiutils_GetValueFromControl:
  Exit Function

Err_uiutils_GetValueFromControl:
  Call errorhandler_MsgBox("Module: modshared_uiutils, Function: uiutils_GetValueFromControl()")
  uiutils_GetValueFromControl = ""
  Resume Exit_uiutils_GetValueFromControl

End Function

Example usage:

  'Receive fields from the UI
  Me.userid = uiutils_GetValueFromControl(MePointer.flduserid, "S")
  Me.username = uiutils_GetValueFromControl(MePointer.fldusername, "S")
  Me.active = uiutils_GetValueFromControl(MePointer.fldactive, "N")

 

[mdlueck]

Well, I have now depricated my shared API above, as I found Nz() is able to handle both numeric and string datatypes.

  'Receive fields from the UI
  Me.userid = Nz(MePointer.flduserid, "")
  Me.username = Nz(MePointer.fldusername, "")
  Me.active = Nz(MePointer.fldactive, 0)

 

[vbaInet]

To be honest if you don't want your fields to hold zero-length strings at all, you're better off enforcing this at table level. There's a field property called Allow Zero Length which when set to No prevents the zero-length string (i.e. ""). Now all you need to test for is Null.

 

[JANR]

To be honest if you don't want your fields to hold zero-length strings at all, you're better off enforcing this at table level. There's a field property called Allow Zero Length which when set to No prevents the zero-length string (i.e. ""). Now all you need to test for is Null.

There is even a code to to this automatically found here :http://allenbrowne.com/bug-09.html

JR

 

[mdlueck]

Yes, proper constraints are in place at table level.

My trouble was people could blank out a text field in the edit form, try to commit the change, and that would crash my program when the validation code tried to extract the null value from the field.

Recall, if users tried to commit a blank Add record form, the validation code would properly flag the required field(s).

If users tried to blank out an existing record on the Edit screen and commit that, then the validation code would blow up when trying to touch the null field.

 

[vbaInet]

There must be a validation somewhere causing it to not accept Null values. What code line does it point to when you Debug? Can we possibly see a stripped down version of your db?

 

[mdlueck]

In the Validation class where I am sucking the values out of the form controls into class attributes.

I try touching the control to get the value, and the code blows up.

Thus, right there where I have pasted in the code as I have been modifying it.

Oh, forgot to answer about seeing... The DB is dependent on a SQL Server back end. It would be very rough to slim it down into a stand-alone prototype.

 

[vbaInet]

Alright, but what exact line is it bombing?

If it's SQL Server then it's an SQL Server problem. Perhaps the field that is bombing is not set to allow Nulls. I don't use SQL Server much.

 

[mdlueck]

No it is not SQL Server related. It is my Validate VBA class attempting to extract the data out of the Form fields and store the data in attributes of the Validate class. No where near SQL Server when the crash happens.

Error log entry as follows:

Date: 20110831 Time: 16:17:36 UserID: c_mlueck
AppErrorMsg: Class: clsObjUIValidationAuthTbl, Function: Validate()
Error Source: Fandango_FE
Error Number: 94
Error Description: Invalid use of Null
MessageText: Error not found.

Thus I was bewildered at how many values of NULL does MS insist on supporting!?!