Assign custom function to field in report (openrecordset problem?)

[Sendeman]

Hi All,

I'm trying to assign a value returned by a custom vba function to a textbox on a report. The function uses two parameters that are passes from the call in the textbox. I tried setting the textbox value to:

=Bedrag(0, "Deelgebied_1")

or something similar. I have around forty different combinations for these parameters (different integers, n.g. 1,"Deelgebied_2"; 1,"Deelgebied_3"; 10, "Deelgebied_1"; etc.).

This is the code in the function:

Public Function Bedrag(i As Integer, strDeelgebied As String)
Dim strSQL As String, intJaar As Integer, rstResult As Recordset

intJaar = Year(Date)

'Create a recordset of the costs for the current year
strSQL = "SELECT tblVerkeersborden.Deelgebied, Sum(tblVerkeersborden.Vervangingsprijs) AS KostenJaar" & Chr(10) & _
         "FROM tblVerkeersborden" & Chr(10) & _
         "WHERE tblVerkeersborden.Vervangingsdatum>#12/31/" & intJaar + (i - 1) & "# And tblVerkeersborden.Vervangingsdatum<#1/1/" & intJaar + (i + 1) & "#" & Chr(10) & _
         "GROUP BY tblVerkeersborden.Deelgebied" & Chr(10) & _
         "HAVING tblVerkeersborden.Deelgebied = " & strDeelgebied & ";"
Set rstResult = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Return the results from the Recordset to the textfield in the report
With rstResult
    If .BOF Then 'No records were found for this year
        Bedrag = 0
    Else 'Records were found for this year
        Bedrag = .Fields("KostenJaar").value
    End If
End With

End Function

The fieldnames are in Dutch, so they probably won't mean a lot to you.

The problem is, I don't get any values returned, but an #error value in the textbox. I think something goes wrong with opening the recordset, because if I put a msgbox before the line:

Set rstResult = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

the messagebox pops up. But if I put the msgbox line after the codeline mentioned above, I get no msgbox.

What is wrong with my sql/openrecordset statement?

Thanks in advance for any help! Please tell me if I need to further clarify what I'm trying to do.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[danvlas]

Is rstResult a DAO.Recordset or ADODB.Recordset?

You haven't fully qualified it, maybe ADO has a higher priority.




Daniel Vlas
Systems Consultant

 

[Sendeman]

I know as much as you do. I used Dim As Recordset. Shoudl Is use ADO or DAO? I'm not sure what the difference is (no access expert).

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[danvlas]

The syntax you're using suggests DAO.
So try:

Dim rstResult As DAO.Recordset




Daniel Vlas
Systems Consultant

 

[Sendeman]

No, I still get the same error...



I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

By the way, this is the query that is stored in strSQL in one of my test runs:

SELECT tblVerkeersborden.Deelgebied, Sum(tblVerkeersborden.Vervangingsprijs) AS KostenJaar
FROM tblVerkeersborden
WHERE tblVerkeersborden.Vervangingsdatum>#12/31/2009# And tblVerkeersborden.Vervangingsdatum<#1/1/2011#
GROUP BY tblVerkeersborden.Deelgebied
HAVING tblVerkeersborden.Deelgebied = Kern Haastrecht;

Could it be the date values are invalid?

Best regards,
Martijn.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

p.s. I think not, because if I use a different format, Access turns it into this...

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

Any other ideas?

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

Oops solved it. The Having statement was missing quotes. Stupid. Thanks for your feedback though!

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -