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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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...
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


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

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.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

751 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