H
hughess7
Hi all
I am trying to loop through a recordset and create new records for
additional dates based on the value in ReviewDays field (if it is greater
than 1).
At the moment my code looks like below but I get an error on compile -
'method or data member not found' on the EOF statement. Please can someone
let me know the correct code for this.
Thanks in advance for any help.
Sue
Set DB = CurrentDb
Set rst = DB.OpenRecordset("Select [ReviewDate] and [Specialist] from
Itinerary")
Set rstTemp = DB.OpenRecordset("Select * from [Itinerary Report] where
[ReviewDays]>1")
Do While rstTemp.EOF = False
rstTemp.MoveFirst
intDays = rstTemp!ReviewDays
CheckDate = rstTemp!ReviewDate
SpecID = rstTemp!Specialist
Do While intDays > 1
CheckDate = CheckDate + 1
intDays = intDays - 1
rst.FindFirst "[ReviewDate] = #" & Format$(CheckDate,
"mm\/dd\/yyyy") & "#" & "And [Specialist] = " & Format(SpecID, "0")
If Weekday(CheckDate) <> vbSunday And Weekday(CheckDate) <>
vbSaturday Then
If rst.NoMatch Then
rstTemp.AddNew
rstTemp![ReviewDate] = CheckDate
rstTemp![CountryCode] = rst![CountryCode]
rstTemp![Specialist] = rst![Specialist]
rstTemp![Activity] = rst![Activity]
rstTemp![DealerCode] = rst![DealerCode]
rstTemp![ItineraryComments] = rst![ItineraryComments]
rstTemp![AuditNo] = rst![AuditNo]
rstTemp![ReviewDays] = 1
rstTemp.Update
End If
End If
Loop
rst.MoveNext
Loop
I am trying to loop through a recordset and create new records for
additional dates based on the value in ReviewDays field (if it is greater
than 1).
At the moment my code looks like below but I get an error on compile -
'method or data member not found' on the EOF statement. Please can someone
let me know the correct code for this.
Thanks in advance for any help.
Sue
Set DB = CurrentDb
Set rst = DB.OpenRecordset("Select [ReviewDate] and [Specialist] from
Itinerary")
Set rstTemp = DB.OpenRecordset("Select * from [Itinerary Report] where
[ReviewDays]>1")
Do While rstTemp.EOF = False
rstTemp.MoveFirst
intDays = rstTemp!ReviewDays
CheckDate = rstTemp!ReviewDate
SpecID = rstTemp!Specialist
Do While intDays > 1
CheckDate = CheckDate + 1
intDays = intDays - 1
rst.FindFirst "[ReviewDate] = #" & Format$(CheckDate,
"mm\/dd\/yyyy") & "#" & "And [Specialist] = " & Format(SpecID, "0")
If Weekday(CheckDate) <> vbSunday And Weekday(CheckDate) <>
vbSaturday Then
If rst.NoMatch Then
rstTemp.AddNew
rstTemp![ReviewDate] = CheckDate
rstTemp![CountryCode] = rst![CountryCode]
rstTemp![Specialist] = rst![Specialist]
rstTemp![Activity] = rst![Activity]
rstTemp![DealerCode] = rst![DealerCode]
rstTemp![ItineraryComments] = rst![ItineraryComments]
rstTemp![AuditNo] = rst![AuditNo]
rstTemp![ReviewDays] = 1
rstTemp.Update
End If
End If
Loop
rst.MoveNext
Loop