related tables
I created a Word project and added a new data source to AdventureWorks database using the wizard. I selected three tables Product, ProductPhoto and ProductProductPhoto. The last one is many-to-many kind of table. On the Word document surface I have dragged and dropped some columns to see them in bookmark control. In result the designer added the following lines:
'TODO: Delete this line of code to remove the default AutoFill for 'AdventureWorksDataSet.ProductPhoto'.
IfMe.NeedsFill("AdventureWorksDataSet")Then
Me.ProductPhotoTableAdapter.Fill(Me.AdventureWorksDataSet.ProductPhoto)
EndIf
'TODO: Delete this line of code to remove the default AutoFill for 'AdventureWorksDataSet.Product'.
IfMe.NeedsFill("AdventureWorksDataSet")Then
Me.ProductTableAdapter.Fill(Me.AdventureWorksDataSet.Product)
EndIf
It dazzled me that ProductProductPhoto table is not Fill-ed in this scenario. But in order to show a product’s photo we must use this many-to-many table. My fix was to drag and drop a column from ProductProductPhoto to Word surface and then to delete the column. The designer added these lines:
'TODO: Delete this line of code to remove the default AutoFill for 'AdventureWorksDataSet.ProductProductPhoto'.
IfMe.NeedsFill("AdventureWorksDataSet")Then
Me.ProductProductPhotoTableAdapter.Fill(Me.AdventureWorksDataSet.ProductProductPhoto)
EndIf
So far, so good.I want to create a picture box that is bound to ProductPhoto binding source, but the PhotoID must be taken from Product binding source. The user has to able to scroll the entire products and see product’s picture.
Can you help me with this one please?
Martin Kulov
http://www.codeattest.com/blogs/martin
Hi Martin,
Given the way the relationship was designed between the ProductPhoto and Product table, I added code to filter the result set from the ProductPhoto table so that the correct image was displayed as I navigated through the records in the Product table.
For example, in the project that I created I added a couple of command buttons to the task pane so that I could navigate the records in the Product table. In addition to the code needed to navigate between the records, I added code to implement the filtering and have included the code from one of these command buttons below.
Sample Code
==========================
Private Sub btnPrev_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrev.Click
'Move to the previous record in the Product table.
Me.ProductBindingSource.MovePrevious()
'If the ProductPhotoId field in the Product table contains a value then
'filter the data form the ProductPhoto table based on this value.
If Me.ProductBindingSource.Current.row.item(13) <> "" Then
Me.ProductPhotoBindingSource.Filter = "productphotoid = " & Me.ProductBindingSource.Current.row.item(13)
End If
End Sub
==========================
Regards,
Ken Laws
MSFT
This posting is provided "AS IS" with no warranties, and confers no rights.
For more information regarding Visual Studio Tools for Office 2005:
Best of Blogs: Visual Studio 2005 Tools for Office
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_2003_ta/html/odc_landvsto2005_ta.asp
Visual Studio Tools for Office Forum
http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=16
Visual Studio Tools for the Microsoft Office System
http://msdn.microsoft.com/office/understanding/vsto/default.aspx
Hi Ken,
I decided to skip the use of many-to-many table. However it is still interesting to me how we can bind NamedRange view controls to a common binding source given the fact that the controls represent tables which have many-to-many relationship?
Thanks,
Martin Kulov
http://www.codeattest.com/blogs/martin
