Microsoft Access Database Development With VBA

Introduction to the Extensible Markup Language (XML)

 

XML Fundamentals

 

Introduction

The eXtensible Markup Language, XML, is a simple text-based language with which some tags are created in a regular text-based document. The document can be created using any regular text editor such as Notepad in Microsoft Windows.

The document must be saved as a computer file with the .xml extension. Once the document is created and the file has been saved, any application that needs that file can use it. Based on this, there are some characteristics to an XML file:

  • The document can be created using any text editor
  • The content of the document is made of ASCII text that consists of small things (formats) named tags
  • The tags in the document must follow some rules
  • The whole content must be structured a certain way, referred to as well-formed. In other words, an XML document that follows the XML standards is said to be well-formed
  • The document does not indicate what it is used for. The application that uses that file must know what to do with the file

Based on this, XML makes it possible to create a document that various types of applications can use. For example, you could have various database environments that have different ways to create and manage their records. Imagine you have a table of customers or products in one of the databases and you want other databases to use or even update the list of customers or products. To make those databases environments communicate, you can create an XML file that contains information about the customers or products. Then, any of those database environments can access that document and do whatever it wants, such as adding new records or updating existing records. This can be illustrated as follows:

The Extensible Markup Language in Microsoft Access

Practical LearningPractical Learning: Introducing XML

  1. Start Microsoft Access
  2. Click Blank Desktop Database
  3. In the File Name text box, replace the name with College Park Auto-Repair
  4. Click CREATE
  5. Close the default table without saving it
  6. On the Ribbon, click CREATE
  7. In the Form section, click Form Design
  8. Save the form as NewRepairOrder
  9. Change the design of the form as follows:
     
    College Park Auto Repair - New Repair Order
    Control Name Caption Other Properties
    Label Label lblMainTitle College Park Auto Repair Fore Color: #FFFFFF
    Label Label lblMainTitleShadow College Park Auto Repair Fore Color: Text 1
    Option Group Option Group   Order Identification  
    Text Box Text Box txtCustomerName Customer Name:  
    Text Box Text Box txtAddress Address:  
    Text Box Text Box txtCity City:  
    Text Box Text Box txtState State:  
    Text Box Text Box txtZIPCode ZIP Code:  
    Text Box Text Box txtMake Make/Model:  
    Text Box Text Box txtModel    
    Text Box Text Box txtCarYear Year:  
    Text Box Text Box txtProblemDescription Problem Description:  
    Tab Control Tab Control      
    Page   pgePartsUsed Parts Used  
    Label Label   Part Name  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub-Total  
    Text Box Text Box txtPartName1    
    Text Box Text Box txtUnitPrice1   Format: Fixed
    Text Box Text Box txtQuantity1    
    Text Box Text Box txtSubTotal1   Format: Fixed
    Text Box Text Box txtPartName2    
    Text Box Text Box txtUnitPrice2   Format: Fixed
    Text Box Text Box txtQuantity2    
    Text Box Text Box txtSubTotal2   Format: Fixed
    Text Box Text Box txtPartName3    
    Text Box Text Box txtUnitPrice3   Format: Fixed
    Text Box Text Box txtQuantity3    
    Text Box Text Box txtSubTotal3   Format: Fixed
    Text Box Text Box txtPartName4    
    Text Box Text Box txtUnitPrice4   Format: Fixed
    Text Box Text Box txtQuantity4    
    Text Box Text Box txtSubTotal4   Format: Fixed
    Text Box Text Box txtPartName5    
    Text Box Text Box txtUnitPrice5   Format: Fixed
    Text Box Text Box txtQuantity5    
    Text Box Text Box txtSubTotal5   Format: Fixed
    Page   pgeJobsPerformed Jobs Performed  
    Label Label   Job Name  
    Label Label   Cost  
    Text Box Text Box txtJobName1    
    Text Box Text Box txtJobCost1   Format: Fixed
    Text Box Text Box txtJobName2    
    Text Box Text Box txtJobCost2   Format: Fixed
    Text Box Text Box txtJobName3    
    Text Box Text Box txtJobCost3   Format: Fixed
    Text Box Text Box txtJobName4    
    Text Box Text Box txtJobCost4   Format: Fixed
    Text Box Text Box txtJobName5    
    Text Box Text Box txtJobCost5   Format: Fixed
    Text Box Text Box txtTotalParts Total Parts:  
    Text Box Text Box txtTotalLabor Total Labor:  
    Text Box Text Box txtTotalRepair Total Repair:  
    Text Box Text Box txtCarYear Year:  
    Text Box Text Box txtRecommendations Recommendations:  
    Button Button cmdSaveRepairOrder Save Repair Order  
    Button Button cmdResetForm Reset Form  
    Button Button cmdClose Close  
  10. Right-click Reset Form and click Build Event...
  11. In the Choose Builder dialog box, click Code Builder and click OK
  12. Implement the event as follows:
    Private Sub cmdResetForm_Click()
        txtCustomerName = ""
        txtAddress = ""
        txtCity = ""
        txtState = ""
        txtZIPCode = ""
        txtMake = ""
        txtModel = ""
        txtCarYear = ""
        txtProblemDescription = ""
        
        txtPartName1 = "": txtUnitPrice1 = ""
        txtQuantity1 = "": txtSubTotal1 = ""
        txtPartName2 = "": txtUnitPrice2 = ""
        txtQuantity2 = "": txtSubTotal2 = ""
        txtPartName3 = "": txtUnitPrice3 = ""
        txtQuantity3 = "": txtSubTotal3 = ""
        txtPartName4 = "": txtUnitPrice4 = ""
        txtQuantity4 = "": txtSubTotal4 = ""
        txtPartName5 = "": txtUnitPrice5 = ""
        txtQuantity5 = "": txtSubTotal5 = ""
        
        txtJobName1 = "": txtJobCost1 = ""
        txtJobName2 = "": txtJobCost2 = ""
        txtJobName3 = "": txtJobCost3 = ""
        txtJobName4 = "": txtJobCost4 = ""
        txtJobName5 = "": txtJobCost5 = ""
        
        txtTotalParts = "0.00"
        txtTotalLabor = "0.00"
        txtTotalRepair = "0.00"
        txtRecommendations = ""
    End Sub
    
    Private Sub CalculateOrder()
        Dim UnitPrice1 As Double, UnitPrice2 As Double, _
            UnitPrice3 As Double, UnitPrice4 As Double, _
            UnitPrice5 As Double
        Dim Quantity1 As Integer, Quantity2 As Integer, _
            Quantity3 As Integer, Quantity4 As Integer, _
            Quantity5 As Integer
        Dim SubTotal1 As Double, SubTotal2 As Double, _
            SubTotal3 As Double, SubTotal4 As Double, _
            SubTotal5 As Double
        Dim TotalParts As Double, TotalLabor As Double
        Dim JobCost1, JobCost2, JobCost3, JobCost4, JobCost5 As Double
                     
        If txtPartName1 = "" Then
            SubTotal1 = 0
        Else
            SubTotal1 = CDbl(Nz(txtUnitPrice1)) * CInt(Nz(txtQuantity1))
        End If
        
        If txtPartName2 = "" Then
            SubTotal2 = 0
        Else
            SubTotal2 = CDbl(Nz(txtUnitPrice2)) * CInt(Nz(txtQuantity2))
        End If
        
        If txtPartName3 = "" Then
            SubTotal3 = 0
        Else
            SubTotal3 = CDbl(Nz(txtUnitPrice3)) * CInt(Nz(txtQuantity3))
        End If
        
        If txtPartName4 = "" Then
            SubTotal4 = 0
        Else
            SubTotal4 = CDbl(Nz(txtUnitPrice4)) * CInt(Nz(txtQuantity4))
        End If
        
        If txtPartName5 = "" Then
            SubTotal5 = 0
        Else
            SubTotal5 = CDbl(Nz(txtUnitPrice5)) * CInt(Nz(txtQuantity5))
        End If
        
        If txtJobCost1 = "" Then
            JobCost1 = 0
        Else
            JobCost1 = CDbl(Nz(txtJobCost1))
        End If
        If txtJobCost2 = "" Then
            JobCost2 = 0
        Else
            JobCost2 = CDbl(Nz(txtJobCost2))
        End If
        If txtJobCost3 = "" Then
            JobCost3 = 0
        Else
            JobCost3 = CDbl(Nz(txtJobCost3))
        End If
        If txtJobCost4 = "" Then
            JobCost4 = 0
        Else
            JobCost4 = CDbl(Nz(txtJobCost4))
        End If
        If txtJobCost5 = "" Then
            JobCost5 = 0
        Else
            JobCost5 = CDbl(Nz(txtJobCost5))
        End If
        
        TotalParts = SubTotal1 + SubTotal2 + SubTotal3 + _
                     SubTotal4 + SubTotal5
        TotalLabor = JobCost1 + JobCost2 + JobCost3 + JobCost4 + JobCost5
        
        txtTotalParts = TotalParts
        txtTotalLabor = TotalLabor
        txtTotalRepair = FormatNumber(TotalParts + TotalLabor)
    End Sub
    
    Private Sub txtJobCost1_LostFocus()
        If (Not IsNull(txtJobName1)) And (Not IsNull(txtJobName1)) Then
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtJobCost2_LostFocus()
        CalculateOrder
    End Sub
    
    Private Sub txtJobCost3_LostFocus()
        CalculateOrder
    End Sub
    
    Private Sub txtJobCost4_LostFocus()
        CalculateOrder
    End Sub
    
    Private Sub txtJobCost5_LostFocus()
        CalculateOrder
    End Sub
    
    Private Sub txtQuantity1_LostFocus()
        If txtPartName1 <> "" Then
            txtSubTotal1 = CDbl(Nz(txtUnitPrice1)) * CInt(Nz(txtQuantity1))
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtQuantity2_LostFocus()
        If txtPartName2 <> "" Then
            txtSubTotal2 = CDbl(Nz(txtUnitPrice2)) * CInt(Nz(txtQuantity2))
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtQuantity3_LostFocus()
        If txtPartName3 <> "" Then
            txtSubTotal3 = CDbl(Nz(txtUnitPrice3)) * CInt(Nz(txtQuantity3))
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtQuantity4_LostFocus()
        If txtPartName4 <> "" Then
            txtSubTotal4 = CDbl(Nz(txtUnitPrice4)) * CInt(Nz(txtQuantity4))
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtQuantity5_LostFocus()
        If txtPartName5 <> "" Then
            txtSubTotal5 = CDbl(txtUnitPrice5) * CInt(txtQuantity5)
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtUnitPrice1_LostFocus()
        If (txtPartName1 <> "") And (txtUnitPrice1 <> "") Then
            txtQuantity1 = "1"
            txtSubTotal1 = txtUnitPrice1
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtUnitPrice2_LostFocus()
        If (txtPartName2 <> "") And (txtUnitPrice2 <> "") Then
            txtQuantity2 = "1"
            txtSubTotal2 = txtUnitPrice2
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtUnitPrice3_LostFocus()
        If (txtPartName3 <> "") And (txtUnitPrice3 <> "") Then
            txtQuantity3 = "1"
            txtSubTotal3 = txtUnitPrice3
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtUnitPrice4_LostFocus()
        If (txtPartName4 <> "") And (txtUnitPrice4 <> "") Then
            txtQuantity4 = "1"
            txtSubTotal4 = txtUnitPrice4
            CalculateOrder
        End If
    End Sub
    
    Private Sub txtUnitPrice5_LostFocus()
        If (txtPartName5 <> "") And (txtUnitPrice5 <> "") Then
            txtQuantity5 = "1"
            txtSubTotal5 = txtUnitPrice5
            CalculateOrder
        End If
    End Sub
    
    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_Error
    
        DoCmd.Close , ""
    
    cmdClose_Exit:
        Exit Sub
    
    cmdClose_Error:
        MsgBox Error$
        Resume cmdClose_Exit
    
    End Sub
  13. Close the form
  14. When asked whether you want to save it, click Yes
  15. In the Navigation Pane, right-click NewRepairOder and click Copy
  16. Right-click an empty area in the Navigation Pane and click Paste
  17. Set the Name to OpenRepairOrder
  18. Click OK
  19. In the Navigation pane, right-click OpenRepairOrder and click Design View
  20. Design the form as follows:
     
    College Park Auto Repair - Open Repair Order
    Control Name Caption Other Properties
    Label Label lblMainTitle College Park Auto Repair Fore Color: #FFFFFF
    Label Label lblMainTitleShadow College Park Auto Repair Fore Color: Text 1
    Option Group Option Group   Order Identification  
    Text Box Text Box txtCustomerName Customer Name:  
    Text Box Text Box txtAddress Address:  
    Text Box Text Box txtCity City:  
    Text Box Text Box txtState State:  
    Text Box Text Box txtZIPCode ZIP Code:  
    Text Box Text Box txtMake Make/Model:  
    Text Box Text Box txtModel    
    Text Box Text Box txtCarYear Year:  
    Text Box Text Box txtProblemDescription Problem Description:  
    Tab Control Tab Control      
    Page   pgePartsUsed Parts Used  
    Label Label   Part Name  
    Label Label   Unit Price  
    Label Label   Qty  
    Label Label   Sub-Total  
    Text Box Text Box txtPartName1    
    Text Box Text Box txtUnitPrice1   Format: Fixed
    Text Box Text Box txtQuantity1    
    Text Box Text Box txtSubTotal1   Format: Fixed
    Text Box Text Box txtPartName2    
    Text Box Text Box txtUnitPrice2   Format: Fixed
    Text Box Text Box txtQuantity2    
    Text Box Text Box txtSubTotal2   Format: Fixed
    Text Box Text Box txtPartName3    
    Text Box Text Box txtUnitPrice3   Format: Fixed
    Text Box Text Box txtQuantity3    
    Text Box Text Box txtSubTotal3   Format: Fixed
    Text Box Text Box txtPartName4    
    Text Box Text Box txtUnitPrice4   Format: Fixed
    Text Box Text Box txtQuantity4    
    Text Box Text Box txtSubTotal4   Format: Fixed
    Text Box Text Box txtPartName5    
    Text Box Text Box txtUnitPrice5   Format: Fixed
    Text Box Text Box txtQuantity5    
    Text Box Text Box txtSubTotal5   Format: Fixed
    Page   pgeJobsPerformed Jobs Performed  
    Label Label   Job Name  
    Label Label   Cost  
    Text Box Text Box txtJobName1    
    Text Box Text Box txtJobCost1   Format: Fixed
    Text Box Text Box txtJobName2    
    Text Box Text Box txtJobCost2   Format: Fixed
    Text Box Text Box txtJobName3    
    Text Box Text Box txtJobCost3   Format: Fixed
    Text Box Text Box txtJobName4    
    Text Box Text Box txtJobCost4   Format: Fixed
    Text Box Text Box txtJobName5    
    Text Box Text Box txtJobCost5   Format: Fixed
    Text Box Text Box txtTotalParts Total Parts:  
    Text Box Text Box txtTotalLabor Total Labor:  
    Text Box Text Box txtTotalRepair Total Repair:  
    Text Box Text Box txtCarYear Year:  
    Text Box Text Box txtRecommendations Recommendations:  
    Button Button cmdOpenRepairOrder Save Repair Order  
    Button Button cmdUpdateRepairOrder Update Repair Order  
    Button Button cmdResetForm Reset Form  
    Button Button cmdClose Close  
  21. Close the form
  22. When asked whether you want to save the form, click Yes

Creating an XML File in Microsoft Access

To create an example file in Microsoft Access, you have four primary options, ranging from ridiculously easy to a little complex. The first thing to know is that Microsoft has done an incredibly efficient work in XML, not only in Microsoft Access but throughout the Microsoft Windows family of operating systems. For example, it is so easy to create an XML file in Microsoft Access without writing a single line of code.

The four options to use XML in Microsoft Access are:

  • Visually importing and/or exporting XML
  • Using File Processing to manually create an XML document and file
  • Using the Application class
  • Using the MSXML library

Each one of those options has its advantages and disadvantages.

One of the basic ways to view an XML document is to open it in a Web browser.

Instead of going through the classic easy way, we will study XML starting with a kind of difficult way (file processing), followed by another but detailed hard way (MSXML), and then end with the easy ways (import/export and the Application class). The reason is that if we start with the easy way, Microsoft Access takes care of almost everything for you, but you may end up asking many questions about what is going on and why something is happening. On the other hand, if we start with file processing and MSXML, we are obliged to explain everything. That way, by the time you get to the graphical and easy way Microsoft Access deals with XML, you will know exactly what is going on.

 
 
 

Introducing XML Structure

  

Overview

File processing consists of:

  • Creating values in a document and saving those values in a file
  • Opening a file to retrieve some values stored in it

In the same way, you can create XML to be stored in a file, or you can open an XML document to read its content and use it as you see fit.

Introduction to XML Elements

An XML document is made of sections called elements. An XML file can have as many elements as possible. This means that they can range in the dozens, hundreds, or thousands. The operating system has the responsibility of being able to handle the file if it gets exceedingly large. To rightly play its role, an element must follow some rules.

An XML element is classically made of three parts, two of which are called tags: a start tag and an end tag. A start tag is made of three parts: <, followed by a name, and ending with >. An example is

<DEPARTMENT>

The name must follow some rules:

  • A name can start with a letter or an underscore (_)
  • After the starting letter or underscore, the name can contain letters, underscores (_), digits (1, 2, 3, 4, 5, 6, 7, 8, 9, or 0), hyphens (-), and periods (.)
  • A name must not include anything that is not in the above two rules. This means that a name cannot have empty spaces and cannot have special characters
  • A name is case-sensitive. This means that tag, TAG, and Tag are different

As started above, an element must have a start tag and an end tag. The end tag appears like the start tag but must include a forward slash after the <. This also means that the end tag must have the same name as the start tag. An example is:

</DEPARTMENT>

Between the start tag and the end tag, an element can have a value. An example is:

<DEPARTMENT>Environmental Protection Agency</DEPARTMENT>

This can be created in file processing as follows:

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Departments1.xml" For Output As #1

    Print #1, "<DEPARTMENT>Environmental Protection Agency</DEPARTMENT>"

    Close #1

    Exit Sub

cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
	   vbOKOnly Or vbInformation, _
	   "Extensible Markup Language"
    Resume Next
End Sub

An Empty Element

We stated that an element could have a value. This means that some elements do not have a value. An example is:

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Departments2.xml" For Output As #1

    Print #1, "<DEPARTMENT></DEPARTMENT>"

    Close #1

    Exit Sub

cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
	   vbOKOnly Or vbInformation, _
	   "Extensible Markup Language"

    Resume Next
End Sub

This is called an empty element. When an element does not have a value, you can use one tag that would act as the start and end tags. In this case, omit the end tag but, between the start tag's name and >, add a forward slash. An example is:

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Departments3.xml" For Output As #1
    
    Print #1, "<DEPARTMENT/>"
    
    Close #1
    
    Exit Sub
  
cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
    vbOKOnly Or vbInformation, _
    "Extensible Markup Language"
    
    Resume Next
End Sub

It is usually a good idea to add an empty space before the forward slash to make it easy to read. Here is an example:

<DEPARTMENT />

To resume, an XML element can be made of:

  • A start tag, a value and an end tag. Here is an example:
    <FirstName>Frank</FirstName>
  • A start tag and an end tag with nothing between them. This is called an empty element. Here is an example:
    <MiddleName></MiddleName>
  • One tag that acts as the start and end tags with no value. Here is an example:
    <MiddleName/>

XML Declaration

An XML file is made of elements created using regular text characters. When an application or an environment wants to use that file, it must be able to recognize the various elements. The program that must read and analyze the content of the XML document is called a parser.

The values of some of the elements in an XML document may not be in regular US English, which means that they may use International characters or symbols, referred to as Unicode. Some other values may include complex or special characters. One way to assist the parser is to indicate whether the document uses only regular (US English) words or regular and Unicode characters. To give such an instruction, you must add a line in the top section of the document. Such a line is called an XML declaration. An XML declaration is not required in an XML document, especially if you know that the parsers that will use your XML file can read it without a problem. In most cases, although the XML declaration is not required, you don’t lose anything by adding it.

An XML declaration:

  • Must appear as the first line in the XML document
  • Is a special element with only a start tag
  • Is an element that starts with <?xml and ends with ?>
  • Must include a version number. The version number is created as version="1.0". An example is:
    <?xml version="1.0"?>
  • As stated already, you can indicate the type of character scheme applied to the letters in the document. This scheme is called an encoding. To specify it, assign "UTF-8" or "UTF-16" to the encoding word. Here is an example:
    <?xml version="1.0" encoding="UTF-8"?>
    To do this in a file processing code, you can include UTF-8 in single-quotes. Here is an example:
    Private Sub cmdXMLElement_Click()
    On Error GoTo cmdElement_Error
    
        Open "Departments4.xml" For Output As #1
    
        Print #1, "<?xml version='1.0' encoding='UTF-8'?>"
    
        Print #1, "<DEPARTMENT>Environmental Protection Agency</DEPARTMENT>"
        
        Close #1
    
        Exit Sub
    
    cmdElement_Error:
        MsgBox "There was a problem when trying to create the XML file.", _
            vbOKOnly Or vbInformation, _
            "Extensible Markup Language"
        
        Resume Next
    End Sub

You probably know already that, in the Visual Basic language, to include a double-quote in your code, you can double it. This is another way to apply the encoding. Other types of encodings can be used. UTF-8 and UTF-16 are just two examples.

XML Elements and Their Relationships

   

Introduction to the Nodes of an XML Document

As mentioned already, an XML document contains one or a series of elements. The elements follow one another like a bulleted list of a text document. The document is organized like a tree (an upside-down tree). For this reason, each part (or element) is referred to as a node.

The Root Element

An XML document can have just one element. Another XML document can have as many elements as necessary. Here is an example:

<Employee>Frank Misma 10/04/2008 General Manager</Employee>
<Employee>Justine Grand 05/30/2012 Sales Associates</Employee>
<Employee>Arnold Alley 5/2/2010 Webmaster</Employee>

If an XML document contains more than one element and those elements are on the same level (in later sections, or little by little, we will find out what “level” means), the elements must be included in another element. This is done as follows. Create a start tag. Add the necessary elements, and close with an end tag that corresponds to the earlier start tag. Here is an example:

<Identification>
<Employee>Frank Misma 10/04/2008 General Manager</Employee>
<Employee>Justine Grand 05/30/2012 Sales Associates</Employee>
<Employee>Arnold Alley 5/2/2010 Webmaster</Employee>
</Identification>

If an XML document contains many elements, there must be a special element that starts the document. The start tag of that element is in the beginning of the document. That element is referred to as the root of the document. In the above example, <Identification> is the root of the document. If the document contains an XML declaration, the root is created just after the declaration. Here is an example:

<?xml version="1.0" encoding=”UTF-8”?>
<Identification>
<Employee>Frank Misma 10/04/2008 General Manager</Employee>
<Employee>Justine Grand 05/30/2012 Sales Associates</Employee>
<Employee>Arnold Alley 5/2/2010 Webmaster</Employee>
</Identification>

This can be created in a file as follows:

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Departments5.xml" For Output As #1
    
    Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
    Print #1, "<Identification>"
    Print #1, "<Employee>Frank Misma 10/04/2008 General Manager</Employee>"
    Print #1, "<Employee>Justine Grand 05/30/2012 Sales Associates</Employee>"
    Print #1, "<Employee>Arnold Alley 5/2/2010 Webmaster</Employee>"
    Print #1, "</Identification>"

    Close #1
    
    Exit Sub

cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
    	vbOKOnly Or vbInformation, _
    	"Extensible Markup Language"
    	Resume Next
End Sub

The Parent Node of an Element

An element can be created inside of another element. Here is an example:

<Identification>
<Employee>Frank Misma</Employee>
</Identification>

In this case, the <Employee> element is created inside the <Identification> Element. We also say that the <Employee> element is nested in the <Identification> element. As a result, the <Employee> element is considered, or referred to as, the parent of the <Identification> Element. In the same way, a parent of a node can be a parent of another node. An element can be a parent of a node that itself is a parent.

Indentation

Indentation consists of aligning the elements of a document to make it easy to read. To make it possible, the elements are considered by levels. The root node is the first level. Any node created inside of it is considered in the second or next level. Subsequently, other nodes can be created inside of other nodes.

Indentation consists of leaving empty spaces on the left side of an element with regards to the parent node. The typical number of empty spaces is 2. Normally, the XML declaration and the root node should be on the same level. Here is an example:

<?xml version="1.0" encoding=”UTF-8”?>
<Identification>
  <Employee>Frank Misma 10/04/2008 General Manager</Employee>
  <Employee>Justine Grand 05/30/2012 Sales Associates</Employee>
  <Employee>Arnold Alley 5/2/2010 Webmaster</Employee>
</Identification>

If you decide to indent by 4 characters, this is equivalent to pressing Tab. Here is an example:

<?xml version="1.0" encoding=”UTF-8”?>
<Identification>
    <Employee>Frank Misma 10/04/2008 General Manager</Employee>
    <Employee>Justine Grand 05/30/2012 Sales Associates</Employee>
    <Employee>Arnold Alley 5/2/2010 Webmaster</Employee>
</Identification>

You can use indentation in your text editor. Indentation is not necessary if you are creating your XML file in Microsoft Access.

The Child Nodes (or Children) of a Parent

As mentioned already, an element can be created inside another element. This is also referred to as nesting an element. This means that the element created inside is said to be nested. The element that encloses another is referred to as the parent. The element created inside is referred to as a child element or a child node. Consider the following example:

<Identification>
  <Employee>Frank Misma</Employee>
</Identification>

In this case, the <Employee> node is the child of the <Identification> node. In the same way, you can create as many child nodes to a parent as you want. Here is an example we saw already:

<?xml version="1.0" encoding=”UTF-8”?>
<Identification>
  <Employee>Frank Misma 10/04/2008 General Manager</Employee>
  <Employee>Justine Grand 05/30/2012 Sales Associates</Employee>
  <Employee>Arnold Alley 5/2/2010 Webmaster</Employee>
</Identification>

In this case, the parent <Identification> node has three children that are all named Employee. The child nodes of a parent can have different names even if they are on the same level. Of course, a child node can have only one parent, but a parent can have as many children as necessary.

An element can be created as a child of an element that itself is a child of another element. In fact, this is a good feature to organize the elements of an XML document by illustrating their dependencies. This is also where indentation is important. Here are examples:

<?xml version="1.0" encoding="UTF-8"?>
<Employees>
  <Employee>
    <FirstName>Frank</FirstName>
    <LastName>Misma</LastName>
    <DateHired>10/04/2008</DateHired>
    <Title>General Manager</Title>
  </Employee>
  <Employee>
    <FirstName>Justine</FirstName>
    <LastName>Grand</LastName>
    <DateHired>05/30/2012</DateHired>
    <Title>Sales Associates</Title>
  </Employee>
  <Employee>
    <FirstName>Arnold</FirstName>
    <LastName>Alley</LastName>
    <DateHired>5/2/2010</DateHired>
    <Title>Webmaster</Title>
  </Employee>
</Employees>

The document can be created in file processing as follows (indentation is used simply to make the document easy to read; indentation is not required):

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Employees1.xml" For Output As #1

    Print #1, "<?xml version='1.0' encoding='UTF-8'?>"
    Print #1, "<Employees>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Frank</FirstName>"
    Print #1, "    <LastName>Misma</LastName>"
    Print #1, "    <DateHired>10/04/2008</DateHired>"
    Print #1, "    <Title>General Manager</Title>"
    Print #1, "  </Employee>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Justine</FirstName>"
    Print #1, "    <LastName>Grand</LastName>"
    Print #1, "    <DateHired>05/30/2012</DateHired>"
    Print #1, "    <Title>Sales Associates</Title>"
    Print #1, "  </Employee>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Arnold</FirstName>"
    Print #1, "    <LastName>Alley</LastName>"
    Print #1, "    <DateHired>5/2/2010</DateHired>"
    Print #1, "    <Title>Webmaster</Title>"
    Print #1, "  </Employee>"
    Print #1, "</Employees>"

    Close #1
    
    Exit Sub

cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
         vbOKOnly Or vbInformation, _
         "Extensible Markup Language"
    Resume Next
End Sub

A child node is just another XML element. It must have a start tag. It can be empty, which means it may not have a value, in which case it may not have an end tag. Here are examples:

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Employees2.xml" For Output As #1

    Print #1, "<?xml version='1.0' encoding='UTF-8'?>"
    Print #1, "<Employees>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Frank</FirstName>"
    Print #1, "    <LastName>Misma</LastName>"
    Print #1, "    <DateHired>10/04/2008</DateHired>"
    Print #1, "    <Title>General Manager</Title>"
    Print #1, "  </Employee>"
    Print #1, "  <Employee></Employee>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Justine</FirstName>"
    Print #1, "    <MiddleName></MiddleName>"
    Print #1, "    <LastName>Grand</LastName>"
    Print #1, "    <DateHired>05/30/2012</DateHired>"
    Print #1, "    <Title>Sales Associates</Title>"
    Print #1, "  </Employee>"
    Print #1, "  <Employee/>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Arnold</FirstName>"
    Print #1, "    <MiddleName />"
    Print #1, "    <LastName>Alley</LastName>"
    Print #1, "    <DateHired>5/2/2010</DateHired>"
    Print #1, "    <Title>Webmaster</Title>"
    Print #1, "    <Salary></Salary>"
    Print #1, "  </Employee>"
    Print #1, "</Employees>"

    Close #1
    
    Exit Sub

cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
         vbOKOnly Or vbInformation, _
         "Extensible Markup Language"
    Resume Next
End Sub

The Value of a Parent Element

A parent node can have its own value. To specify its value, type it just after the start tag and before its child node(s). Here are two examples:

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Employees3.xml" For Output As #1

    Print #1, "<?xml version='1.0' encoding='UTF-8'?>"
    Print #1, "<Employees>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Frank</FirstName>"
    Print #1, "    <LastName>Misma</LastName>"
    Print #1, "    <DateHired>10/04/2008</DateHired>"
    Print #1, "    <Title>General Manager</Title>"
    Print #1, "  </Employee>"
    Print #1, "  <Employee>First Sales Associate to be hired"
    Print #1, "    <FirstName>Justine</FirstName>"
    Print #1, "    <LastName>Grand</LastName>"
    Print #1, "    <DateHired>05/30/2012</DateHired>"
    Print #1, "    <Title>Sales Associates</Title>"
    Print #1, "  </Employee>"
    Print #1, "  <Employee>For the time being, the webmaster also acts as the application developer."
    Print #1, "    <FirstName>Arnold</FirstName>"
    Print #1, "    <LastName>Alley</LastName>"
    Print #1, "    <DateHired>5/2/2010</DateHired>"
    Print #1, "    <Title>Webmaster</Title>"
    Print #1, "  </Employee>"
    Print #1, "</Employees>"

    Close #1
    
    Exit Sub

cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
         vbOKOnly Or vbInformation, _
         "Extensible Markup Language"
    Resume Next
End Sub

The Siblings of an Element

The elements on the same level are referred to as siblings. Consider the following elements:

Private Sub cmdXMLElement_Click()
On Error GoTo cmdElement_Error

    Open "Employees4.xml" For Output As #1

    Print #1, "<?xml version="1.0" encoding=”UTF-8”?>"
    Print #1, "<Employees>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Frank</FirstName>"
    Print #1, "    <LastName>Misma</LastName>"
    Print #1, "    <DateHired>10/04/2008</DateHired>"
    Print #1, "    <Title>General Manager</Title>"
    Print #1, "  </Employee>"
    Print #1, "  <Employee>"
    Print #1, "    <FirstName>Justine</FirstName>"
    Print #1, "    <LastName>Grand</LastName>"
    Print #1, "    <DateHired>05/30/2012</DateHired>"
    Print #1, "    <Title>Sales Associates</Title>"
    Print #1, "    <HourlySalary>22.15</HourlySalary>"
    Print #1, "  </Employee>"
    Print #1, "  <Contractor>"
    Print #1, "    <FullName>Arnold Alley</LastName>"
    Print #1, "    <Job>Webmaster</Job>"
    Print #1, "  </Contractor>"
    Print #1, "</Employees>"

    Close #1
    
    Exit Sub

cmdElement_Error:
    MsgBox "There was a problem when trying to create the XML file.", _
         vbOKOnly Or vbInformation, _
         "Extensible Markup Language"
    Resume Next
End Sub

In this case, the <Employee> and the <Contractor> elements are siblings. The <FirstName>, the <LastName, the <DateHired>, and the <Title> elements of the first <Employee> node are siblings. In the same way, the <FullName> and the <Job> nodes are sibling elements.

Practical LearningPractical Learning: Creating an XML File

  1. In the Navigation pane, right-click the NewRepairOrder form and click Design View
  2. On the form, right-click Save Repair Order and click Build Event
  3. In the Choose Builder dialog box, double-click Code Builder
  4. Implement the event as follows:
    Private Sub cmdSaveRepairOder_Click()
    On Error GoTo cmdSaveRepairOder_Error
        Dim Action As Integer
        Dim dlgSaveFile As FileDialog
        Dim strProblemDescription As String
        Dim strJobName1 As String, strPartName2 As String, strJobName2 As String
        Dim strJobName3 As String, strJobName4 As String, strJobName5 As String
        Set dlgSaveFile = Application.FileDialog(msoFileDialogSaveAs)
    
        Action = dlgSaveFile.Show
        
        If Action <> 0 Then
            
            Open dlgSaveFile.SelectedItems(1) & ".xml" For Output As #1
        
            Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
            Print #1, "<RepairOrder>"
            Print #1, "<CustomerName>" & txtCustomerName & "</CustomerName>"
            Print #1, "<Address>" & txtAddress & "</Address>"
            Print #1, "<City>" & txtCity & "</City>"
            Print #1, "<State>" & txtState & "</State>"
            Print #1, "<ZIPCode>" & txtZIPCode & "</ZIPCode>"
            Print #1, "<Make>" & txtMake & "</Make>"
            Print #1, "<Model>" & txtModel & "</Model>"
            Print #1, "<CarYear>" & txtCarYear & "</CarYear>"
            
            If IsNull(txtProblemDescription) Then
                Print #1, "<ProblemDescription></ProblemDescription>"
            Else
                Print #1, "<ProblemDescription>" & Replace(txtProblemDescription, ",", "&#044;") & "</ProblemDescription>"
            End If
            
    
            If IsNull(txtPartName1) Then
                Print #1, "<PartName1></PartName1>"
            Else
                Print #1, "<PartName1>" & Replace(txtPartName1, ",", "&#044;") & "</PartName1>"
            End If
            Print #1, "<UnitPrice1>" & txtUnitPrice1 & "</UnitPrice1>"
            Print #1, "<Quantity1>" & txtQuantity1 & "</Quantity1>"
            Print #1, "<SubTotal1>" & txtSubTotal1 & "</SubTotal1>"
            
            If IsNull(txtPartName2) Then
                Print #1, "<PartName2></PartName2>"
            Else
                Print #1, "<PartName2>" & Replace(txtPartName2, ",", "&#044;") & "</PartName2>"
            End If
            Print #1, "<UnitPrice2>" & txtUnitPrice2 & "</UnitPrice2>"
            Print #1, "<Quantity2>" & txtQuantity2 & "</Quantity2>"
            Print #1, "<SubTotal2>" & txtSubTotal2 & "</SubTotal2>"
            
            If IsNull(txtPartName3) Then
                Print #1, "<PartName3></PartName3>"
            Else
                Print #1, "<PartName3>" & Replace(txtPartName3, ",", "&#044;") & "</PartName3>"
            End If
            Print #1, "<UnitPrice3>" & txtUnitPrice3 & "</UnitPrice3>"
            Print #1, "<Quantity3>" & txtQuantity3 & "</Quantity3>"
            Print #1, "<SubTotal3>" & txtSubTotal3 & "</SubTotal3>"
            
            If IsNull(txtPartName4) Then
                Print #1, "<PartName4></PartName4>"
            Else
                Print #1, "<PartName4>" & Replace(txtPartName4, ",", "&#044;") & "</PartName4>"
            End If
            Print #1, "<UnitPrice4>" & txtUnitPrice4 & "</UnitPrice4>"
            Print #1, "<Quantity4>" & txtQuantity4 & "</Quantity4>"
            Print #1, "<SubTotal4>" & txtSubTotal4 & "</SubTotal4>"
            
            If IsNull(txtPartName5) Then
                Print #1, "<PartName5></PartName5>"
            Else
                Print #1, "<PartName5>" & Replace(txtPartName5, ",", "&#044;") & "</PartName5>"
            End If
            Print #1, "<UnitPrice5>" & txtUnitPrice5 & "</UnitPrice5>"
            Print #1, "<Quantity5>" & txtQuantity5 & "</Quantity5>"
            Print #1, "<SubTotal5>" & txtSubTotal5 & "</SubTotal5>"
        
            If IsNull(txtJobName1) Then
                Print #1, "<JobName1></JobName1>"
            Else
                Print #1, "<JobName1>" & Replace(txtJobName1, ",", "&#044;") & "</JobName1>"
            End If
            Print #1, "<JobCost1>" & txtJobCost1 & "</JobCost1>"
            
            If IsNull(txtJobName2) Then
                Print #1, "<JobName2></JobName2>"
            Else
                Print #1, "<JobName2>" & Replace(txtJobName2, ",", "&#044;") & "</JobName2>"
            End If
            Print #1, "<JobCost2>" & txtJobCost2 & "</JobCost2>"
            
            If IsNull(txtJobName3) Then
                Print #1, "<JobName3></JobName3>"
            Else
                Print #1, "<JobName3>" & Replace(txtJobName3, ",", "&#044;") & "</JobName3>"
            End If
            Print #1, "<JobCost3>" & txtJobCost3 & "</JobCost3>"
            
            If IsNull(txtJobName4) Then
                Print #1, "<JobName4></JobName4>"
            Else
                Print #1, "<JobName4>" & Replace(txtJobName4, ",", "&#044;") & "</JobName4>"
            End If
            Print #1, "<JobCost4>" & txtJobCost4 & "</JobCost4>"
            
            If IsNull(txtJobName5) Then
                Print #1, "<JobName5></JobName5>"
            Else
                Print #1, "<JobName5>" & Replace(txtJobName5, ",", "&#044;") & "</JobName5>"
            End If
            Print #1, "<JobCost5>" & txtJobCost5 & "</JobCost5>"
        
            Print #1, "<TotalParts>" & txtTotalParts & "</TotalParts>"
            Print #1, "<TotalLabor>" & txtTotalLabor & "</TotalLabor>"
            Print #1, "<TotalRepair>" & txtTotalRepair & "</TotalRepair>"
            
            If IsNull(txtRecommendations) Then
                Print #1, "<Recommendations></Recommendations>"
            Else
                Print #1, "<Recommendations>" & Replace(txtRecommendations, ",", "&#044;") & "</Recommendations>"
            End If
            Print #1, "</RepairOrder>"
        
            Close #1
        
        End If
        
        Set dlgSaveFile = Nothing
        cmdResetForm_Click
        
        Exit Sub
        
    cmdSaveRepairOder_Error:
        MsgBox "There is a problem with the repair order. It cannot be saved.", _
               vbOKOnly Or vbInformation, _
               "College Park Auto Repair"
        Resume Next
    End Sub
    
    
  5. Close the NewRepairOrder form
  6. When asked whether you want to save, click Yes
  7. In the Navigation pane, right-click the OpenRepairOrder form and click Design View
  8. On the form, right-click Open Repair Order and click Build Event...
  9. In the Choose Builder dialog box, double-click Code Builder
  10. Implement the event as follows:
    Option Compare Database
    Option Explicit
    
    Private strFileName As String
    
    Private Sub cmdResetForm_Click()
        . . . No Change
        
        strFileName = ""
    End Sub
    
    Private Sub cmdOpenRepairOder_Click()
    On Error GoTo cmdOpenRepairOder_Error
        Dim Action As Integer
        Dim dlgOpenFile As FileDialog
        Dim strDeclaration As String, strRoot As String
        Dim CustomerName As String, Address As String, City As String
        Dim State As String, ZIPCode As String, Make As String, Model As String
        Dim CarYear As String, ProblemDescription As String
        Dim PartName1 As String, UnitPrice1 As String, Quantity1 As String
        Dim SubTotal1 As String, PartName2 As String, UnitPrice2 As String
        Dim Quantity2 As String, SubTotal2 As String, PartName3 As String
        Dim UnitPrice3 As String, Quantity3 As String, SubTotal3 As String
        Dim PartName4 As String, UnitPrice4 As String, Quantity4 As String
        Dim SubTotal4 As String, PartName5 As String, UnitPrice5 As String
        Dim Quantity5 As String, SubTotal5 As String
        Dim JobName1 As String, JobCost1 As String
        Dim JobName2 As String, JobCost2 As String
        Dim JobName3 As String, JobCost3 As String
        Dim JobName4 As String, JobCost4 As String
        Dim JobName5 As String, JobCost5 As String
        Dim TotalParts As String, TotalLabor As String
        Dim TotalRepair As String, Recommendations As String
        
        Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
    
        Action = dlgOpenFile.Show
        
        If Action <> 0 Then
            cmdResetForm_Click
            Open dlgOpenFile.SelectedItems(1) For Input As #1
            strFileName = dlgOpenFile.SelectedItems(1)
        
            Input #1, strDeclaration
            Input #1, strRoot
            Input #1, CustomerName
            Input #1, Address
            Input #1, City
            Input #1, State
            Input #1, ZIPCode
            Input #1, Make
            Input #1, Model
            Input #1, CarYear
            Input #1, ProblemDescription
        
            Input #1, PartName1
            Input #1, UnitPrice1
            Input #1, Quantity1
            Input #1, SubTotal1
            Input #1, PartName2
            Input #1, UnitPrice2
            Input #1, Quantity2
            Input #1, SubTotal2
            Input #1, PartName3
            Input #1, UnitPrice3
            Input #1, Quantity3
            Input #1, SubTotal3
            Input #1, PartName4
            Input #1, UnitPrice4
            Input #1, Quantity4
            Input #1, SubTotal4
            Input #1, PartName5
            Input #1, UnitPrice5
            Input #1, Quantity5
            Input #1, SubTotal5
        
            Input #1, JobName1
            Input #1, JobCost1
            Input #1, JobName2
            Input #1, JobCost2
            Input #1, JobName3
            Input #1, JobCost3
            Input #1, JobName4
            Input #1, JobCost4
            Input #1, JobName5
            Input #1, JobCost5
        
            Input #1, TotalParts
            Input #1, TotalLabor
            Input #1, TotalRepair
            Input #1, Recommendations
    
            txtCustomerName = Replace(Replace(Replace(CustomerName, "<CustomerName>", ""), "</CustomerName>", ""), ",", ",")
            txtAddress = Replace(Replace(Replace(Address, "<Address>", ""), "</Address>", ""), ",", ",")
            txtCity = Replace(Replace(Replace(City, "<City>", ""), "</City>", ""), ",", ",")
            txtState = Replace(Replace(Replace(State, "<State>", ""), "</State>", ""), ",", ",")
            txtZIPCode = Replace(Replace(ZIPCode, "<ZIPCode>", ""), "</ZIPCode>", "")
            txtMake = Replace(Replace(Make, "<Make>", ""), "</Make>", "")
            txtModel = Replace(Replace(Model, "<Model>", ""), "</Model>", "")
            txtCarYear = Replace(Replace(CarYear, "<CarYear>", ""), "</CarYear>", "")
            txtProblemDescription = Replace(Replace(Replace(ProblemDescription, "<ProblemDescription>", ""), "</ProblemDescription>", ""), ",", ",")
        
            txtPartName1 = Replace(Replace(Replace(PartName1, "<PartName1>", ""), "</PartName1>", ""), ",", ",")
            txtUnitPrice1 = Replace(Replace(UnitPrice1, "<UnitPrice1>", ""), "</UnitPrice1>", "")
            txtQuantity1 = Replace(Replace(Quantity1, "<Quantity1>", ""), "</Quantity1>", "")
            txtSubTotal1 = Replace(Replace(SubTotal1, "<SubTotal1>", ""), "</SubTotal1>", "")
            txtPartName2 = Replace(Replace(Replace(PartName2, "<PartName2>", ""), "</PartName2>", ""), ",", ",")
            txtUnitPrice2 = Replace(Replace(UnitPrice2, "<UnitPrice2>", ""), "</UnitPrice2>", "")
            txtQuantity2 = Replace(Replace(Quantity2, "<Quantity2>", ""), "</Quantity2>", "")
            txtSubTotal2 = Replace(Replace(SubTotal2, "<SubTotal2>", ""), "</SubTotal2>", "")
            txtPartName3 = Replace(Replace(Replace(PartName3, "<PartName3>", ""), "</PartName3>", ""), ",", ",")
            txtUnitPrice3 = Replace(Replace(UnitPrice3, "<UnitPrice3>", ""), "</UnitPrice3>", "")
            txtQuantity3 = Replace(Replace(Quantity3, "<Quantity3>", ""), "</Quantity3>", "")
            txtSubTotal3 = Replace(Replace(SubTotal3, "<SubTotal3>", ""), "</SubTotal3>", "")
            txtPartName4 = Replace(Replace(Replace(PartName4, "<PartName4>", ""), "</PartName4>", ""), ",", ",")
            txtUnitPrice4 = Replace(Replace(UnitPrice4, "<UnitPrice4>", ""), "</UnitPrice4>", "")
            txtQuantity4 = Replace(Replace(Quantity4, "<Quantity4>", ""), "</Quantity4>", "")
            txtSubTotal4 = Replace(Replace(SubTotal4, "<SubTotal4>", ""), "</SubTotal4>", "")
            txtPartName5 = Replace(Replace(Replace(PartName5, "<PartName5>", ""), "</PartName5>", ""), ",", ",")
            txtUnitPrice5 = Replace(Replace(UnitPrice5, "<UnitPrice5>", ""), "</UnitPrice5>", "")
            txtQuantity5 = Replace(Replace(Quantity5, "<Quantity5>", ""), "</Quantity5>", "")
            txtSubTotal5 = Replace(Replace(SubTotal5, "<SubTotal5>", ""), "</SubTotal5>", "")
            txtJobName1 = Replace(Replace(Replace(JobName1, "<JobName1>", ""), "</JobName1>", ""), ",", ",")
            txtJobCost1 = Replace(Replace(JobCost1, "<JobCost1>", ""), "</JobCost1>", "")
            txtJobName2 = Replace(Replace(Replace(JobName2, "<JobName2>", ""), "</JobName2>", ""), ",", ",")
            txtJobCost2 = Replace(Replace(JobCost2, "<JobCost2>", ""), "</JobCost2>", "")
            txtJobName3 = Replace(Replace(Replace(JobName3, "<JobName3>", ""), "</JobName3>", ""), ",", ",")
            txtJobCost3 = Replace(Replace(JobCost3, "<JobCost3>", ""), "</JobCost3>", "")
            txtJobName4 = Replace(Replace(Replace(JobName4, "<JobName4>", ""), "</JobName4>", ""), ",", ",")
            txtJobCost4 = Replace(Replace(JobCost4, "<JobCost4>", ""), "</JobCost4>", "")
            txtJobName5 = Replace(Replace(Replace(JobName5, "<JobName5>", ""), "</JobName5>", ""), ",", ",")
            txtJobCost5 = Replace(Replace(JobCost5, "<JobCost5>", ""), "</JobCost5>", "")
            
            txtTotalParts = Replace(Replace(TotalParts, "<TotalParts>", ""), "</TotalParts>", "")
            txtTotalLabor = Replace(Replace(TotalLabor, "<TotalLabor>", ""), "</TotalLabor>", "")
            txtTotalRepair = Replace(Replace(TotalRepair, "<TotalRepair>", ""), "</TotalRepair>", "")
            
            txtRecommendations = Replace(Replace(Replace(Recommendations, "<Recommendations>", ""), "</Recommendations>", ""), ",", ",")
        
            Close #1
        
        End If
        
        Set dlgOpenFile = Nothing
        
        Exit Sub
        
    cmdOpenRepairOder_Error:
        MsgBox "There is a problem with the repair order. It cannot be opened.", _
               vbOKOnly Or vbInformation, _
               "College Park Auto Repair"
        Resume Next
    End Sub
    
    Private Sub cmdResetForm_Click()
        . . . No Change
    
        strFileName = ""
    End Sub
    
    
  11. In the Object combo box, select cmdUpdateRepairOrder
  12. Implement the event as follows:
    Private Sub cmdUpdateRepairOrder_Click()
    On Error GoTo cmdUpdateRepairOder_Error
    
        If strFileName <> "" Then
            Open strFileName For Output As #1
        
            Print #1, "<?xml version=""1.0"" encoding=""UTF-8""?>"
            Print #1, "<RepairOrder>"
            Print #1, "<CustomerName>" & txtCustomerName & "</CustomerName>"
            Print #1, "<Address>" & txtAddress & "</Address>"
            Print #1, "<City>" & txtCity & "</City>"
            Print #1, "<State>" & txtState & "</State>"
            Print #1, "<ZIPCode>" & txtZIPCode & "</ZIPCode>"
            Print #1, "<Make>" & txtMake & "</Make>"
            Print #1, "<Model>" & txtModel & "</Model>"
            Print #1, "<CarYear>" & txtCarYear & "</CarYear>"
            Print #1, "<ProblemDescription>" & Replace(txtProblemDescription, ",", "&#044;") & "</ProblemDescription>"
            
            Print #1, "<PartName1>" & Replace(txtPartName1, ",", "&#044;") & "</PartName1>"
            Print #1, "<UnitPrice1>" & txtUnitPrice1 & "</UnitPrice1>"
            Print #1, "<Quantity1>" & txtQuantity1 & "</Quantity1>"
            Print #1, "<SubTotal1>" & txtSubTotal1 & "</SubTotal1>"
            Print #1, "<PartName2>" & Replace(txtPartName2, ",", "&#044;") & "</PartName2>"
            Print #1, "<UnitPrice2>" & txtUnitPrice2 & "</UnitPrice2>"
            Print #1, "<Quantity2>" & txtQuantity2 & "</Quantity2>"
            Print #1, "<SubTotal2>" & txtSubTotal2 & "</SubTotal2>"
            Print #1, "<PartName3>" & Replace(txtPartName3, ",", "&#044;") & "</PartName3>"
            Print #1, "<UnitPrice3>" & txtUnitPrice3 & "</UnitPrice3>"
            Print #1, "<Quantity3>" & txtQuantity3 & "</Quantity3>"
            Print #1, "<SubTotal3>" & txtSubTotal3 & "</SubTotal3>"
            Print #1, "<PartName4>" & Replace(txtPartName4, ",", "&#044;") & "</PartName4>"
            Print #1, "<UnitPrice4>" & txtUnitPrice4 & "</UnitPrice4>"
            Print #1, "<Quantity4>" & txtQuantity4 & "</Quantity4>"
            Print #1, "<SubTotal4>" & txtSubTotal4 & "</SubTotal4>"
            Print #1, "<PartName5>" & Replace(txtPartName5, ",", "&#044;") & "</PartName5>"
            Print #1, "<UnitPrice5>" & txtUnitPrice5 & "</UnitPrice5>"
            Print #1, "<Quantity5>" & txtQuantity5 & "</Quantity5>"
            Print #1, "<SubTotal5>" & txtSubTotal5 & "</SubTotal5>"
        
            Print #1, "<JobName1>" & Replace(txtJobName1, ",", "&#044;") & "</JobName1>"
            Print #1, "<JobCost1>" & txtJobCost1 & "</JobCost1>"
            Print #1, "<JobName2>" & Replace(txtJobName2, ",", "&#044;") & "</JobName2>"
            Print #1, "<JobCost2>" & txtJobCost2 & "</JobCost2>"
            Print #1, "<JobName3>" & Replace(txtJobName3, ",", "&#044;") & "</JobName3>"
            Print #1, "<JobCost3>" & txtJobCost3 & "</JobCost3>"
            Print #1, "<JobName4>" & Replace(txtJobName4, ",", "&#044;") & "</JobName4>"
            Print #1, "<JobCost4>" & txtJobCost4 & "</JobCost4>"
            Print #1, "<JobName5>" & Replace(txtJobName5, ",", "&#044;") & "</JobName5>"
            Print #1, "<JobCost5>" & txtJobCost5 & "</JobCost5>"
        
            Print #1, "<TotalParts>" & txtTotalParts & "</TotalParts>"
            Print #1, "<TotalLabor>" & txtTotalLabor & "</TotalLabor>"
            Print #1, "<TotalRepair>" & txtTotalRepair & "</TotalRepair>"
            Print #1, "<Recommendations>" & Replace(txtRecommendations, ",", "&#044;") & "</Recommendations>"
            Print #1, "</RepairOrder>"
        
            Close #1
        End If
            
        Exit Sub
        
    cmdUpdateRepairOder_Error:
        MsgBox "There is a problem with the repair order. It cannot be updated.", _
               vbOKOnly Or vbInformation, _
               "College Park Auto Repair"
        Resume Next
    End Sub
    
    
  13. Close Microsoft Visual Basic and return to Microsoft Access
  14. Close the form
  15. When asked whether you want to save, click Yes
  16. In the Navigation pane, double-click the the NewRepairOrder form
  17. Complete it with a repair order. Here is an example:
     
    New Repair Order
     
    New Repair Order
  18. Click Save Repair Order
  19. In the dialog box, set the file name using the initials of the customer name (our example would be cm)
  20. Click Save
  21. Create another repair order. Here is an example:
     
    New Repair Order
     
    New Repair Order
  22. Click Save Repair Order
  23. In the dialog box, set the file name using the initials of the customer name (our example would be fe)
  24. Close the NewRepairOrder
  25. In the Navigation pane, double-click the OpenRepairOrder form
  26. Click the Open Repair Order button
  27. In the dialog box, select a file name you had saved (such as cm.xml)
     
    New Repair Order
  28. Change some values in the form. Here is an example:
     
    New Repair Order
     
    New Repair Order
  29. Click Update Repair Order
  30. Click Open Repair Oder
  31. Select the other repair order
  32. Open the first repair order and make sure the changes you had made were saved
  33. Close the form
 
 
   
 

Previous Copyright © 2013 FunctionX, Inc. Next