S
Scott Wickham
I'm using Access 2007 forms as a front-end for SharePoint (WSS v3.0) data.
I'm having a problem with Attachment fields from the SharePoint lists when
displayed on the Access forms...but only in a specific scenario. Here are the
conditions that cause the Attachment fields to break:
* The Access form uses a SharePoint linked table as its data source
* The Record Source for the form uses an SQL query (SELECT * FROM tablename)
instead of simply using the table name with no SQL
* The VBA code-behind for the form sets a Filter for the form in the
Form_Open event
* The Access database/form is launched from a SharePoint document library
(not launched from the local copy on a workstation)
With this combination of factors, the Attachment field on my form
breaks...if you double-click in the Attachment field you can view the
attachments, but the "Add..." and "Remove..." buttons are no longer enabled,
preventing the addition or removal of any other attachments.
Here is the exact series of steps that reproduce the problem for me:
1. Create a new SharePoint custom list named “TestListâ€
2. Add a new record to TestList using “TestTitle†as the value for the Title
field
3. Open Access 2007 and create a new database named “TestList.accdbâ€
4. Create a linked table in Access that is linked to the “TestList†list in
SharePoint
5. Create an Access form for the linked “TestList†table that contains at
least the “Attachments†field
6. Add a “Form_Open†code event for the form, containing the following two
lines of code:
Me.Filter = "[Title] = 'TestTitle'"
Me.FilterOn = True
7. Save the form as “TestList Formâ€
8. Create a new SharePoint document library named “TestLibraryâ€
9. Upload the “TestList.accdb†Access database to the“TestLibrary†document
library in SharePoint
10. Open “TestList.accdb†from within SharePoint (do not open the local
workstation copy)
11. If the Access ribbon displays the Security Warning that says “Certain
content in the database has been disabledâ€, click the “Options…†button next
to the warning and enable the content (this allows the “Form_Open†code to
run)
12. Open the “TestList Form†form in Form View
13. Double-click within the Attachments field on the form. Everything should
be working properly, and the “Attachments†dialog should have the “Add…â€
button enabled, ready to accept new attachments.
14. Close the “TestList.accdb†database.
15. Reopen the local workstation copy of the “TestList.accdb†database (do
not open the copy that was uploaded to SharePoint)
16. Next, modify the form’s “Record Source†property. It should currently
have “TestList†as the Record Source value…
17. Click the ellipsis (…) button on the Record Source property to invoke
the Query Builder.
18. In the Query Builder, change the view to SQL View. The current SQL
should read:
SELECT FROM TestList;
…change this by adding an asterisk “*†between SELECT and FROM as follows:
SELECT * FROM TestList;
19. Close the Query Builder and accept the changes.
20. Save the form again
21. Upload the “TestList.accdb†Access database to the“TestLibrary†document
library in SharePoint again, overwriting the old version
22. Open “TestList.accdb†from within SharePoint (do not open the local
workstation copy)
23. If the Access ribbon displays the Security Warning that says “Certain
content in the database has been disabledâ€, click the “Options…†button next
to the warning and enable the content (this allows the “Form_Open†code to
run)
24. Open the “TestList Form†form in Form View
25. Double-click within the Attachments field on the form. This is where the
attachment functionality was breaking for me—the “Add…†button was now
disabled and there was no way to add new attachments through the form.
26. Switching the Record Source back to the original value (using just the
table name “TestList†as the Record Source instead of the SQL “SELECT * FROM
TestList;â€) corrects the attachment problem once again.
It took me a very long time to isolate the problem to this specific set of
circumstances, and now I'm hoping that someone can help figure out the cause
of the problem. For now we are working around the problem by using only the
table name in the form's Record Source instead of a SQL query, but would like
to know the complete solution if there is one.
Thanks,
Scott
I'm having a problem with Attachment fields from the SharePoint lists when
displayed on the Access forms...but only in a specific scenario. Here are the
conditions that cause the Attachment fields to break:
* The Access form uses a SharePoint linked table as its data source
* The Record Source for the form uses an SQL query (SELECT * FROM tablename)
instead of simply using the table name with no SQL
* The VBA code-behind for the form sets a Filter for the form in the
Form_Open event
* The Access database/form is launched from a SharePoint document library
(not launched from the local copy on a workstation)
With this combination of factors, the Attachment field on my form
breaks...if you double-click in the Attachment field you can view the
attachments, but the "Add..." and "Remove..." buttons are no longer enabled,
preventing the addition or removal of any other attachments.
Here is the exact series of steps that reproduce the problem for me:
1. Create a new SharePoint custom list named “TestListâ€
2. Add a new record to TestList using “TestTitle†as the value for the Title
field
3. Open Access 2007 and create a new database named “TestList.accdbâ€
4. Create a linked table in Access that is linked to the “TestList†list in
SharePoint
5. Create an Access form for the linked “TestList†table that contains at
least the “Attachments†field
6. Add a “Form_Open†code event for the form, containing the following two
lines of code:
Me.Filter = "[Title] = 'TestTitle'"
Me.FilterOn = True
7. Save the form as “TestList Formâ€
8. Create a new SharePoint document library named “TestLibraryâ€
9. Upload the “TestList.accdb†Access database to the“TestLibrary†document
library in SharePoint
10. Open “TestList.accdb†from within SharePoint (do not open the local
workstation copy)
11. If the Access ribbon displays the Security Warning that says “Certain
content in the database has been disabledâ€, click the “Options…†button next
to the warning and enable the content (this allows the “Form_Open†code to
run)
12. Open the “TestList Form†form in Form View
13. Double-click within the Attachments field on the form. Everything should
be working properly, and the “Attachments†dialog should have the “Add…â€
button enabled, ready to accept new attachments.
14. Close the “TestList.accdb†database.
15. Reopen the local workstation copy of the “TestList.accdb†database (do
not open the copy that was uploaded to SharePoint)
16. Next, modify the form’s “Record Source†property. It should currently
have “TestList†as the Record Source value…
17. Click the ellipsis (…) button on the Record Source property to invoke
the Query Builder.
18. In the Query Builder, change the view to SQL View. The current SQL
should read:
SELECT FROM TestList;
…change this by adding an asterisk “*†between SELECT and FROM as follows:
SELECT * FROM TestList;
19. Close the Query Builder and accept the changes.
20. Save the form again
21. Upload the “TestList.accdb†Access database to the“TestLibrary†document
library in SharePoint again, overwriting the old version
22. Open “TestList.accdb†from within SharePoint (do not open the local
workstation copy)
23. If the Access ribbon displays the Security Warning that says “Certain
content in the database has been disabledâ€, click the “Options…†button next
to the warning and enable the content (this allows the “Form_Open†code to
run)
24. Open the “TestList Form†form in Form View
25. Double-click within the Attachments field on the form. This is where the
attachment functionality was breaking for me—the “Add…†button was now
disabled and there was no way to add new attachments through the form.
26. Switching the Record Source back to the original value (using just the
table name “TestList†as the Record Source instead of the SQL “SELECT * FROM
TestList;â€) corrects the attachment problem once again.
It took me a very long time to isolate the problem to this specific set of
circumstances, and now I'm hoping that someone can help figure out the cause
of the problem. For now we are working around the problem by using only the
table name in the form's Record Source instead of a SQL query, but would like
to know the complete solution if there is one.
Thanks,
Scott