Recordset Report Problems

[Chinde]

I've googled this and I've come up with nothing.

I'm using the code below to produce a recordset for a report. all seems to work just fine except when i try to print I get the error:

"A custom macro in this report has failed to run, and is preventing the report from rendering"

Option Compare Database
Option Explicit

Dim rsOne As Recordset

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Me.txtFullName.Value = rsOne("FirstName") & " " & rsOne("LastName")

    rsOne.MoveNext

End Sub

Private Sub Report_Open(Cancel As Integer)

    Dim SQL As String
    
    SQL = "SELECT * FROM ReminderFinal"
    
    Set rsOne = CurrentDb.OpenRecordset(SQL)
    
    rsOne.MoveFirst
        
End Sub

I've traced the problem to .MoveNext in the Sub Detail_Format, but when I comment it out my report no longer steps through all of the records in the record set.

I'm new to using VBA for reports so (again) I'm happy to believe that I am not understanding some kind of fundamental logic for Recordsets and reports.

Using Access2007, XpOS.

[ChipR]

Is your report itself based on ReminderFinal? If not, base it on a query that includes the name information, rather than using that code.

Then, in the txtFullName control on the report in design view put:
=[FirstName] & " " & [LastName]

[Stewart Ross]

Hi Chinde. The custom recordset you are opening is nothing to do with the report. It is not the same recordset as the report itself is opening, which is defined by the recordsource of the report itself.

As the report is based on a specific recordsource, and you are trying to fill values of controls with values drawn from an unrelated recordset, you may be trying to set a value for a bound control - one that is bound to a field in the underlying recordset. This is certain to fail.

To set up custom names and so on it is far better to include these as calculated fields within the underlying query used as the recordsource of your report. If you do this you will have no need for custom coding the way you are doing.

If, for some reason, you don't want to include the custom fields in your underlying query you can set an unbound textbox to be firstname concatenated with lastname, but you'd have to place the two named fields onto your report and then set their visible properties to false to hide them first.

My feeling is that your current approach is not going to lead you anywhere except to frustration, as there is no chance that a recordset opened from within a report will have any bearing on what your report is really working on - these are completely different things.

If you could tell us what you are trying to achieve I'm sure we could advise you how to do it without coding at all if possible...

-Stewart

ps apologies to Chip for cross-posting - didn't see yours when I was preparing mine.

[Chinde]

ChipR
It is based on the entire contents of the table ReminderFinal, the method you outlined is more or less the method I wished to avoid, as I really wanted to manipulate the data dynamically via the VBA. I guess that I'm just not as comfortable with the expression builder syntax. I also wanted to pull other bits of info in from other related tables.

[ChipR]

You can set the value of a control to a function, if that makes it any easier, but I would base the report on a query that has all the data you need in it.

=GetFullName([FirstName],[LastName])


Function GetFullName (FirstName As String, LastName As String) As String
   GetFullName = FirstName & " " & LastName
End Function

Will do the same thing, but you can see that your function could be as complicated as you like.

[Chinde]

Thanks both.

I will follow what both of you have advised and return to the more straight forwards method using the reports native recordsource. Though I am sure I will come to a dead end with my knowledge in that direction.

I'll be back.

[Stewart Ross]

Hi Chinde. I'm still not sure what you would hope to gain by code-based manipulation - but when you mention taking data from other tables this really is much, much easier to do by joining the tables into the query on which the report is based. It is also much easier for others to maintain - code-based solutions can be hard for others to understand, never mind to maintain, whereas queries are much more straightforward .

An informal rule in using Access is to avoid trying to develop bespoke coding for things which can be done using native Access features with little effort. You will not replicate the product's overall ease of use, years of development and general efficiency at doing what it does if you go that route.

I use minimal bespoke coding in reports to do things which cannot be easily achieved in other ways - for example bespoke formatting of particular fields (emboldening, changing font characteristics , and so on), or setting particular values visible dynamically.

In many years of report development in Access I have never had any occasion to use an internal recordset variable or to loop through any records other than those the report was already working on. All the main 'work' in my reports is contained in the (sometimes complex) underlying queries used as the report's recordsource - not in the report itself.

I strongly advise using the facilities already there instead of trying to replicate them through difficult-to-verify code - doing so is not a good use of your time nor that of any developer who takes over such a database from you. I know that you have said you will now go this route, but it clearly is with somewhat of a heavy heart. It really is the best way to go!

-Stewart

[Chinde]

Thank you, I do appreciate the advice from a seasoned professional.

Like I said I'm just not as comfortable with the expression builder as I am with code based solutions, but I will persevere with the new set of syntax.

On a slightly different tack I could set up code based solutions as functions and use them in the expression builder and hence the report?? Or again is this the road to frustration??

Though I am pre-empting problems which I don't know will come.

[Stewart Ross]

Hi Chinde. Where no existing function can fit your needs it is indeed quite normal to write a bespoke VBA function and use it to provide data in calculated fields of a query.

The only downside is that the use of bespoke function calls in a query limits the generality of the SQL if it might be used or transferred out of the Access environment where you do not have the equivalent VBA functionality available.

E.g. if you connect to such a query using an ODBC driver from Excel the MS Query object which runs the SQL will not recognise any bespoke function calls.

Not a problem if you stay within Access, however.

-Stewart

[Chinde]

Thank you. progress is happening and so far so good.