Add new records using vba

[Guest]

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

 

[Guest]

Do While Not rstTemp.EOF

 

[Guest]

Doh - I had dim rstTemp as database instead of recordset ! This sorted it...
well it is Friday afternoon and nearly the weekend

Just hope the code works when I test it now as been trying to find a
solution for this problem for a while now and decided to try creating this
temporary table (rstTemp) to see if this provides a solution.

Thanks and have a good weekend
Sue

Do While Not rstTemp.EOF

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

 

[Guest]

oops! I should have caught that.

Doh - I had dim rstTemp as database instead of recordset ! This sorted it...
well it is Friday afternoon and nearly the weekend

Just hope the code works when I test it now as been trying to find a
solution for this problem for a while now and decided to try creating this
temporary table (rstTemp) to see if this provides a solution.

Thanks and have a good weekend
Sue

Do While Not rstTemp.EOF

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

 

[Bas Cost Budde]

By the way: "while not" equals "until"

Do While Not rstTemp.EOF

 

[Guest]

I don't believe that is correct. The Until will always execute at least one
iteration of the loop. The While will not execute the first loop if the
condition is false.

 

[Marshall Barton]

No, no, Bas is correct.

Do Until False
. . .
Loop

will never execute the code inside the loop.

Perhaps you were thinking of the other form of Do

Do
. . .
Loop Until False

which will always execute the loop's code once.