Values shown only in string fields of forms with fabricated ADO recordset

[janeks]

I faced with problem in the subject.

I need to dinamicaly create the recordset and show the results in a small subform.
To construct and test the form I made following test code for form load event:

Private Sub Form_Load()
    Dim rst As New ADODB.Recordset
 
    With rst
        With .fields
            .Append "FullName", adLongVarChar, 260
            .Append "Size", adInteger
            .Append "IsFolder", adBoolean
        End With
        .Open
        .fields("FullName").Properties("Optimize") = True
        .fields("Size").Properties("Optimize") = True
    End With
 
    With rst
        .AddNew
        !FullName = "TestPath"
        !Size = 123
        !IsFolder = True
        .Update
    End With
 
   Set Me.Recordset = rst
 
End Sub

And then I designed the text line controls with the field names above in Control Sources. Then when showing form I can see values only in text fields, while in other fields/textline controls there are #Error.

It is Access2003
What could be wrong?

Thanks in adwance,
Janeks

[puppydogbuddy]

I faced with problem in the subject.

I need to dinamicaly create the recordset and show the results in a small subform.
To construct and test the form I made following test code for form load event:

Private Sub Form_Load()
    Dim rst As New ADODB.Recordset
 
    With rst
        With .fields
            .Append "FullName", adLongVarChar, 260
            .Append "Size", adInteger
            .Append "IsFolder", adBoolean
        End With
        .Open
        .fields("FullName").Properties("Optimize") = True
        .fields("Size").Properties("Optimize") = True
    End With
 
    With rst
        .AddNew
        !FullName = "TestPath"
        !Size = 123
        !IsFolder = True
        .Update
    End With
 
   Set Me.Recordset = rst
 
End Sub

And then I designed the text line controls with the field names above in Control Sources. Then when showing form I can see values only in text fields, while in other fields/textline controls there are #Error.

It is Access2003
What could be wrong?

Thanks in adwance,
Janeks

I am not sure, but it looks like you are encountering nulls or non-numeric values when you are adding/appending values for the size field. Try testing for nulls or non-numeric values before doing the add/append.

If Not IsNull("Size") And IsNumeric("Size") Then
        .Append "Size", adInteger
End If

[janeks]

I am not sure, but it looks like you are encountering nulls or non-numeric values when you are adding/appending values for the size field. Try testing for nulls or non-numeric values before doing the add/append.

If Not IsNull("Size") And IsNumeric("Size") Then
        .Append "Size", adInteger
End If

It should be not a problem.
When I am testing what I appended by using Debug.Print or MsgBox, then all works fine.
Interesting behaviour I observer when I clicked in check box of boolean field, than the value in size field suddenly appears in place of #Error.

[puppydogbuddy]

It should be not a problem.
When I am testing what I appended by using Debug.Print or MsgBox, then all works fine.
Interesting behaviour I observer when I clicked in check box of boolean field, than the value in size field suddenly appears in place of #Error.

Ok, if it is the yes/no (boolean) fields, there is a known bug that results in an error if the boolean field is null. This link contains the work-around for the problem.

http://allenbrowne.com/bug-14.html

[janeks]

Ok, if it is the yes/no (boolean) fields, there is a known bug that results in an error if the boolean field is null. This link contains the work-around for the problem.

http://allenbrowne.com/bug-14.html

That is not only yes/no (boolean) fields (the same problem persists with f.ex. numeric fields if there is no boolean field) and the case in link was about query, but in this case it in code created recordset.

[puppydogbuddy]

That is not only yes/no (boolean) fields (the same problem persists with f.ex. numeric fields if there is no boolean field) and the case in link was about query, but in this case it in code created recordset.

In your prior response, you said everything was working ok, except for the boolean fields. Based on your new statement above, I looked at your code again and it appears to me that you left out the .Update command on your append code as shown below:

With .fields
            .Append "FullName", adLongVarChar, 260
            .Append "Size", adInteger
            .Append "IsFolder", adBoolean
            .Update   '<<<<<<<<<<<<<<<<<<<<<<<<<<
End With

[janeks]

Then I got Run-time error '445'

Object doesn't support this action

[puppydogbuddy]

Then I got Run-time error '445'

Object doesn't support this action

Your first append line is appending a fields collection object and a value to a recordset object. You need to create the field object before the recordset object is opened, then append the value to the newly created fields object.

See this link:

http://msdn2.microsoft.com/en-us/lib...64(VS.85).aspx


Excerpt from the link:
The FieldValue parameter is only valid when adding a Field object to a Record object, not to a Recordset object. With a Record object, you can append fields and provide values at the same time. With a Recordset object, you must create fields while the Recordset is closed, and then open the Recordset and assign values to the fields.

[janeks]

Isn't so in my 1-st code example?

Up to line 10 the fields are appended,
Then in line 10 the recordset is opened and only then the field values are added from line 16.