Results 1 to 6 of 6

Thread: Loop help

  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: Loop help

    I have a macro I am making to check a field in a table that has a date and then outputs the number of days from that date etc. Well the loop that goes through each record is only going through one record and none of the others. If you could please look over my code that would be great, I have been working on this for a couple days now.



    Function ContractCheck()

    On Error GoTo Errors:

    Dim NowDate As Date
    Dim DateEnd As Date
    Dim TimeLeft As Single
    Dim db As Database
    Dim Rec As Recordset
    Dim I As Integer

    Set db = CurrentDb()
    Set Rec = db.OpenRecordset("Contacts")


    NowDate = DateTime.Date
    DateEnd = Nz(Rec("End_Date"))
    Rec.MoveFirst

    Do While Not Rec.EOF



    TimeLeft = (DateEnd) - (NowDate)

    If TimeLeft <= 90 Then

    Dim Msg As String

    Msg = MsgBox("Good Job Billy" & vbNewLine & TimeLeft, vbOKOnly, "Great Guy")

    End If

    Rec.MoveNext

    Loop


    Errors:
    If Err Then


    MsgBox Err.Description, vbOKOnly, "Error"

    End If


    End Function










    Thank you,

    Billy

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have you checked to make sure it's loading the record set properly?

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    How many times do you get the messagebox popping up? Any?

  4. #4
    Join Date
    Oct 2003
    Posts
    43

    Msg Box

    The Msg Box pops up 5 times due to their are 5 records in the table.



    Billy

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't know exactly what you're trying to do here, but DateEnd and NowDate are never going to be set after the first time. They're not included in the while loop.
    Last edited by Teddy; 01-09-04 at 17:25.

  6. #6
    Join Date
    Nov 2002
    Location
    Wyoming
    Posts
    48

    Re: Loop help

    Originally posted by bwood415
    I have a macro I am making to check a field in a table that has a date and then outputs the number of days from that date etc. Well the loop that goes through each record is only going through one record and none of the others. If you could please look over my code that would be great, I have been working on this for a couple days now.



    Function ContractCheck()

    On Error GoTo Errors:

    Dim NowDate As Date
    Dim DateEnd As Date
    Dim TimeLeft As Single
    Dim db As Database
    Dim Rec As Recordset
    Dim I As Integer

    Set db = CurrentDb()
    Set Rec = db.OpenRecordset("Contacts")


    NowDate = DateTime.Date
    DateEnd = Nz(Rec("End_Date"))
    Rec.MoveFirst

    Do While Not Rec.EOF



    TimeLeft = (DateEnd) - (NowDate)

    If TimeLeft <= 90 Then

    Dim Msg As String

    Msg = MsgBox("Good Job Billy" & vbNewLine & TimeLeft, vbOKOnly, "Great Guy")

    End If

    Rec.MoveNext

    Loop


    Errors:
    If Err Then


    MsgBox Err.Description, vbOKOnly, "Error"

    End If


    End Function










    Thank you,

    Billy
    Change this:

    TimeLeft = (DateEnd) - (NowDate)

    To:

    TimeLeft = Rec!End_Date - NowDate
    Cheers,
    Ken

Posting Permissions

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