Loop Through Objects in Access Database Using VBA

Posted on 2013-12-13
Medium Priority
Last Modified: 2013-12-16
I have created a form that contains employee details. I have connected the form to a web service provided by our Human Resources department. The user clicks a button and automatically most of the fields are updated.

The problem that I am having is that if the user doesn’t click the button then they don’t know if the record needs to be update. To be honest if the user clicks the button and there is an update, it just updates, the user never really knows what was updated.

Is there a way that I can alert the user of possible updates for fields by, I don’t know, maybe making a red asterisk visible or something like that. Sometimes it is not necessary to update all of the available fields from the web services so I was also hoping that an update button could appear next to the object with a possible update.

Any assistance, ideas, suggestions would be very much appreciated. I have included my current code that is connected to my update button.

Thank you,

Private Sub cmdAddUpdate_Click()

On Error GoTo Error_Handler

Dim strLANID As String
Dim objXML As Object
Dim strURL As String
Dim objDomDoc As New MSXML2.DOMDocument
Dim varX As Variant
Dim varY As Variant
Dim varZ As Variant
Dim strPicLink As String
Dim strEmpIdCk As String

If Me!txtEE_LANID & "" = "" Then
   MsgBox "You must enter a valid LAN ID to add a new Employee or one must already exist to update a record.", _
    vbOKOnly, "Required Field"
    Exit Sub
End If

strLANID = Me!txtEE_LANID.Value

Set objXML = CreateObject("MSXML2.XMLHTTP")
strURL = "http://hrapps//humanresources/hrorgwebservice/employeelogin.asmx/Search?LanID="

objXML.Open "GET", strURL & strLANID, False
Set objDomDoc = objXML.responseXML

Me!txtEmployeeId = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/IDV").Text
Me!txtEE_POSITION_ID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Position").Text
Me!txtEE_PREFERRED_NM = Replace(objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/PreferredName").Text, " ", "")
Me!txtEE_FIRST_NM = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/FirstName").Text
Me!txtEE_MIDDLE_NM = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/MiddleName").Text
Me!txtEE_LAST_NM = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/LastName").Text
Me!txtEE_NAME_SUFFIX = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/EmployeeSuffix").Text
Me!txtEE_LANID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/LanID").Text
Me!txtEE_EMAIL = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Email").Text
Me!txtEE_JOB_ID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/JobCode").Text
Me!txtEE_JOB_TITLE = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/JobTitle").Text
Me!txtEE_JOB_BAND_ID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/JobBandCode").Text
Me!txtEE_COSTCENTER_ID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/CostCenterNumber").Text
Me!txtEE_LOCATION = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Location").Text
Me!txtEE_AREACODE = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/AreaCode").Text
Me!txtEE_PHONE_NO = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/FullPhone").Text
Me!txtEE_EXTENSTION = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Extension").Text
Me!txtEE_SUPERVISOR = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Supervisor").Text
Me!txtEE_FUNCTIONAL_AREA = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/FunctionalArea").Text
Me!txtEE_DEPT_NM = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/DeptName").Text
Me!txtEE_ROLE = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Role").Text
Me!txtEE_REG_TEMP = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/REG_TEMP").Text
Me!txtEE_FULL_PARTTIME = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/FULL_PARTTIME").Text
Me!txtEE_MAIL_STOP = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/EmployeeMailStop").Text
Me!txtEE_COMPANY = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Company").Text
Me!txtEE_STATUS = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Status").Text
Me!txtMGR_ID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerID").Text
Me!txtMGR_POSITION = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerPosition").Text
Me!txtMGR_MAIL_STOP = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerMailStop").Text
Me!txtMGR_FIRST_NM = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerFirstName").Text
Me!txtMGR_MIDDLE_NM = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerMiddleName").Text
Me!txtMGR_LAST_NM = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerLastName").Text
Me!txtMGR_FULL_NAME = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerFullName").Text
Me!txtMGR_NM_SUFFIX = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerNameSuffix").Text
Me!txtMGR_PHONE_NO = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerFullPhone").Text
Me!txtMGR_PHONE_EXT = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/ManagerPhoneExtension").Text

    strPicLink = "https://hrapps/HumanResources/Directory/WebSite/Photo.ashx?identifier="
    strEmpIdCk = Me!txtEmployeeId
    Me!IMG_EE.Navigate strPicLink + strEmpIdCk
    varX = "about:<html><center><body scroll='no'><img src='" 'Centers Picture
    varY = "</img><p> </center><pre> </pre></body></html>" 'Puts Picture in Center
    varZ = "'width=100% height=100% />"
   'Puts Picture in Web Browser Centered & Turns off ScrollBar & Scales Picture to WebBrowser Size
    Me!IMG_EE.Navigate varX + strPicLink + strEmpIdCk + varZ + varY

    Set objDomDoc = Nothing
    Set objXML = Nothing
    Exit Sub
    DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure

End Sub

Open in new window

Question by:spaced45
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 58
ID: 39716736
Not quite sure I follow, but sure, you can loop through the forms controls collection and look at each control.

An asterick can easily be added to the controls attached label.

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 total points
ID: 39716754
Just to give you an idea what that looks like, here's a routine that flags required fields by changing the background color.  There's also a routine to flag the caption.


Function FlagRequiredFields(frm As Form)

        ' High lite all required fields.
        ' Assumes form already opened when called.

        Const RoutineName = "FlagRequiredFields"
        Const Version = "1.0.0"

        Dim ctrl As Control
        Dim rs As DAO.Recordset       'Recordset of the form.
        Dim strField As String        'Name of the field a control is bound to.

10      On Error Resume Next

20      Set rs = frm.RecordsetClone

30      For Each ctrl In frm.Controls
          ' Don't do any control execpt those in the detail section
40        If ctrl.Section = acDetail Then
50          With ctrl
              ' Set the background color
60            Select Case .ControlType
                Case acTextBox, acComboBox, acListBox
                    ' Assign background color if required.
                    ' Ignore unbound, or bound to an expression.
70                  strField = ctrl.ControlSource
80                  If (strField <> vbNullString) And Not (strField Like "=*") Then
90                    With rs(strField)
100                     If (.Required) Or (.ValidationRule Like "*Is Not Null*") Then
110                       ctrl.BackColor = &H99CCFF
                          ' JRD *** Make this a app configurration item at some point
                          'Option should be to:
                          '1. High lite only
                          '2. Flag caption only
                          '3. Do both
                          'Call MarkAttachedLabel(ctrl)
120                     End If
130                   End With
140                 End If
150           End Select
160         End With
170       End If
180     Next ctrl

190     Set ctrl = Nothing
200     Set rs = Nothing

End Function

Private Function MarkAttachedLabel(ctl As Access.Control)

      'Author:        Allen Browne    allen@allenbrowne.com
      'Version:       21 September 2008.
      'Copyright:     None. You can use this in your database for any purpose.
      '               We request that you acknowledge the source in your code.
      'Documentation: http://allenbrowne.com

10    On Error GoTo Err_Handler
          'Purpose:   Bypass the error if a control has no attached label.
20        With ctl.Controls(0)
30            If Not .Caption Like "*[*]" Then
40                .Caption = .Caption & "*"
50                .FontBold = True
60            End If
70        End With

80        Exit Function

90        Resume Exit_Handler
End Function
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 39716759
I don't quite follow what you're asking. Looks like you're retrieving data from a webservice based on the "txtEE_LANID" value, and you then want to update the local table based on the return from that service? And you want to alert the user if there is an update?

How do you know there is an update? For example, do you only want to modify data if it's changed - that is, assume that POSITION_ID is currently = 2, but when you query the web service it comes back as 3. Do you then want to somehow alert the user that a new POSITION_ID is available?

If so, then you could do exactly what you're doing now, but NOT populate the form's textboxes with the data retrieved from the web service. Instead, you could compare that with the data currently on the form, and then alert your user. For example:

If objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Position").Text <> Me!txtEE_POSITION_ID Then
Me!txtEE_POSITION_ID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Position").Text
End If

Open in new window

If you want to alert the user, then you could include a small label with the Asterisk as the caption, and the forecolor set to red, positioned just next to the txtEE_POSITION textbox, and set to Visible = False. You could then do this:

Me.YourLabelName.Visible = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Position").Text <> Me!txtEE_POSITION_ID

Open in new window

Of course, you'd then have to add code to a button, or something, that would allow the user to act on that. If the user accepts the changes, you'd do something like this:

Me!txtEE_POSITION_ID = objDomDoc.selectSingleNode("//NewDataSet/LOOKUP/Position").Text

Open in new window

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 39716783
No you did understand perfectly. I am wondering though how I can add an update button to each field that potentially has an update. See, if you notice the part in my code where the web service is called and then each node value is then assigned to its corresponding textbox object. Well I am thinking I could hide a button next to each object and then on the OnCurrent event place some code that will cycle through the objecgs, check the values and where they dont match unhide an update button. So I'm looking for an efficient way to cycle through the objects and their values. Makes sense? what you have provided is very useful so I will try it out anyways.

Author Comment

ID: 39716791
hey sorry I didnt notice the last part of your post. I think u answered my question. give me just a bit to test it out.

Author Closing Comment

ID: 39723234
Both worked like a charm. Thank you.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question