Looping Through Query and Updating Form Field

ghmartinezjr used Ask the Experts™
I have a main form for Staff displaying all personal data. The underlying table for the main form is 'tbl_Staff'. I created a query, 'qry_Staff_TotalFTE', that summarizes data from a second table. I wanted to loop through the query results and update the TotalFTE  in 'tbl_Staff' with the 'TotalFTE' from the query  'qry_Staff_TotalFTE' where the UTIDs match.

table 'tbl_Staff'
UTID         TotalFTE

query 'qry_Staff_TotalFTE'
UTID         TotalFTE
256315     1.0
125435      .75
124415      .5

I want to create a button on the main form, 'Update All FTEs", so when pressed it will loop through the query and at the same time give a visual of where the process is. For example a text box on the main form that shows what UTID is being processed at the time. I put the text on the form already, named "txtLoopingUTID".

I hope this makes sense.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

You need to write code to iterate through a DAO recordset based on the query, that would write a message "Processing UTID n" to a textbox, or better still create a text file as a record of progress, which you can have as a record.  Here is an example of writing lines to a text file, using the Scripting Library Textstream object (you need a reference to the Scripting Runtime LIbrary):
Public Sub GetFolderList(dteModified As Date)
   Dim strCurrentPath As String
   Dim fso As New Scripting.FileSystemObject
   Dim fld As Scripting.Folder
   Dim fldRoot As Scripting.Folder
   Dim ts As Scripting.TextStream
   Dim strTextFile As String
   strTextFile = "Folder Names.txt"
   strCurrentPath = Application.CurrentProject.Path
   strTextFile = strCurrentPath & "\" & strTextFile
   Set fldRoot = fso.GetFolder(strCurrentPath)
   Set ts = fso.OpenTextFile(FileName:=strTextFile, _
      IOMode:=ForWriting, _
   For Each fld In fldRoot.SubFolders
      If fld.DateLastModified >= dteModified Then
         ts.WriteLine fld.Name
      End If
   Next fld
End Sub

Open in new window


Helen Feddema:

What you mentioned here is exactly what I want do:
"You need to write code to iterate through a DAO recordset based on the query, that would write a message "Processing UTID n" to a textbox".

That's the part I don't know how to do. Do you have sample code based on my table and query names?


If I understand you correctly, you are looking for a status bar.  I have a "StatusBar" form that I open whenever I have a measure-able process running.

Basically it is nothing more than a small form with a few controls on it.  The first label lets me put a description (e.g. "Generating Report") The rectangle acts as the progress bar.  It's blue, and I increase its width as the code progresses.  The second label lets me update the status. (e.g. "90% Complete")  I have attached the form so that you can import it into your database.

If I wanted to open the status bar in my form's Load event, it would look something like this:
Private Sub Form_Load()
        'Open the status bar
        If Not IsFormOpen("frmStatusBar") Then DoCmd.OpenForm "frmStatusBar", acNormal
        Forms![frmStatusBar].boxbar.Width = 0
        Forms![frmStatusBar].lblDesc.Caption = "Generating Report"
       '###Begin executing code (for me this is usually the start of a loop)
       'Update Status Bar (for me this is usually at the end of the loop)
       Forms![frmStatusBar].boxbar.Width = (Forms![frmStatusBar].boxMain.Width / iNumRecords) * iRecordIndex
       Forms![frmStatusBar].Label7.Caption = Round(((iRecordIndex / iNumRecords) * 100), 0) & " Percent Complete"
       '###Code finished executing
       'Close the status bar
       If IsFormOpen("frmStatusBar") Then DoCmd.Close acForm, "frmStatusBar"
End Sub
'Determine whether or not a form is already open
Public Function IsFormOpen(sFormName As String) As Boolean
    On Error GoTo Error_Handler
    Dim frmCurrent As Form
    For Each frmCurrent In Forms
        If UCase(frmCurrent.Name) = UCase(sFormName) Then
            IsFormOpen = True
            Exit Function
        End If
    Exit Function
    IsFormOpen = False
End Function

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!


Any help with part of the question. This is most important to work through: ' I wanted to loop through the query results and update the TotalFTE  in 'tbl_Staff' with the 'TotalFTE' from the query  'qry_Staff_TotalFTE' where the UTIDs match."
Just FYI,  While this sort of scenario can be useful for processes that take a considerable amount of time they are pretty much useless for quick processes.  Ie they popup and are gone before you even get to read the title.   In this situation it looks more like a bug in your system than a useful progress notification tool.  It will also slow the process down, especially if you have to perform additional processes to determin how long the main process is going to take.

I am not saying that tools like this don't have their place, Just that you should consider the ups / downs.  Quite often a simple message box "Processing" will suffice.

I only mention this because from the idea I've got in my head the process you are trying to run would be over and done with very quickly especially since I would assume it will be run on a regular basis.  (I may be wrong)



Thanks, I'll try a different route.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial