Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


For Next/Do until Loop Again VBA Access

Posted on 2008-06-26
Medium Priority
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:Jamie Poole
  • 5
  • 4
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21875233

attaching your db here will be a good start

Author Comment

by:Jamie Poole
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...
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


Author Comment

by:Jamie Poole
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Author Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21876403
just get part of the tbl_TimesheetData
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 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

by:Jamie Poole
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

by:Jamie Poole
ID: 31470977
thank you

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

580 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