Function with two recordsets

[c8tz]

HI,
The following is a sub function of the functions used in our research
db. Am encountering an error in my second recordset - called
MARKSTRING.
COUNTSTRING seems to work fine on its own but once markstring is added
- it gives an error in the result.

Need help - my access skills have reached its peaks - the code was set
up by another person who has left.

Could it be possible to break up the code into 3 sep functions? hope
someone can see the loop in this...

Thanks,
CC


***
Function AnnFrondProd(Trial, Plot)
If IsNull(Trial) Or IsNull(Plot) Then
Exit Function
End If
Dim db As Database
Dim rst As Recordset
Dim countstring As String
Dim markstring As String
Dim Countdate As Date, DATE1 As Date, DATE2 As Date
Dim Pos1 As Integer, Pos2 As Integer
Dim cfrondprod As Single
Set db = CurrentDb

countstring = "SELECT TOP 1 FrondCount.CDate, FrondCount.Trial,
FrondCount.Plot, Count(FrondCount.Palm) AS NoOfPalms,
Avg(FrondCount.FrondCount) AS FrondCount, Avg([Pos3]-[pos1]) AS
ProdCount FROM FrondCount GROUP BY FrondCount.CDate, FrondCount.Trial,
FrondCount.Plot HAVING (((FrondCount.Trial) = " & Trial & ") And
((FrondCount.Plot) = " & Plot & ")) ORDER BY FrondCount.CDate DESC;"

Set rst = db.OpenRecordset(countstring)
If rst.BOF Then Exit Function
cfrondprod = rst!prodcount
Countdate = rst!CDate

markstring = "SELECT TOP 3 Date, Colour, Trial, Plot FROM
FrondMarking WHERE Trial = " & Trial & " And Plot = " & Plot & " AND
Date < Datevalue('" & Countdate & "') ORDER BY Date DESC;"

Set db = CurrentDb
'get last 3 marks before last count record
Set rst = db.OpenRecordset(markstring)
If Not rst.BOF Then
rst.MoveFirst
DATE1 = rst!Date
rst.MoveNext
If rst.EOF Then 'Not enough marks
Exit Function
Else
rst.MoveNext
If Not rst.EOF Then
DATE2 = rst!Date
End If
End If
End If
AnnFrondProd = cfrondprod * 365 / (DATE1 - DATE2)
Debug.Print cfrondprod, DATE1; DATE2

End Function

 

[pietlinden]

HI,
The following is a sub function of the functions used in our research
db. Am encountering an error in my second recordset - called
MARKSTRING.
COUNTSTRING seems to work fine on its own but once markstring is added
- it gives an error in the result.

Okay, end the mystery. Exactly what error are you getting? Some error
number? What's the text of the error?

 

[c8tz]

ok - like i said - this function is used in another function which is
used to run a query to give some calculated fields.
The fields dependent to this function just show #Error.

I commented different parts of the function to see exactly where the
error was coming from and it was when the sql string MARKSTRING is
being used. I'm thinking the sql string itself but I tried modifying it
and it seems to work ok...

so am lost! help...

 

[Nick 'The database Guy']

Hi c8,

Can you tell me what the data types of Trial, Plot and Countdate are?

What happens when you set a breakpoint in your code after the line I
have pasted in below, then goto the debug window (Ctrl + g) and type '?
markstring' then paste the sql in to the native sql window of the query
builder and then run that query. If it falls over then you have found
the crux of your problem.

markstring = "SELECT TOP 3 Date, Colour, Trial, Plot FROM FrondMarking
WHERE Trial = " & Trial & " And Plot = " & Plot & " AND Date <
Datevalue('" & Countdate & "') ORDER BY Date DESC;"

Good luck,

Nick

 

[Guest]

If you want to use two recordsets, you need to establish two recordset
objects. In your code, you establish the first recordset, retrieve two
values from the first record, the use the same object reference (rst) to
establish the second recordset. You can use more than one recordset, you just
need two objects:

Dim rstCount as Recordset
Dim rstMark as Recordset

 

[c8tz]

Hi Nick -

I did what you had said to do and I got the following SQL statement
when ?markstring
SELECT TOP 3 Date, Colour, Trial, Plot FROM FrondMarking
I ran the query and it asked for a plot - I put plot in.
When I tried it again - in the immediate window - nothing came up.

I removed Plot from the markstring statement in the code - but it still
gives the error #error.

The data types are :
Trial - Number (byte)
Plot - Number (byte)
Countdate As Date (MediumDate)

still lost!

 

[c8tz]

Hi Nick -

I did what you had said to do and I got the following SQL statement
when ?markstring
SELECT TOP 3 Date, Colour, Trial, Plot FROM FrondMarking
I ran the query and it asked for a plot - I put plot in.
When I tried it again - in the immediate window - nothing came up.

I removed Plot from the markstring statement in the code - but it still
gives the error #error.

The data types are :
Trial - Number (byte)
Plot - Number (byte)
Countdate As Date (MediumDate)

still lost!