updating a record set with a form

[adj]

Hi,
I am trying to update a record set using a form to enter the new work week.

The code is below. Please take a look and provide somefeed back as to what is wrong with this.
<code>
Private Sub Update_Click()
Dim nd As Date
Dim rd As String
If Forms![Update].Form![Prev_Week] = "" Then
MsgBox "You must select a date for "" Previous Week ""."
If [Next_Date] = "" Then
MsgBox "You must enter a date for ""Next Date""."
If [Next_Week] = "" Then
MsgBox "You must enter a date for ""Next Week""."
End If
Exit Sub
ElseIf Forms![Update].Form![Next_Date] = "" Then
MsgBox "You must enter a date for "" Next_Date""."
If [ Next_Week] = "" Then
MsgBox "You must enter a date for "" Next Week""."
End If
Exit Sub
ElseIf Forms![Update].Form![Next_Week] = "" Then
MsgBox "You must enter a date for "" Next Week""."
Exit Sub
End If

Forms![Update].Form![StatusBox].Visible = True
Forms![Update].Form![StatusBox] = "Updating ....."
Forms![Update].Repaint

'local variables declared here.

Dim mydb As Database
Dim mydata As Recordset
Dim mytable As Recordset


Set mydb = DBEngine.Workspaces(0).Databases(0)

Set mydata = mydb.OpenRecordset("SELECT DISTINCTROW TimeData.Emp,TimeData.Date,timedata.SortOrder,Timedata.jobno,timedata.[Report Date],timedata.actual,timedata.forecast,timedata.Status FROM timedata where ((timedata.[Report Date] = '" & Forms![Update].Form![Prev_Week] & "'))", DB_OPEN_DYNASET)

Set mytable = mydb.OpenRecordset("SELECT * FROM Timedata", DB_OPEN_DYNASET)

'now I am testing to see if there is information in the table.

If mydata.RecordCount > 0 Then
mydata.MoveLast
MsgBox (mydata.EOF)
mydata.MoveFirst
While Not mydata.EOF
If mytable.RecordCount > 0 Then
mytable.MoveFirst
nd = Forms![Update].Form![Next_Date]
rd = Forms![Update].Form![Next_Week]
mytable.FindFirst "EMP = '" & mydata("EMP") & "' & [Date] = #" And Forms![Update].Form![Next_Date] And "# & [Job No] = '" & mydata.[Job No] & "' & [Report date] = '" And Forms![Update].Form![Next Week] & "'"
'***************************
'this is a nested if
If mytable.NoMatch Then
mytable.AddNew
Else
mytable.Edit
End If
'****************************
mytable("EMP") = mydata("EMP") 'set emp
mytable("Date") = Forms![Update].Form![Next_Date] ' set the date
mytable("Job No") = mydata("Job No") 'set job no
mytable("SortOrder") = mydata("SortOrder")
mytable("Actual") = mydata("Actual")
mytable("Forecast") = mydata("Forecast")
mytable("Status") = "New"
mytable("Report Date") = Forms![Update].Form![Next_Week]
mytable.Update
End If
mydata.MoveNext
Wend

End If #!--- This is where the problems appear to end.

Forms![Update].Form![Prev_Week] = ""
Forms![Update].Form![Next_Date] = ""
Forms![Update].Form![Next_Week] = ""

Forms![Update].Form![Prev_Week].Requery

Forms![Update].Form![StatusBox] = "Update Complete"

For n = 1 To 1000
Next n
Forms![Update].Form![StatusBox] = ""
Forms![Update].Form![StatusBox].Visible = False

End If


End Sub
</code>