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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

830 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