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 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 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 85

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now


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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month4 days, 23 hours left to enroll

635 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