"allow null"? property for recordset field 1

[jordanking]

hello,

I was wondering if there is a way to determine if a recordset's field allows nulls based on the desing of the underlying table(s).

I am using unbound forms to control the user data. All of the columns in the table have equivelent controls on the form.
I want to have a modular piece of code that checks all the fields on any form (each control's name matches the recordset's field name) to see if the fields that can not be null have values.

Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
Dim ctl As Control
Dim vartemp As Variant
Dim sqlSelect As String
  
    'Open connection
    cnn.Open frm.Controls("xProvider") & frm.Controls("xDataSource")
   
    'Open recordset
    sqlSelect = "Select * From " & frm.Controls("xRecordset")
    rst.Open sqlSelect, cnn, adOpenStatic
    
    'Iterate through controls on form that match fields in recordset
    For Each ctl In frm
        'if error the field is not on the form
        vartemp = rst.Fields(ctl.Name).Name
        On Error Resume Next
        Err = 0
            If ctl.Enabled Then
                if rst.Fields(ctl.Name)."allownulls" = false and rst.Fields(ctl.Name).value = null then
                      'alert user
                end if
            End If
        End If
    Next

i have removed sections of this code to make it more condensed

thanks in advance

 

[CautionMP]

jordanking,
I believe that the Nullable property is stored in the Attributes flag for the field regardless of what the source DBMS is: You should be able to do something like this:

...
             if rst.Fields(ctl.Name).[b]Attributes And adFldIsNullable[/b] then
...

Some of the other flags may be of interest so you might take a quick look at the other values in [tt]ADODB.FieldAttributeEnum[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[jordanking]

Thanks a lot CautionMP

That worked perfectly, and as you said, I did find another attribute that was useful. (ie: identifiying the field that is used as the uniqe identifier for the table.)

the following link is a list of attributes i found on the msdn website, for anyone else interested.

http://msdn2.microsoft.com/en-us/library/ms676553.aspx

JK