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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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...
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


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 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

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 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

830 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