By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,851 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

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

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Dim rst As New ADODB.Recordset
  3.  
  4.     With rst
  5.         With .fields
  6.             .Append "FullName", adLongVarChar, 260
  7.             .Append "Size", adInteger
  8.             .Append "IsFolder", adBoolean
  9.         End With
  10.         .Open
  11.         .fields("FullName").Properties("Optimize") = True
  12.         .fields("Size").Properties("Optimize") = True
  13.     End With
  14.  
  15.     With rst
  16.         .AddNew
  17.         !FullName = "TestPath"
  18.         !Size = 123
  19.         !IsFolder = True
  20.         .Update
  21.     End With
  22.  
  23.    Set Me.Recordset = rst
  24.  
  25. 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
Jan 4 '08 #1
Share this Question
Share on Google+
8 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Dim rst As New ADODB.Recordset
  3.  
  4.     With rst
  5.         With .fields
  6.             .Append "FullName", adLongVarChar, 260
  7.             .Append "Size", adInteger
  8.             .Append "IsFolder", adBoolean
  9.         End With
  10.         .Open
  11.         .fields("FullName").Properties("Optimize") = True
  12.         .fields("Size").Properties("Optimize") = True
  13.     End With
  14.  
  15.     With rst
  16.         .AddNew
  17.         !FullName = "TestPath"
  18.         !Size = 123
  19.         !IsFolder = True
  20.         .Update
  21.     End With
  22.  
  23.    Set Me.Recordset = rst
  24.  
  25. 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.
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull("Size") And IsNumeric("Size") Then
  2.         .Append "Size", adInteger
  3. End If
Jan 5 '08 #2

P: 5
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.
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull("Size") And IsNumeric("Size") Then
  2.         .Append "Size", adInteger
  3. 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.
Jan 5 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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
Jan 5 '08 #4

P: 5
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.
Jan 5 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. With .fields
  2.             .Append "FullName", adLongVarChar, 260
  3.             .Append "Size", adInteger
  4.             .Append "IsFolder", adBoolean
  5.             .Update   '<<<<<<<<<<<<<<<<<<<<<<<<<<
  6. End With
Jan 5 '08 #6

P: 5
Then I got Run-time error '445'

Object doesn't support this action
Jan 5 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 5 '08 #8

P: 5
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.
Jan 5 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.