Help with Recordsets

[LeftyLeo via AccessMonster.com]

I have the following code and what I am trying to do is go through each
record in rec determine the OpenDate and based on whether it is > or = to
Period 10 run and one insert code else a different insert code. I know I
have to move to the next record some where but not sure where. Not sure how
to do this, any help would be appreciated.

Private Sub cmdNewGasStations_Click()
Dim rec As ADODB.Recordset
Dim rec2 As ADODB.Recordset
Dim strYear
Dim strPeriod

Set rec = New ADODB.Recordset
Set rec2 = New ADODB.Recordset
rec.Open "SELECT tblGasStations.OpenDate, tblGasStations.fldOpen FROM
tblGasStations WHERE (((tblGasStations.OpenDate) Is Not Null) AND (
(tblGasStations.fldOpen)=No));", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
While rec.EOF
rec.MoveFirst
If Right(rec("OpenDate"), 2) < "10" Then
strYear = Left(rec("OpenDate"), 4) - 1
strPeriod = Right(rec("OpenDate"), 2)
rec2.Open "INSERT INTO tblFinalCopy ( Location, [Date], Costs, Acct )
SELECT tblGasStations.Location, tblFinalCopy.Date, [Costs]*0.03 AS GasCosts,
'GasStation' AS Expr1 FROM tblGasStations INNER JOIN tblFinalCopy ON
tblGasStations.Location = tblFinalCopy.Location WHERE (((tblFinalCopy.Date)

=strYear & strPeriod) AND ((tblGasStations.OpenDate) Is Not Null) AND (

(tblGasStations.fldOpen)=No) AND ((tblFinalCopy.Acct)='Elec'));",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
rec2.Close
Else
strYear = Left(rec("openDate"), 4) - 2
strPeriod = "01"
rec2.Open "INSERT INTO tblFinalCopy ( Location, [Date], Costs, Acct )
SELECT tblGasStations.Location, tblFinalCopy.Date, [Costs]*0.03 AS GasCosts,
'GasStation' AS Expr1 FROM tblGasStations INNER JOIN tblFinalCopy ON
tblGasStations.Location = tblFinalCopy.Location WHERE (((tblFinalCopy.Date)

=strYear & Right([OpenDate],2) And (tblFinalCopy.Date)< me.txtYear &

strPeriod) AND ((tblGasStations.OpenDate) Is Not Null) AND ((tblGasStations.
Open)=No) AND ((tblFinalCopy.Acct)='Elec'));", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
rec2.Close
End If
Wend

End Sub

 

[Steve Sanford]

I would add it here

End If
rec.MoveNext

Wend

End Sub

You'll also need to change these lines:

While rec.EOF
rec.MoveFirst

It should be:


rec.MoveFirst
While NOT rec.EOF
IF .......

 

[LeftyLeo via AccessMonster.com]

I would add it here

End If
rec.MoveNext

Wend

End Sub

You'll also need to change these lines:

While rec.EOF
rec.MoveFirst

It should be:

rec.MoveFirst
While NOT rec.EOF
IF .......
.
' commands
.
END IF

rec.MoveNext

WEND
END SUB

One other point. "Date" is a reserved word in Access and shouldn't be used
as a name for objects. Also, it does not describe the object - date of what???

See http://www.allenbrowne.com/AppIssueBadWord.html

HTH

I have the following code and what I am trying to do is go through each
record in rec determine the OpenDate and based on whether it is > or = to

[quoted text clipped - 43 lines]

End Sub

Yes I discovered the Not needing to be added to the while statement. I
actually scrapped all this code and created 2 queries instead. Thank you
very much for your insights. And Yes I changed the Date in the tables to
fldDate due to it causing problems with my other code.

Anyway thanks again.