Solved

Loop Through Objects in Access Database Using VBA

Posted on 2013-12-13
6
1,101 Views
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"
            Me!txtEE_LANID.SetFocus
    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
objXML.Send
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


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

End Sub

Open in new window

0
Comment
Question by:spaced45
  • 3
  • 2
6 Comments
 
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.

Jim.
0
 
LVL 57

Accepted Solution

by:
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.

Jim.

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

Exit_Handler:
80        Exit Function

Err_Handler:
90        Resume Exit_Handler
End Function
0
 
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

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:spaced45
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.
0
 
LVL 1

Author Comment

by:spaced45
ID: 39716791
lsmconsulting,
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.
0
 
LVL 1

Author Closing Comment

by:spaced45
ID: 39723234
Both worked like a charm. Thank you.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
My experience with Windows 10 over a one year period and suggestions for smooth operation
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now