CIM Lab 6: Downloading Bill of Material from Solidworks into ParentChildRelations table in : Northwind.mdb We will be adding some features to the form created in Lab 5. When this form is fully working, the user will press a button on the form which will open a “File Open” dialog box. The user will then select a SolidWorks assembly. Then the software will automatically read the name of the assembly and the children. It will create part names for the assembly and the parts. Then it will create the parent child relations between the assembly and the parts. Lastly, it will show the assembly in the ParentChildRelations form. The steps are as follows: Opening up the Database that was created in Lab 5 Open the form: ParentChildRelections in Design View Add a Microsoft Common Dialog Box Control to the form: Under the Design tab select Active X Controls and select Microsoft Common Dialog Box Control Right click on the newly created common dialog control, select properties and change the name of the control to : ComDia Add a command button to the form, select Report Operation – Open Report Click next: For the button’s caption, type in Read SolidWorks Assembly, then click Next. Type anything in the command line, then click Finish Change the name on the button to Read Solidworks Assembly: Change the name of the Command Button to ReadAssembly: Right click on the newly created command button. Select Event| OnClick : Remove the existing code for that event and replace with the following code on the On Click event for the command button: Dim swApp As SldWorks.SldWorks Dim swModel As SldWorks.ModelDoc2 ' a SolidWorks model document Dim fullAssemLoc As String Dim longstatus As Long, longwarnings As Long Dim docName As String ' the save name chosen by the user for the assembly without the ".sldasm" Dim FinalDirLoc As String ' save-to location, Dim fullDrawLoc As String Dim swConf As SldWorks.Configuration Dim swRootComp As SldWorks.Component2 Set dicComps = CreateObject("Scripting.Dictionary") With ComDia .CancelError = False .InitDir = "c:\" .Filter = "Assembly (*.sldasm)|*.sldasm" .ShowOpen fullAssemLoc = .FileName End With Dim posn1 As Integer Dim posn2 As Integer If Not fullAssemLoc = "" Then 'txtLocation.Text = fullAssemLoc fullDrawLoc = Mid$(fullAssemLoc, 1, Len(fullAssemLoc) - 6) + "slddrw" posn1 = InStrRev(fullAssemLoc, "\") posn2 = InStrRev(fullAssemLoc, ".") FinalDirLoc = Left$(fullAssemLoc, posn1) docName = Mid$(fullAssemLoc, posn1 + 1, (posn2 - posn1) - 1) End If Set swApp = CreateObject("SldWorks.Application") Set swModel = swApp.OpenDoc6(fullAssemLoc, swDocASSEMBLY, 0, "", longstatus, longwarnings) Set swModel = swApp.ActivateDoc2(docName, False, longstatus) Set swModel = swApp.ActiveDoc Set swConf = swModel.GetActiveConfiguration Set swRootComp = swConf.GetRootComponent TraverseComponent swRootComp PrintData swApp.Visible = True Add the following lines to the top of the code: Option Explicit Dim dbs As Database Dim sFileName As String, lPrevAssy(10) As Long, iDepth As Integer Dim objACAD As Object, iDwgCount As Integer, bAssyIDHasFocus As Boolean Dim swApp As SldWorks.SldWorks, swDoc As SldWorks.ModelDoc2 Dim dicComps As Object The top of the code should now look like this: At the bottom of the code, add the following two subroutines: Sub TraverseComponent(swComp As SldWorks.Component2) Dim vChildComp As Variant Dim swChildComp As SldWorks.Component2 Dim swCompConfig As SldWorks.Configuration Dim i As Long vChildComp = swComp.GetChildren For i = 0 To UBound(vChildComp) Set swChildComp = vChildComp(i) If Not swChildComp Is Nothing Then Dim path As String Dim curCount As Integer path = swChildComp.GetPathName If dicComps.Exists(path) Then curCount = dicComps.Item(path) dicComps.Item(path) = curCount + 1 Else curCount = 0 dicComps.Add swChildComp.GetPathName, curCount + 1 End If End If TraverseComponent swChildComp Next i End Sub Sub PrintData() Dim keys As Variant Dim i As Integer keys = dicComps.keys For i = 0 To UBound(keys) Dim compName As String Dim vStrings As Variant vStrings = Split(keys(i), "\") compName = vStrings(UBound(vStrings)) vStrings = Split(compName, ".") compName = vStrings(0) Debug.Print compName & " -> " & dicComps.Item(keys(i)) Next End Sub Open up visual basic and click on View | Immediate window. Add the references: - note that the version of Solidworks will be based on the version installed on the machine. . Add Solidworks Constant type library, Solidworks Type Library, sldFuncFeat Type Library, and Solidworks Extensibility Type library (note that if it does not show up in the list, you can click browse and add it) The final references should look like this Testing the code thus far. Save and close the form. Open up a window showing the visual basic code and show the Immediate Window (View | Immediate Window) Open another window showing the form in Form View.Click on the Read SolidWorks Assembly button and open a pre-existing assembly. Switch over to the visual basic code and the names of the parts in the assembly should appear in the immediate window. The names of the children should be showing up in the immediate window as follows. Now we are going to add functionality to the visual basic to create records in the Products table for the assembly and each component in the assembly. Then we will create parent child relations between the assembly and the parts in the ParentChildRelations table. Lastly, we will show the assembly in the ParentChildRelations form. For this part, I will give you the lines of Visual Basic code, and you must figure out where to put them in your program. First, since we are going to be accessing the database directly using visual basic, we will need to dimension a variable to represent the database. Make sure that the following line of code exists at the top of the visual basic code – it should already be there from earlier work. This line creates a variable to represent the database: Dim dbs As Database Second, we need to initialize the variable dbs with setting it to point to the current database using the following line of code: Set dbs = CurrentDb This needs to be put somewhere in the subroutine ReadAssembly_Click before we try to write anything to the database. Third, we will create a record in the Products table corresponding to the assembly name with the following line of code: dbs.Execute "insert into Products (ProductName) VALUES ('" & docName & "');" The function dbs.Execute will execute a line of SQL (Structured Query Language), which is the language used to talk directly to databases. In this situation, we are using the insert into command. More information about the insert into command is available at http://www.w3schools.com/sql/sql_insert.asp This line of code needs to be put into the ReadAssembly_Click subroutine sometime after the program has figured out a value for docName Fourth, now that we have created a record with the information about the assembly, we need to get the ID of that newly created record for later use when we add parent child relations. To do that, the following two lines of code need to be added. Dim ID As Long ID = DLookup("[ProductID]", "Products", "[ProductName] LIKE """ & docName & """") Fifth, we will need to pass this ID down to PrintData subrouting. Modify the call to the PrintData subrourtine so it looks like this: PrintData (ID) Sixth, the declaration for the PrintData subroutine needs to be modified to accept the variable we are passing it as follows: Sub PrintData(ParentID As Long) This way the ID for the parent will get passed to the PrintData subroutine. Seventh, we need to create records the Products table for each of the components (children) in the assembly using the following line of code. dbs.Execute "insert into Products (ProductName) VALUES ('" & compName & "');" Make sure this line of code is inside a loop that goes through all the components and make sure it is after the line that assigns a value to compName. Eighth, for each recorded created in the Products table, we need to get the ID of the newly created record so that we can create relations between the parent and the children. The following lines of code accomplish that. Dim ID As Long ID = DLookup("[ProductID]", "Products", "[ProductName] LIKE """ & compName & """") Ninth, the ParentChildRelations table also stores information about the quantity of each component, so we need to capture that information using that following lines of code. Dim Quantity As Single Quantity = dicComps.Item(keys(i)) Tenth, we need to create record in the ParentChildRelations table with the information about the parent, the child, and the quantity using the following lines of code: dbs.Execute "insert into ParentChildRelations (HufParent, HufChild, Quantity) VALUES (" & ParentID & "," & ID & "," & Quantity & ");" Eleventh, after we have looped through all the components, we can close out the connection to the database using the following line: dbs.Close Twelfth, the last thing we do now that all the information is stored in the database, is to show assembly in our form using the following lines of code: ParentComboBox = ID Form.Requery Form.Refresh Make sure you save your form as it will also save all the VB code Now you can test this code by clicking on the Read SolidWorks Assembly button on the form. To show that it is working, please demonstrate it to me. Also print out a screen shot of the form with the assembly information contained, also print out your Visual Basic Code.
Pages to are hidden for
"CIM Lab 4 Adding Fields to a Database Northwind"Please download to view full document