Tuesday, April 8, 2014

Data cannot be inserted because there is no matching record - Sharepoint / Access List Error

Occasionally, my SharePoint 2010 list, which is attached in a MS Access 2010 database will refuse to allow me to run an update query on the list.

The Access error I get when running the update query is “Data cannot be inserted because there is no matching record".

An internet search lead me to some clues, but none of the responses directly applied to my situation.

I did find an answer from these clues and added my solution here: http://stackoverflow.com/questions/3791372/data-update-error-with-sharepoint-2010-and-ms-access-2010

In my case, my attached list is called “ProjectSearch” and running an update query on the list produced this error.




I observed that my database had an additional list that was automatically attached for me when I attached to my “ProjectSearch” list. This is not a list that I see in SharePoint – but it is an important relationship in Ms Access to the list.  I found that reading more about SharePoint to Access lists helped http://office.microsoft.com/en-us/access-help/import-from-or-link-to-a-sharepoint-list-HA001230313.aspx

To resolve my list error, I simply needed to refresh this secondary additional list. Then I was able to run my update query without an issue.




Wednesday, February 12, 2014

Outlook 2010 - find a folder


OK… this isn’t a SharePoint topic – but I was frustrated by this issue and it became a catalyst to find an answer and then share that finding.   Hope it helps someone….

In our efforts to organize our emails, we can get carried away with nested folders in our Inbox Cabinet.   Maybe you're one of those people who take great pride in keeping your Inbox clean by filing away important emails.  I commend you.

But sometimes you need to file a new email into a nested folder and you just can't remember the parent folder.   There isn't an obvious way to search for a folder name - but there is a way you can discover the path to your nested folder.

The trick lies in searching for an email term that likely resides in the folder you want to locate.

To illustrate, let's assume you created a nested folder called "donor sites".   You don't recall what parent folder - or complex hierarchy of folders - in which it may reside.

Start in your inbox, and search for a likely term contained in emails within your donor sites folder - perhaps "charity".
 

 
From the initial results, click "try searching again in All Mail Items".  Now look for the desired folder name in the "In Folder" column.  (If you are not displaying the In Folder column - see how to add,remove, or move a column).

 

Open (double click) that email in the desired folder and press Ctrl+Shift+F to open Advanced Search.   Next click the Browse button to see a list of folders.   Ta-da!  You have just discovered the path to your nested folder.
 


 
It would be nice if Outlook could search for folder names - but until that day arrives, this is one method I've found to achieve that goal.

 

Sunday, November 18, 2012

Trouble-shooting file not found error after export / import of a webpart

I learned something new about exporting and importing a webpart in Sharepoint 2010. Here's the scenario: I exported a form webpart from Sharepoint Designer. Once I imported it to my page, the page would throw this error of "file not found".
When I looked at the logs, it basically said, "there is no Web named http://...."
That lead me to open the exported webpart with NotePad ++ and there I found 5 references to a site path which included the domain name.

I whacked the domain name, leaving only the relative path, saved and imported the webpart again.

That resolved the error.

Saturday, September 22, 2012

Finding the key to understanding Sharepoint

I liked this video as it attempts to enlighten us on the key to understanding SharePoint. I must say, when I attend conferences, there is actually starting to be a line in the Women's bathroom. So I don't know for how long Mark Rackley can get away with a talk like this, but there truth to his presentation with regards to understanding SharePoint.

Thursday, September 20, 2012

Demo of InfoPath 2010

Attending a Bootcamp immersion class and learning some very good things.  Today's topic was InfoPath and I was impressed with the power of this tool.  It seemed familiar to me as it reminded me of both designing with Dreamweaver and Ms Access forms.

I created a couple of videos using Jing to highlight what we learned today.

Part One details Replacing the Sharepoint New Form with an InfoPath Form  (5 minutes)

Part Two details Using the Infopath Webpart on a Sharepoint 2 column page (5 minutes)

While I could only scratch the surface in this class, I want to learn more about InfoPath.  Did you know you could do the following with InfoPath?
  • apply themes to the form
  • create controls such as buttons, combo boxes, date pickers, and dynamic text in calculated values
  • add in rules to offer a complex form by hiding fields based on form choices, making some field optional, etc
  • display the form in a webpart on your webpage
All pretty simple and quick.

Friday, February 17, 2012

Using Access Services with a Sharepoint Website

I did some experimenting with Access Services and Sharepoint (both Office 2010 versions).  What I discovered was just how easy it was to publish an existing Microsoft database, complete with a form and macro events.

I started a blank database and created a very basic table called Products and based a form upon that table.  I set the Display Form and Web Display Form to that new form, ProductsForm



Happy with my form, I followed these steps:
  1. on the file tab, click Publish to Assess Services
  2.  I entered a URL to my site as the server URL and gave entered the Site Name as OrdersManagement.
  3. When I clicked Publish, the new site was created and the database was published.
  4. I went to the url /Mysite/OrdersManagement and there was my form. 
  5. I entered a record and clicked Save Record.  A message was displayed
  6. Returning to my database, I see the record is now in my table
That’s pretty cool.  But our database forms offer a lot of functionality, so I wondered if adding events to my form would translate up to the SharePoint site.  My next experiment involved adding a button on the form and attaching a message box declaring “demo successful!” to the OnClick event.


Before the Sharepoint site can reflect my changes, I need to sync the service.  Do that by clicking the Sync button under the File menu of Access



Returning to mySite/OrdersManagement, I enter a record and click the button.  There’s my message proving this event translates up to the Website. 


 
Encouraged, I go on to add a delete button and attach the delete record command to the onClick event.   Sure enough, this works and as a bonus, Microsoft adds the logic to confirm if a deletion is really desired. 




Wednesday, November 23, 2011

DVWP and Displaying Records Within a Specific Time Frame

Today I worked on a page which has a high bounce rate, frankly because it is not useful. It displayed more than 1000 records by paging through 25 records at a time.

The request I had was to display only 10 months worth of records - which would display a much more useful set of 47 records.

The page had a DVWP whose source was a SQL table. My adventure included tweaking the SQL select statement and initially adding an ORDER BY clause, but eventually opting not to use it and allow the DVWP to be sortable by the columns.

The biggest challenge I encountered was getting the syntax correct in the query while using the built-in filtering wizard of the dataview webpart.

I frequently ran into a roadblock with the generic SPD error message, "the server returned a non-specific error ..." whenever I tried to filter or sort. After much gnashing of teeth and clicking the undo button, I stopped using the wizard and went straight to the select statement.
.
Don't ask me why, but typing the ORDER BY clause into the statement works.

To get to the select statement, open your file in SharePoint Designer (SPD) and find the <Datasources> tag in the XSL code.


This is an edited version of the original SQL statement displaying all records - but it has all relevant parts.
<DataSources>
<SharePointWebControls:SPSqlDataSource runat="server" ... SelectCommand="SELECT TableName . OrderNumber , TableName . PublicationDate , TableName . ProjectNumber , TableName . ReportTitle , ... FROM TableName INNER JOIN TableName2 ON TableName . ProjectNumber = TableName2 . ProjectNumber " ID="Custom_x0020_Query2"></SharePointWebControls:SPSqlDataSource>
</DataSources>

To begin, I clicked the chevron to open the filter wizard.


The Filter Wizard is not going to give me the ability to say "display the records where the publication date is equal or less than 10 months from today".

But I picked a date from the Wizard so that I could look at the sql statement to get a clue about the syntax. That results in the following WHERE clause "WHERE [PublicationDate] &gt;= '1/23/2011' " and reminds me to escape the greater than symbol for CAML.


That gets me my 47 records - but the date is hard-coded. Not what I want.

After some failed attempts to get the SQL syntax right, I reached for an expert at Experts Exchange.

That quickly yielded the correct solution: WHERE [PublicationDate] &gt; DATEADD(MONTH,-10,getdate())" Which prompted me to read more about those SQL functions here:
http://www.electrictoolbox.com/date-add-sql-server-intervals-dates/

Now all that's left to do is see if the bounce rate improves on this page.