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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


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

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)

Question has a verified solution.

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

Suggested Solutions

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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

21 Experts available now in Live!

Get 1:1 Help Now