Loop Through Objects in Access Database Using VBA

Posted on 2013-12-13
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 57
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 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 250 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
      '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.

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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

752 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