Recipes based on Access data

The example covers the following topics:

• Creating a VBA program attached to a button.

• Accessing and using the specific functionality of applications using OLE Automation in this example Access will be used.

• Creating advanced forms with text-boxes, list boxes and buttons and changing values based on user interaction.

• Retrieving data from Access and displaying it in a form list box.

• Changing and adding records in Access.

• Creating and using user defined functions The example:

This example is very much like the previous example, only in this case data is retrieved from an Access Database instead of an Excel spreadsheet. Besides that it is also possible to add and change profiles (=records) whereas in the previous example this was not possible.

In this example different recipes are retrieved from Access using OLE automation and presented in a user-defined form. The operator can either choose a recipe and download the values in the recipe to the ProcessView variables or change/add a profile in the database. Use the following steps to create a push button and the code behind it:

1. Add a button to the screen using the toolbar or by choosing Dynamics
Intrinsics
Push- button from the menu.

3. Press the Create Button to Open the VBA Script Wizard dialog 4. Enter "AccessRecipe" as the Script Name

5. Check the box "Insert a from" and press the OK button 6. Press the Edit Button to open the VBA Editor

7. Go to Tools
References in the menu and mark the following libraries: "Microsoft Access 8.0 Object Library" and “Microsoft DAO 3.5 Object Library” to have easy access to the Access functionality.

8. In the project window double click on "GwxAccessRecipe_MainForm" this will open the user form and the form toolbox. If the toolbox is not visible choose View
Toolbox from the menu.

Figure 2.6. VBA Toolbox

9. The following form will have to be created:

Figure 2.7. Profiles Form

10. The form itself has the following properties: Form: (Name): GwxAccessRecipe_MainForm Caption: Profiles

11. The large box on the right is a list box and has the following property: ListBox: (Name): Profiles

12. The text labels next to the ListBox have the following properties from top to bottom: Label: (Name): Label1 Caption: Name

Label: (Name): Label2 Caption: Furnace 1 Label: (Name): Label3 Caption: Furnace 2 Label: (Name): Label4 Caption: Furnace 3 Label: (Name): Label5 Caption: Furnace 4 Label: (Name): Label6 Caption: Furnace 5 Label: (Name): Label7 Caption: Cool Down

13. The text entry fields in the middle have the following properties from top to bottom TextBox: (Name): Furnace1

TextBox: (Name): Furnace2 TextBox: (Name): Furnace3 TextBox: (Name): Furnace4

TextBox: (Name): Furnace5

Combobox (Name): Cooling MatchEntry: 1

14. The text labels on the right have the following properties from top to bottom: Label: (Name): Label8 Caption: °C

Label: (Name): Label9 Caption: °C Label: (Name): Label10 Caption: °C Label: (Name): Label11 Caption: °C Label: (Name): Label12 Caption: °C

15. The button on the right have the following properties from top to bottom: Button: (Name): NewProfile Caption: Add Profile

Button: (Name): ChangeProfile Caption: Change Profile Button: (Name): DelProfile Caption: Delete Profile Button: (Name): UseProfile Caption: Use Profile Button: (Name): Cancel Caption: Cancel 16. Double click on the form itself and add the following code:

Dim AccessObj As Access.Application Dim Profdb As Database

Dim Profrec As Recordset Private Sub UserForm_Initialize()

Set AccessObj = GetObject(, "Access.Application.8")

AccessObj.OpenCurrentDatabase ("d:\wwsc\documentation\wwsc.mdb") Set Profdb = AccessObj.CurrentDb

Set Profrec = Profdb.OpenRecordset("Profiles") Cooling.AddItem ("TRUE")

Cooling.AddItem ("FALSE") Call UpDate

End Sub

Private Sub UserForm_Terminate() Set Profdb = Nothing

Set Profrec = Nothing

AccessObj.CloseCurrentDatabase Set AccessObj = Nothing

End Sub Sub UpDate() Dim i As Integer Profiles.Clear With Profrec .MoveFirst For i = 0 To .RecordCount – 1 Profiles.AddItem (.Fields(1).Value) .MoveNext Next i End With End Sub

The first three objects are defined to be able to connect to Access and the objects in Access. The procedure UserForm_Initialize creates a connection to Access using the GetObject function. When a link to Access is created a particular database can be opened. After that a link is setup to the database object. When the link to the database exists a link to a particular table (Profiles) is created with the OpenRecordSet method. Once the three links are created two items (TRUE and FALSE) are added to the Cooling combobox.

When the procedure Update is started, this procedure will first clear the listbox on the screen, after that it will read the records one by one in the table and add those to the profiles listbox. The

procedure UserForm_Terminate runs when the form is closed, it closes the database and resets the objects that are linked to Access.

17. In the project window double click on "GwxAccessRecipe_MainForm" this will open the user form again.

18. Double click on the listbox in the form this will open the code window again, enter the following code here:

Private Sub Profiles_Click() Dim i As Integer

Dim found As Boolean Profrec.MoveFirst found = False

For i = 0 To Profrec.RecordCount – 1

If Profrec.Fields(1).Value = Profiles.Value Then found = True Exit For End If Profrec.MoveNext Next I If found Then ProfileName = Profrec.Fields(1).Value Furnace1 = Profrec.Fields(2).Value Furnace2 = Profrec.Fields(3).Value Furnace3 = Profrec.Fields(4).Value Furnace4 = Profrec.Fields(5).Value Furnace5 = Profrec.Fields(6).Value Cooling = Profrec.Fields(7).Value Else ProfileName = "" Furnace1 = "" Furnace2 = "" Furnace3 = "" Furnace4 = "" Furnace5 = "" Cooling = "" End If End Sub

This procedure tries to find the profile in the table that was clicked on in the profiles listbox. If this record is found in the table, it displays the settings of this particular record in the form, if the record is not found it empties the fields in the form.

19. In the project window double click on "GwxAccessRecipe_MainForm" this will open the user form again.

20. Double click on the “Add Profile”-button in the form this will open the code window again, enter the following code here:

Private Sub NewProfile_Click() With Profrec .AddNew !Profile = ProfileName !Furnace1 = Furnace1 !Furnace2 = Furnace2 !Furnace3 = Furnace3 !Furnace4 = Furnace4 !Furnace5 = Furnace5 !Cooling = Cooling .UpDate

End With Call UpDate End Sub

This procedure uses the With statement to perform certain methods on the Profrec object. The AddNew method adds a new record in the table. After that the different fields in the table are filled in. To access a field in a table use the following form: tablename!fieldname. Once all the fields are filled in the Update method is called to write the changes to the database. At the end the Update function is called to update the listbox in the form.

21. In the project window double click on "GwxAccessRecipe_MainForm" this will open the user form again.

22. Double click on the “Change Profile”-button in the form this will open the code window again, enter the following code here:

Private Sub ChangeProfile_Click() With Profrec .Edit !Profile = ProfileName !Furnace1 = Furnace1 !Furnace2 = Furnace2 !Furnace3 = Furnace3 !Furnace4 = Furnace4 !Furnace5 = Furnace5 !Cooling = Cooling .UpDate End With Call UpDate End Sub

This procedure uses the With statement to perform certain methods on the Profrec object. The Edit method makes it possible to modify a record in the table. When the record is set into edit mode the different fields in the table are updated. Once all the fields are filled in the Update method is called to write the changes to the database. At the end the Update function is called to update the listbox in the form.

23. In the project window double click on "GwxAccessRecipe_MainForm" this will open the user form again.

24. Double click on the “Delete Profile”-button in the form this will open the code window again, enter the following code here:

Private Sub DelProfile_Click() Profrec.Delete

Call UpDate End Sub

This procedure deletes the current record and calls the Update function to update the listbox in the form.

25. In the project window double click on "GwxAccessRecipe_MainForm" this will open the user form again.

26. Double click on the “Use Profile”-button in the form this will open the code window again, enter the following code here:

Sub SetValue(Name As String, Value As Variant) Dim MyPoint As GwxPoint

Set MyPoint = ThisDisplay.GetPointObjectFromName(Name) MyPoint.Value = Value

Set MyPoint = Nothing End Sub

Call SetValue("~~set_furnace1~~", Val(Furnace1.Text)) Call SetValue("~~set_furnace2~~", Val(Furnace2.Text)) Call SetValue("~~set_furnace3~~", Val(Furnace3.Text)) Call SetValue("~~set_furnace4~~", Val(Furnace4.Text)) Call SetValue("~~set_furnace5~~", Val(Furnace5.Text)) Call SetValue("~~setenablecooling~~", Val(Cooling.Text)) Unload Me

End Sub

The procedure SetValue takes two parameters, the first one is the name of a point name in GraphWorX32 and the second one is the value that should be written to the point. The procedure tries to find the specified name and writes the specified value to it. The procedure UseProfile calls the SetValue procedure for each of the different points that should be written. At the end it unloads the form (=Me).

27. In the project window double click on "GwxAccessRecipe_MainForm" this will open the user form again.

28. Double click on the “Cancel”-button in the form this will open the code window again, enter the following code here:

Private Sub Cancel_Click() Unload Me

End Sub

This procedure Unloads the current form (=Me).

29. In the VBA Editor use File
Close and Return to Gwx32 from the menu to return to GraphWorX32.

30. Use the property page to change the text on the button to "Recipe" How the VBA program works:

When the button is pressed the form is loaded. The initializing procedure takes place automatically when loading the form. During the initialization the links to Access are initiated, a specific database is loaded and data from a table is added to the list box using the AddItem method of the list box, and the cursor is set to the first item in the list box.

When the operator clicks on one of the profiles in the list box the text boxes on the right side of the form are automatically updated with the data belonging to the selected profile. This data is retrieved from the Access table.

Pressing the Use Profile button will download the selected profile to the GraphWorX32 variables. The Add, Change and Delete Profile buttons make it respectively possible to add new profiles, change the current profile and delete the current profile.

The cancel button closes the form and returns the operator to GraphWorX32.

When the form is exited, the database in Access is closed and all links to Access are terminated by reassigning them to Nothing.

A table is present in Access that has the following settings:

Field Name DataType Description

Index AutoNumber

Profile Text Profile Name

Furnace1 Long Furnace Temperature Furnace2 Long Furnace Temperature Furnace3 Long Furnace Temperature Furnace4 Long Furnace Temperature Furnace5 Long Furnace Temperature Cooling Yes/No Enable cooling section

Figure 2.8. Profiles Access Worksheet

The result of using the Recipe button is shown below:

Figure 2.9. ProcessView running the Profiles form