Go Premium for a chance to win a PS4. Enter to Win


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...
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

885 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