Add new records using vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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


Klatuu said:
Do While Not rstTemp.EOF

hughess7 said:
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
Click to expand...
Click to expand...
 
oops! I should have caught that.

hughess7 said:
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


Klatuu said:
Do While Not rstTemp.EOF

hughess7 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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.
 
Back
Top