If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Refering to a Reports underlying data using VBA in Detail OnFormat event
I have a complex report that needs to print a series of strings based on
dozens of Boolean Columns in the table the report is based on (Yea I know all about data normalization, and I know this is wrong, but this is the data the customer supplies). The plan was to use some code in the Detail OnFormat event to determine which strings need to be printed. However I am unable to refer to any of the columns in the reports underlying recordset. I get a Error 2465 "ProgramName can't find the field 'afieldname' referred to in your expression" when I try stuff like: Me!ColumnName or Me("ColumnName"). In fact I can not even refer to the reports recordset object at all. Something like Me.Recordset.ColumName will not even compile. So what's up? It looks like a Reports "Me" reference can only "see" objects that have been actually created on the report. I would hate to have to put 4 dozen invisible check boxes on the report just to make this happen. Aside from fixing the bad table design, does anyone have another workaround. Ron W |
#2
|
|||
|
|||
Refering to a Reports underlying data using VBA in Detail OnFormat event
The problem you describe is quite, common, Ron. It seems to result from the
way Access optimizes reports. If it can't actually see a field somewhere (directly in the Control Source of a control, or in the Sortin'n'Grouping), it doesn't bother fetching the field. Rather than add 40 check boxes, you might consider putting the expressions into a query, and making the query the Record Source for a report. You can then put text boxes on your report, and bind them directly to the query fields. Presumably the expression would be something like: IIf([HasMalaria], "Malaria", Null) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "RDub" rweinerattrcrentdotcom wrote in message ... I have a complex report that needs to print a series of strings based on dozens of Boolean Columns in the table the report is based on (Yea I know all about data normalization, and I know this is wrong, but this is the data the customer supplies). The plan was to use some code in the Detail OnFormat event to determine which strings need to be printed. However I am unable to refer to any of the columns in the reports underlying recordset. I get a Error 2465 "ProgramName can't find the field 'afieldname' referred to in your expression" when I try stuff like: Me!ColumnName or Me("ColumnName"). In fact I can not even refer to the reports recordset object at all. Something like Me.Recordset.ColumName will not even compile. So what's up? It looks like a Reports "Me" reference can only "see" objects that have been actually created on the report. I would hate to have to put 4 dozen invisible check boxes on the report just to make this happen. Aside from fixing the bad table design, does anyone have another workaround. Ron W |
#3
|
|||
|
|||
Refering to a Reports underlying data using VBA in Detail OnFormat event
Thanks Allen
I just wanted to be sure I was not missing something stupid. Actually what I should probably do is to create a MASSIVE union query (would have 46 UNIONs) with just the Boolean fields and bind that bad boy to a Subreport. But frankly I am in a foul mood, and this darn thing has only got to work for one lousy week late this month. After that it will be scrapped. In this case I don't think I can use your suggestion, as it would leave holes (empty text boxes) in the report. Oh did I mention that this report has got to fit on a piece of 4" wide by 3" high card stock. I think I will add a bunch of check boxes, and just proceed as I had originally planned, Iterating each field and adding the appropriate string to a Label control (actually 2 label controls as potentially there could be 2 columns of stuff) . An Yes my world does truly suck! Ron W "Allen Browne" wrote in message ... The problem you describe is quite, common, Ron. It seems to result from the way Access optimizes reports. If it can't actually see a field somewhere (directly in the Control Source of a control, or in the Sortin'n'Grouping), it doesn't bother fetching the field. Rather than add 40 check boxes, you might consider putting the expressions into a query, and making the query the Record Source for a report. You can then put text boxes on your report, and bind them directly to the query fields. Presumably the expression would be something like: IIf([HasMalaria], "Malaria", Null) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "RDub" rweinerattrcrentdotcom wrote in message ... I have a complex report that needs to print a series of strings based on dozens of Boolean Columns in the table the report is based on (Yea I know all about data normalization, and I know this is wrong, but this is the data the customer supplies). The plan was to use some code in the Detail OnFormat event to determine which strings need to be printed. However I am unable to refer to any of the columns in the reports underlying recordset. I get a Error 2465 "ProgramName can't find the field 'afieldname' referred to in your expression" when I try stuff like: Me!ColumnName or Me("ColumnName"). In fact I can not even refer to the reports recordset object at all. Something like Me.Recordset.ColumName will not even compile. So what's up? It looks like a Reports "Me" reference can only "see" objects that have been actually created on the report. I would hate to have to put 4 dozen invisible check boxes on the report just to make this happen. Aside from fixing the bad table design, does anyone have another workaround. Ron W |
#4
|
|||
|
|||
Refering to a Reports underlying data using VBA in Detail OnFormatevent
If the report does not use a field (bound to a control, used in sorting, etc.)
then when Access builds the query to be used by the report it drops the field from the source (behind the scenes). So, you will need to include all those boolean fields on the report or ... If the record has a primary key you could use a recordset to retreive the record and step throught the record and build the strings to be printed. Are you stuffing all the strings into one control? Dim Dbany as DAO.Database Dim rst as DAO.Recordset Dim strSQL as String strSQL = "SELECT Boolean1, Boolean2, Boolean3 FROM SomeTable Where PK = " & Me.Pk Set DbAny = CurrentDb() Set rst = DbAny.OpenRecordset (strSQL) 'Now process the fields and build the string John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County RDub wrote: I have a complex report that needs to print a series of strings based on dozens of Boolean Columns in the table the report is based on (Yea I know all about data normalization, and I know this is wrong, but this is the data the customer supplies). The plan was to use some code in the Detail OnFormat event to determine which strings need to be printed. However I am unable to refer to any of the columns in the reports underlying recordset. I get a Error 2465 "ProgramName can't find the field 'afieldname' referred to in your expression" when I try stuff like: Me!ColumnName or Me("ColumnName"). In fact I can not even refer to the reports recordset object at all. Something like Me.Recordset.ColumName will not even compile. So what's up? It looks like a Reports "Me" reference can only "see" objects that have been actually created on the report. I would hate to have to put 4 dozen invisible check boxes on the report just to make this happen. Aside from fixing the bad table design, does anyone have another workaround. Ron W |
#5
|
|||
|
|||
Refering to a Reports underlying data using VBA in Detail OnFormat event
John
Thanks for the suggestion, I decided to go with the 47 invisible check boxes. It's a nasty hack, but it's all working. I am stuffing strings into two controls actually. Have just enough room on the report to get 30 strings in two columns. Waiting on the customer to see if 30 is enough. Might have to go with 3 columns (Label controls). Oye Veh! Ron W "John Spencer" wrote in message ... If the report does not use a field (bound to a control, used in sorting, etc.) then when Access builds the query to be used by the report it drops the field from the source (behind the scenes). So, you will need to include all those boolean fields on the report or ... If the record has a primary key you could use a recordset to retreive the record and step throught the record and build the strings to be printed. Are you stuffing all the strings into one control? Dim Dbany as DAO.Database Dim rst as DAO.Recordset Dim strSQL as String strSQL = "SELECT Boolean1, Boolean2, Boolean3 FROM SomeTable Where PK = " & Me.Pk Set DbAny = CurrentDb() Set rst = DbAny.OpenRecordset (strSQL) 'Now process the fields and build the string John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County RDub wrote: I have a complex report that needs to print a series of strings based on dozens of Boolean Columns in the table the report is based on (Yea I know all about data normalization, and I know this is wrong, but this is the data the customer supplies). The plan was to use some code in the Detail OnFormat event to determine which strings need to be printed. However I am unable to refer to any of the columns in the reports underlying recordset. I get a Error 2465 "ProgramName can't find the field 'afieldname' referred to in your expression" when I try stuff like: Me!ColumnName or Me("ColumnName"). In fact I can not even refer to the reports recordset object at all. Something like Me.Recordset.ColumName will not even compile. So what's up? It looks like a Reports "Me" reference can only "see" objects that have been actually created on the report. I would hate to have to put 4 dozen invisible check boxes on the report just to make this happen. Aside from fixing the bad table design, does anyone have another workaround. Ron W |
Thread Tools | |
Display Modes | |
|
|