Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good Afternoon,

    I am in need of some assistance trying to understand the DO/WITH loop function. I have a table that we use to track our employee's appointments. We use a form to fill in the information in the table and we have another form, designed to look like a calender which displays each of these appointments. So a supervisor can quickly look at this calender and see which of his employees have appointments each day. The issue is that we have to fill out the form for each day. When an employee takes, lets say 6 days off for vacation we have to fill out 6 forms. We would like to use a start date and end date and use the DO/WITH Loop function to fill in the dates inbetween.

    So 1) is this possible?
    And 2) would someone be able to guide me on how to write the loop?

    Thanks in advance
    Rob

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    I'm sure we'll be able to help you, but we need a bit more information. It would be helpful to know, for example, what code is currently used (if any) to add a single record. We could then adapt this code for a range of dates.

  3. #3
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='771811' date='22-Apr-2009 14:13']Welcome to the Lounge!

    I'm sure we'll be able to help you, but we need a bit more information. It would be helpful to know, for example, what code is currently used (if any) to add a single record. We could then adapt this code for a range of dates.[/quote]


    Well we created a form based on the table (Orders). In this form we have EmployeeName (combo box), Shop (combo box), Event (combo box), Start Date (text box which we use the Active X Calender Control 12.0), start time (combo box), End Date (text box which we use the Active X Calender Control 12.0), End Time (combo box), and Remarks (text box we use to put notes in to the employee about his appointment). Once we fill in the info we use a command button to "save" the record and go to the next one. Does this give you the info you are asking about? If you could tell me where to go to get the code for this form I will be glad to copy/paste it. I just don't know where to look.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you post a stripped down and zipped copy of your database?
    • Make a copy of the database and work with that.
    • Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    • In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    • Remove or modify data of a confidential nature.
    • Perform a compact and repair (Tools/Database Utilities).
    • Make a zip file containing the database.
    • If you have difficulties getting the zip file reasonably small, save the database in Access 97 format and then zip it. (Of course, this only helps if you're using Access 2000 or later.)
    • Attach the zip file to a reply.

  5. #5
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    Welcome to the United States Air Force. I have no zip capabilities unfortunately. Is there another way to get you the info?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Windows XP and Vista have zip capability built in: right-click a file and select Send To | Compressed (zipped) folder from the popup menu.

  7. #7
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Learn something new everyday. Here you go.[attachment=83433:Calendar...Copy__2_.zip]
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I've attached a modified version of your database.

    The essential change is in the LoadData procedure. I changed

    strLst = "lstDay" & Trim$((rst!OrderDate - Me!txtDay1) + 1)

    to

    For d = rst!OrderDate To rst!ShippedDate
    strLst = "lstDay" & Trim$((d - Me!txtDay1) + 1)
    ...
    ...
    Next d

    This loops through the date range.

    (Next time, please compact the database before zipping it - the size went down from more than 3,500 KB to 384 KB.
    Also, I removed lots of unused references that may cause the code to fail on other computers)
    Attached Files Attached Files

  9. #9
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This looks great. I really appreciate the help.

    Rob

  10. #10
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One more thing. I noticed that if we put in an end date which is more then 30 days from start date we get a runtime error '2465' - Microsoft Office Access can not find the field '1stday36' referred to in your expression.

    Very seldom do we have events longer then 30 days however I'm afraid one of the supervisors may fat finger the input and go over the 30 days inadvertently. Is there a way to increase the number of allowed days or what do you suggest would be an easy way to prevent this occurance?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'll think about that, but it'll have to be later today...

  12. #12
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No Rush Hans. I appreciate what you have done.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This version of the LoadData procedure should be able to handle "events" lasting more than 30 days.

    [codebox]Private Sub LoadData()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varItem As Variant
    Dim d As Date
    Dim dStart As Date
    Dim dEnd As Date

    strQRY = "qryOrders"
    strSQL = "SELECT * FROM " & strQRY & " " & _
    "WHERE (([OrderDate] <= #" & Format(Me!txtDay35, "mm/dd/yyyy") & _
    "#) and ([ShippedDate] >= #" & Format(Me!txtDay1, "mm/dd/yyyy") & "#)) " & _
    "ORDER BY OrderID, CustomerID ;"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL)

    If rst.RecordCount = 0 Then
    For i = 1 To 35
    With Me("lstDay" & Trim$(i))
    .Value = ""
    .RowSource = ""
    .OnDblClick = ""
    .ColumnCount = 1
    .ColumnWidths = ""
    .Visible = False
    End With
    Next i
    rst.Close
    Set db = Nothing
    Set rst = Nothing
    Exit Sub
    End If

    Application.Echo False
    For i = 1 To 35
    With Me("lstDay" & Trim$(i))
    .Value = ""
    .RowSource = ""
    .OnDblClick = "=ViewOrder()"
    .ColumnCount = 4
    .ColumnWidths = Int(.Width / 4) - 500 & ";" & Int(.Width / 4) - 60 & _
    ";" & Int(.Width / 4) - 1 & ";" & Int(.Width / 4) - 250
    .Visible = True
    End With
    Next i

    rst.MoveFirst
    Do While Not rst.EOF
    strSource = ""
    strSource = strSource & rst!OrderID & ";"
    strSource = strSource & rst!Shop & ";"
    strSource = strSource & rst!Event & ";"
    strSource = strSource & rst!CustomerID & ";"

    dStart = rst!OrderDate
    If dStart < Me!txtDay1 Then
    dStart = Me!txtDay1
    End If
    dEnd = rst!ShippedDate
    If dEnd > Me!txtDay35 Then
    dEnd = Me!txtDay35
    End If

    For d = dStart To dEnd
    strLst = "lstDay" & Trim$((d - Me!txtDay1) + 1)

    If Len(Me(strLst).RowSource) = 0 Then
    Me(strLst).RowSource = strSource
    Else
    Me(strLst).RowSource = Me(strLst).RowSource & strSource
    End If
    Next d
    rst.MoveNext
    Loop

    rst.Close
    Set db = Nothing
    Set rst = Nothing

    Application.Echo True
    End Sub[/codebox]

  14. #14
    New Lounger
    Join Date
    Apr 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, It works like a charm.

    Thanks again for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •