Solved

For Next/Do until Loop Again VBA Access

Posted on 2008-06-26
10
2,991 Views
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.
0
Comment
Question by:life42
  • 5
  • 4
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21875233

attaching your db here will be a good start
0
 

Author Comment

by:life42
ID: 21875432
okay:

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
Else
Me("Day" & "InAMTime").Value = 0
  Me("Day" & "OutAMTime").Value = 0
  Me("Day" & "InPMTime").Value = 0
  Me("Day" & "OutPMTime").Value = 0
Next

Open in new window

TimeSheet.zip
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 21875521
First off, you're missing an "End If" between lines 12 and 13...
0
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

by:life42
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
0
 
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
0
 

Author Comment

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

Expert Comment

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

Accepted Solution

by:
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
0
 

Author Comment

by:life42
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
  Next
  'Close recordset
  rs.Close
  Set rs = Nothing
  DoCmd.RunMacro "mcr_runcalchours"
End Sub

Open in new window

0
 

Author Closing Comment

by:life42
ID: 31470977
thank you
0

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - finding skipped numbers 11 23
Question about DB Schema 27 52
Column Layout in Access Xp VBA Report 3 19
Access Changing Number to Date with Seperator 5 21
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

785 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