Hello,
I have an idea how to do this but do not know how to write the code. In a report I would like to do the following:
I have a key_id which is a hidden field on the report. For each record I would like to check the count of that key in a temporary table. if the count is 0 then I want to insert a row in that table for that key_id. If the count is greater than 0 then I would like to change the color of that row.
select count(*) from tempTable where key_id = hiddenKey
if count(*) > 0
change color in the detail
else
insert into tempTable(key_id, report_name) values (hiddenKey, 'My Report Name')
On opening the report I would like to delete all records in that report where report_name = 'My Report Name'
I am assuming the count(*) code will go in the detail_format and the code for delete the records will go in the open report event.
How do I run these SQL statements from the code builder. Basically what I am asking is how to access the temp table from the code builder to insert and delete records and how do I get the count programatically. The only way I know to access the database is via modules where I write a function and pass in a parameter:
Sample
Set myQuery = DBEngine.Workspaces(0).Databases(0).OpenRecordset("select Country from [Link - Countries]")
Do Until myQuery.EOF
stringToReturn = stringToReturn & myQuery!Country & ", "
myQuery.MoveNext
Loop
myQuery.Close
What syntax do I need to use to write code in a report to access other tables and how do I get the value of count(*) out?
Thanks for your help in advance.
I have an idea how to do this but do not know how to write the code. In a report I would like to do the following:
I have a key_id which is a hidden field on the report. For each record I would like to check the count of that key in a temporary table. if the count is 0 then I want to insert a row in that table for that key_id. If the count is greater than 0 then I would like to change the color of that row.
select count(*) from tempTable where key_id = hiddenKey
if count(*) > 0
change color in the detail
else
insert into tempTable(key_id, report_name) values (hiddenKey, 'My Report Name')
On opening the report I would like to delete all records in that report where report_name = 'My Report Name'
I am assuming the count(*) code will go in the detail_format and the code for delete the records will go in the open report event.
How do I run these SQL statements from the code builder. Basically what I am asking is how to access the temp table from the code builder to insert and delete records and how do I get the count programatically. The only way I know to access the database is via modules where I write a function and pass in a parameter:
Sample
Set myQuery = DBEngine.Workspaces(0).Databases(0).OpenRecordset("select Country from [Link - Countries]")
Do Until myQuery.EOF
stringToReturn = stringToReturn & myQuery!Country & ", "
myQuery.MoveNext
Loop
myQuery.Close
What syntax do I need to use to write code in a report to access other tables and how do I get the value of count(*) out?
Thanks for your help in advance.
