For Next/Do until Loop Again VBA Access

Posted on 2008-06-26
Last Modified: 2013-11-28
When I went over what my manager wanted I discovered I was wrong in what I wanted to ask so I will try again,
IWhat I need is a Do Until For Next Loop that goes until Day14 is reached
Read the tbl_EmployeeData match the employeenumber on the form to the employee number in that table, see if the checkbox value in the table is checked(the checkbox determines what day of the week is populated with the times worked) there are 14 of these if the checkbox is checked then populate the InAmtime, OutAMTime, InPMTime, OutPmTime with the time stored in the table.
I know there is a way to do this but I am not sure how to start it.

Thanks for any help.
Question by:life42
  • 5
  • 4
LVL 119

Expert Comment

by:Rey Obrero
ID: 21875233

attaching your db here will be a good start

Author Comment

ID: 21875432

Here is the code I came up with I am not sure if it will populate all 14 days Day_InAMTim, Day_OutAMTime, Day_InPMTime, Day_OutPMTime fields,

Dim x As Integer

For x = 1 to 14

If Me.EmployeeNo = tbl_EmployeeData.Employee# AND tbl_EmployeeData.WkDay_(here I need a way to check 14 check boxes without writing a separte piece of code for each checkbox, I think it should be & Format(x, "00") = True(checked) Then 

  Me("Day" & "InAMTime").Value = tbl_EmployeeData.InAmTime

  Me("Day" & "OutAMTime").Value = tbl_EmployeeData.OutAmTime

  Me("Day" & "InPMTime").Value = tbl_EmployeeData.InPMTime

  Me("Day" & "OutPMTime").Value = tbl_EmployeeData.OutPMTime


Me("Day" & "InAMTime").Value = 0

  Me("Day" & "OutAMTime").Value = 0

  Me("Day" & "InPMTime").Value = 0

  Me("Day" & "OutPMTime").Value = 0


Open in new window
LVL 12

Expert Comment

ID: 21875521
First off, you're missing an "End If" between lines 12 and 13...

Author Comment

ID: 21875542
I've added that, I am getting an error that it can not find the field in this line of code:
If Me.EmployeeNo = [Table].tbl_EmployeeData.Employee# And [Table].tbl_EmployeeData.WKDay & Format(x, "00") = True Then
Any idea why
LVL 119

Expert Comment

by:Rey Obrero
ID: 21876169
hmm you are in for a tough coding here..

life42, add the table  tbl_TimesheetData  ( not the linked table ) and attach the db again
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


Author Comment

ID: 21876214
I can not attach the db it is to large even when zipped.
Anyway to help without it.
LVL 119

Expert Comment

by:Rey Obrero
ID: 21876403
just get part of the tbl_TimesheetData
LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 21876411
do a compact and repair     - this will reduce the size of the db
tools > database utilities> compact and repair

Author Comment

ID: 21877544
This is what we came up at break, I think it should work

Private Sub PayPeriod_AfterUpdate()

  Dim rs As Recordset

  Dim strSQL As String

  Dim i As Integer

  Dim strDayNum As String

  Dim strDayAMIn As String

  Dim strDayPMIn As String

  Dim strDayAMOut As String

  Dim strDayPMOut As String

  Dim strFldWorkdayWorked As String

  Dim strTemp As String


  'Query the employee table for the employee in question


  strSQL = "Select * from tbl_EmployeeData WHERE [Employee#]='" & EmployeeNo & "'"


  Set rs = CurrentDb.OpenRecordset(strSQL)

  'For each day, check if scheduled to work

  For i = 1 To 14

    strTemp = "00" & Trim(Str(i))

    strDayNum = Right(strTemp, 2)

  '     if scheduled, then fill in default hours from record

    strFldWorkdayWorked = "WKDay" & strDayNum


    If rs.Fields(strFldWorkdayWorked) Then

      strDayAMIn = "Day" & strDayNum & "InAM"

      strDayPMIn = "Day" & strDayNum & "InPM"

      strDayAMOut = "Day" & strDayNum & "OutAM"

      strDayPMOut = "Day" & strDayNum & "OutPM"

      Me.Controls(strDayAMIn).Value = rs!InAmTime

      Me.Controls(strDayPMIn).Value = rs!InPmTime

      Me.Controls(strDayAMOut).Value = rs!OutAmTime

      Me.Controls(strDayPMOut).Value = rs!OutPmTime

    End If


  'Close recordset


  Set rs = Nothing

  DoCmd.RunMacro "mcr_runcalchours"

End Sub

Open in new window


Author Closing Comment

ID: 31470977
thank you

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

20 Experts available now in Live!

Get 1:1 Help Now