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 |
#11
|
|||
|
|||
Show detail rows in report with or without data using VBA?
Ok, let me explain exactly what I meant.
So I'll need to put these two textbox fields in the footer of the detail section, right? No you need to put these two hidden text boxes IN the detail section and not in the footer. The running sum will not work in the footer. I'm a little confused here. I currently have a single row of textbox fields in the detail section that are auto-populated when I run the report. Do you mean replace these with 10 rows of text boxes? I'm not sure what you mean here. You need to have 1 text box in the detail section (plus the two hidden boxes mentioned above). You also need to draw a box around this text box using the line tool in the design view of the report (this will still show as a box when you make the text box later hidden). Then you display all the records returned from your query in the normat way. However in the OnPrint method you need to check if the running sum equals the counter (i.e. you are printing the last record from your query). If you are and it is not the 10th record (in which case you don't need to do anything special), you set the NextRec property to FALSE. This will cause access, to repeat the last record. However, this time you set the Visible property to false and you only see the drawn box giving the impression that that another text box is there. When you have printed 10 records in total (say 4 from your query and 6 times the last record repeated), you set the NextRec to TRUE and access will try to grab the next record from the query. There won't be one and therefore it will move on to print the Report Footer. Make sure that the border in the text boxes is set to Transparent so that you don't get double borders in the records returned from your query (the text box's border and the drawn rectangle). I hope this helps and sorry for taking so long to reply. Lefteris |
#12
|
|||
|
|||
Show detail rows in report with or without data using VBA?
hi
I've tried to follow your description but am still having problems. I had two data items rows in the database when debugging. A Item row is made up of about 16 textbox fields placed side by side in the data section. I turned off the border on each and drew a single long rectangle around them and then drew verticle lines to separate the fields. 1. I can't get the txtTotalItems textbox to work corrrectly. Its located in the detail section and if I use Count([ItemID]) in the control source,it returns the total number of records in the table but not the record subset. In otherwords, the Items table actually has 4 records, two each related to two different parent records...so the value returned is 4...I need to get it to return a value related to the current record set which is 2. I hardcoded this so I could test. I've placed the VBA code below in the On Print event of the detail section. Can you check my code and advise? Thanks in advance. Here's the code: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) Dim DetailRowsMax As Integer ' max number of detail rows in detail section Dim TotalItems As Integer ' Total data rows in detail section Dim RunningSum As Integer ' Holder of running sum text box value 'Dim BlankRowsNeeded As Integer in "On open" event of report 'Dim BlankRowsAdded As Integer in "On open" event of report Dim NoOfControls As Integer ' total number of textbox controls in detail Dim Cnt As Integer ' count value TotalItems = Me.txtTotalItems ' textbox in detail section TotalItems = 2 ' hard coded value for debugging purposes RunningSum = Me.txtRSum ' textbox in detail section DetailRowsMax = 10 BlankRowsNeeded = DetailRowsMax - TotalItems BlankRowsAdded = 0 If RunningSum = TotalItems Then GoTo Quit Else NoOfControls = Me.Section(acDetail).Controls.Count If NoOfControls 0 Then For Cnt = 0 To NoOfControls - 1 Me.Controls(Cnt).Visible = False Next Cnt Else Debug.Print vbTab; "(no controls)" End If BlankRowsAdded = BlankRowsAdded + 1 If BlankRowsAdded BlankRowsNeeded Then Me.NextRecord = False Else Me.NextRecord = True End If End If Quit: End Sub " wrote: Ok, let me explain exactly what I meant. So I'll need to put these two textbox fields in the footer of the detail section, right? No you need to put these two hidden text boxes IN the detail section and not in the footer. The running sum will not work in the footer. I'm a little confused here. I currently have a single row of textbox fields in the detail section that are auto-populated when I run the report. Do you mean replace these with 10 rows of text boxes? I'm not sure what you mean here. You need to have 1 text box in the detail section (plus the two hidden boxes mentioned above). You also need to draw a box around this text box using the line tool in the design view of the report (this will still show as a box when you make the text box later hidden). Then you display all the records returned from your query in the normat way. However in the OnPrint method you need to check if the running sum equals the counter (i.e. you are printing the last record from your query). If you are and it is not the 10th record (in which case you don't need to do anything special), you set the NextRec property to FALSE. This will cause access, to repeat the last record. However, this time you set the Visible property to false and you only see the drawn box giving the impression that that another text box is there. When you have printed 10 records in total (say 4 from your query and 6 times the last record repeated), you set the NextRec to TRUE and access will try to grab the next record from the query. There won't be one and therefore it will move on to print the Report Footer. Make sure that the border in the text boxes is set to Transparent so that you don't get double borders in the records returned from your query (the text box's border and the drawn rectangle). I hope this helps and sorry for taking so long to reply. Lefteris |
#13
|
|||
|
|||
Show detail rows in report with or without data using VBA?
Hi,
I've had a look at your code and I have a couple of suggestions to make. However, I am not sure that they will solve the problem since you are not saying how it doesn't work (when you hard code TotalItems). One comment is in the loop where you set the controls to invisible, I have it as below: Dim ctl As Control For Each ctl In Me.Controls If ctl.Properties("ControlType") = acTextBox Then ctl.Properties("Visible") = False End If Next ctl I remember I had to put the check for the ControlType for some reason. I also think that you need to change the setting of the Visible property using the Properties collection as above. The rest of your code seems ok to me and the logic is good. I also assume that BlankRowsAdded and BlankRowsNeeded are global variables. Now as to why the TotalItems gives you the wrong value I am not sure. I have used it the same way and it worked fine. One difference is that in my query I had two fields with the same name, so the one that I needed to use in the count function was preceded by the table name (i.e. Table.[my unique field]). I suggest you try to get it to work first with the hardcoded value and then try to sort out the Count problem. By the way, I assume that the record source for your report is set to a Query and not a Table. Let me know how it goes, Lefteris |
#14
|
|||
|
|||
Show detail rows in report with or without data using VBA?
Hi Lefteris!
First...let me say thanks for sticking with me on this. I really appreciate your help. :-) I've rewritten my code after your comments and some further debugging. The new code is shown below and works as intended...it prints data rows followed by the required number of empty rows... but I'm now working out how to fix a multi-page print problem. My original report was setup to print two copies, an "originator" and "purchaser" copy before I set out to change the report format to print the empty rows. That part stopped working correctly but I think it has to do with page breaks. I've answered some of your questions in the dialog text below the new code. Here's the new code: Global Declarations ===================================== Public BlankRowsNeeded As Integer Public DetailRowsMax As Integer Public DataRows As Integer Public TotalDetailRows As Integer Public LastDataRow As Boolean Public BlankRowsAdded As Integer in Report "on open" event proc ===================================== BlankRowsNeeded = 0 BlankRowsAdded = 0 DetailRowsMax = 10 in Detail "on print" event proc ===================================== Dim RunningSum As Integer ' running sum text box value Dim ctl As Control ' control object DataRows = Me.txtTotalItems ' textbox in detail section RunningSum = Me.txtRSum ' textbox in detail section BlankRowsNeeded = DetailRowsMax - DataRows If RunningSum = DataRows Then If LastDataRow Then For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox .Visible = False End Select End With Next ctl BlankRowsAdded = BlankRowsAdded + 1 Else LastDataRow = True End If If BlankRowsAdded BlankRowsNeeded Then Me.NextRecord = False Else Me.NextRecord = True End If End If TotalDetailRows = TotalDetailRows + 1 'counter for debugging " wrote: Hi, I've had a look at your code and I have a couple of suggestions to make. However, I am not sure that they will solve the problem since you are not saying how it doesn't work (when you hard code TotalItems). One comment is in the loop where you set the controls to invisible, I have it as below: Dim ctl As Control For Each ctl In Me.Controls If ctl.Properties("ControlType") = acTextBox Then ctl.Properties("Visible") = False End If Next ctl I remember I had to put the check for the ControlType for some reason. I also think that you need to change the setting of the Visible property using the Properties collection as above. Yup. I discovered that too. Unless your specific about which controls to make hidden it will hide the lines as well (line objects are controls) The rest of your code seems ok to me and the logic is good. I also assume that BlankRowsAdded and BlankRowsNeeded are global variables. Correct Now as to why the TotalItems gives you the wrong value I am not sure. I have used it the same way and it worked fine. One difference is that in my query I had two fields with the same name, so the one that I needed to use in the count function was preceded by the table name (i.e. Table.[my unique field]). I changed the expression specified in the control source of the TotalItems txtbox: =DCount("[PRID]","PRItems","PRID = CurrentRecord") It now works correctly. Sheesh.... I suggest you try to get it to work first with the hardcoded value and then try to sort out the Count problem. By the way, I assume that the record source for your report is set to a Query and not a Table. Correct Let me know how it goes, Lefteris |
#15
|
|||
|
|||
Show detail rows in report with or without data using VBA?
Hi Tec,
no problem for the helping. After all, I remember trying to get some help with the exact issue and couldn't get any, so I know what it is like Now as far as the problem that you are having now (possibly with the page breaks) not sure why this is. However, I would like to make you aware that I had some problems with printing the report, as it seemed to misbehave when I "print previewed" the report before printing. In particular, when I opened a report for print preview, then moved to the second page and then tried to print it, I was getting blank records (sometimes, not always). I guess that Access is a bit strange (and possibly buggy?) with the way that some events are fired (this is more the case for the Format event). The way around it, was to modify the behaviour of the print button in the menu that I had, to first close the report and then open it again in "Print Mode", i.e. print it. This worked fine. I don't know if this is your problem at the moment, but I am pointing it out in case you run into it later on. Good luck, Lefteris Ο/Η Tec92407 *γραψε: Hi Lefteris! First...let me say thanks for sticking with me on this. I really appreciate your help. :-) I've rewritten my code after your comments and some further debugging. The new code is shown below and works as intended...it prints data rows followed by the required number of empty rows... but I'm now working out how to fix a multi-page print problem. My original report was setup to print two copies, an "originator" and "purchaser" copy before I set out to change the report format to print the empty rows. That part stopped working correctly but I think it has to do with page breaks. I've answered some of your questions in the dialog text below the new code. Here's the new code: Global Declarations ===================================== Public BlankRowsNeeded As Integer Public DetailRowsMax As Integer Public DataRows As Integer Public TotalDetailRows As Integer Public LastDataRow As Boolean Public BlankRowsAdded As Integer in Report "on open" event proc ===================================== BlankRowsNeeded = 0 BlankRowsAdded = 0 DetailRowsMax = 10 in Detail "on print" event proc ===================================== Dim RunningSum As Integer ' running sum text box value Dim ctl As Control ' control object DataRows = Me.txtTotalItems ' textbox in detail section RunningSum = Me.txtRSum ' textbox in detail section BlankRowsNeeded = DetailRowsMax - DataRows If RunningSum = DataRows Then If LastDataRow Then For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox .Visible = False End Select End With Next ctl BlankRowsAdded = BlankRowsAdded + 1 Else LastDataRow = True End If If BlankRowsAdded BlankRowsNeeded Then Me.NextRecord = False Else Me.NextRecord = True End If End If TotalDetailRows = TotalDetailRows + 1 'counter for debugging " wrote: Hi, I've had a look at your code and I have a couple of suggestions to make. However, I am not sure that they will solve the problem since you are not saying how it doesn't work (when you hard code TotalItems). One comment is in the loop where you set the controls to invisible, I have it as below: Dim ctl As Control For Each ctl In Me.Controls If ctl.Properties("ControlType") = acTextBox Then ctl.Properties("Visible") = False End If Next ctl I remember I had to put the check for the ControlType for some reason. I also think that you need to change the setting of the Visible property using the Properties collection as above. Yup. I discovered that too. Unless your specific about which controls to make hidden it will hide the lines as well (line objects are controls) The rest of your code seems ok to me and the logic is good. I also assume that BlankRowsAdded and BlankRowsNeeded are global variables. Correct Now as to why the TotalItems gives you the wrong value I am not sure. I have used it the same way and it worked fine. One difference is that in my query I had two fields with the same name, so the one that I needed to use in the count function was preceded by the table name (i.e. Table.[my unique field]). I changed the expression specified in the control source of the TotalItems txtbox: =DCount("[PRID]","PRItems","PRID = CurrentRecord") It now works correctly. Sheesh.... I suggest you try to get it to work first with the hardcoded value and then try to sort out the Count problem. By the way, I assume that the record source for your report is set to a Query and not a Table. Correct Let me know how it goes, Lefteris |
#16
|
|||
|
|||
Show detail rows in report with or without data using VBA?
Hi..
I found out what was wrong. Basically, the "on print" event will get executed for each record in the underlying query and again for each page, so the code has to be smart enough to detect this. In short, I have to make sure this code is run an formats the page for every page I print. So I've modified the code again to make sure I don't set the last record "hidden" and to reset all variables when another page needs to be printed. I did this using a new global boolean "LastDataRow" that detects when the last data row has been processed. I also modified the txtRSum text box running sum property to "over group" instead of over all so it would reset when the next page begins to be processed. This code works and I only have one issue left. When I print each page, that last row contains data from the last record when it should be empty. I must be leaving a row visible or something. Any suggestions are welcome.. Modified code below: If RunningSum = DataRows Then If LastDataRow Then For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox .Visible = False End Select End With Next ctl BlankRowsAdded = BlankRowsAdded + 1 Else LastDataRow = True End If If BlankRowsAdded BlankRowsNeeded Then Me.NextRecord = False Else Me.NextRecord = True BlankRowsAdded = 0 LastDataRow = False For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox .Visible = True End Select End With Next ctl End If End If TotalDetailRows = TotalDetailRows + 1 'counter for debugging " wrote: Hi Tec, no problem for the helping. After all, I remember trying to get some help with the exact issue and couldn't get any, so I know what it is like Now as far as the problem that you are having now (possibly with the page breaks) not sure why this is. However, I would like to make you aware that I had some problems with printing the report, as it seemed to misbehave when I "print previewed" the report before printing. In particular, when I opened a report for print preview, then moved to the second page and then tried to print it, I was getting blank records (sometimes, not always). I guess that Access is a bit strange (and possibly buggy?) with the way that some events are fired (this is more the case for the Format event). The way around it, was to modify the behaviour of the print button in the menu that I had, to first close the report and then open it again in "Print Mode", i.e. print it. This worked fine. See my comments above. Also, I found when working between the VBA environment and report design environment I sometimes would mistakely move a textbox or modify a section in a report in a way the would make the report span more than one page. This would cause the sequence of execution to change when debugging in VBA. Maannn....was that ever confusing....:-) Oh well....live and learn. ;-) I don't know if this is your problem at the moment, but I am pointing it out in case you run into it later on. Good luck, Lefteris Ο/Η Tec92407 *γραψε: Hi Lefteris! First...let me say thanks for sticking with me on this. I really appreciate your help. :-) I've rewritten my code after your comments and some further debugging. The new code is shown below and works as intended...it prints data rows followed by the required number of empty rows... but I'm now working out how to fix a multi-page print problem. My original report was setup to print two copies, an "originator" and "purchaser" copy before I set out to change the report format to print the empty rows. That part stopped working correctly but I think it has to do with page breaks. I've answered some of your questions in the dialog text below the new code. Here's the new code: Global Declarations ===================================== Public BlankRowsNeeded As Integer Public DetailRowsMax As Integer Public DataRows As Integer Public TotalDetailRows As Integer Public LastDataRow As Boolean Public BlankRowsAdded As Integer in Report "on open" event proc ===================================== BlankRowsNeeded = 0 BlankRowsAdded = 0 DetailRowsMax = 10 in Detail "on print" event proc ===================================== Dim RunningSum As Integer ' running sum text box value Dim ctl As Control ' control object DataRows = Me.txtTotalItems ' textbox in detail section RunningSum = Me.txtRSum ' textbox in detail section BlankRowsNeeded = DetailRowsMax - DataRows If RunningSum = DataRows Then If LastDataRow Then For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox .Visible = False End Select End With Next ctl BlankRowsAdded = BlankRowsAdded + 1 Else LastDataRow = True End If If BlankRowsAdded BlankRowsNeeded Then Me.NextRecord = False Else Me.NextRecord = True End If End If TotalDetailRows = TotalDetailRows + 1 'counter for debugging " wrote: Hi, I've had a look at your code and I have a couple of suggestions to make. However, I am not sure that they will solve the problem since you are not saying how it doesn't work (when you hard code TotalItems). One comment is in the loop where you set the controls to invisible, I have it as below: Dim ctl As Control For Each ctl In Me.Controls If ctl.Properties("ControlType") = acTextBox Then ctl.Properties("Visible") = False End If Next ctl I remember I had to put the check for the ControlType for some reason. I also think that you need to change the setting of the Visible property using the Properties collection as above. Yup. I discovered that too. Unless your specific about which controls to make hidden it will hide the lines as well (line objects are controls) The rest of your code seems ok to me and the logic is good. I also assume that BlankRowsAdded and BlankRowsNeeded are global variables. Correct Now as to why the TotalItems gives you the wrong value I am not sure. I have used it the same way and it worked fine. One difference is that in my query I had two fields with the same name, so the one that I needed to use in the count function was preceded by the table name (i.e. Table.[my unique field]). I changed the expression specified in the control source of the TotalItems txtbox: =DCount("[PRID]","PRItems","PRID = CurrentRecord") It now works correctly. Sheesh.... I suggest you try to get it to work first with the hardcoded value and then try to sort out the Count problem. By the way, I assume that the record source for your report is set to a Query and not a Table. Correct Let me know how it goes, Lefteris |
#17
|
|||
|
|||
Show detail rows in report with or without data using VBA?
Hi
I've worked out the final bug and the report is working correctly. I can now print multiple copies correctly. Thanks for all your help Here's the final version. ======================================== Dim RunningSum As Integer ' running sum text box value Dim ctl As Control ' control object DataRows = Me.txtTotalItems ' textbox in detail section RunningSum = Me.txtRSum ' textbox in detail section BlankRowsNeeded = DetailRowsMax - DataRows If RunningSum = DataRows Then If LastDataRow Then For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox .Visible = False End Select End With Next ctl BlankRowsAdded = BlankRowsAdded + 1 Else LastDataRow = True End If If BlankRowsAdded BlankRowsNeeded Then Me.NextRecord = False Else Me.NextRecord = True BlankRowsAdded = 0 LastDataRow = False End If Else For Each ctl In Me.Section(acDetail).Controls With ctl Select Case .ControlType Case acTextBox .Visible = True End Select End With Next ctl End If |
#18
|
|||
|
|||
Show detail rows in report with or without data using VBA?
Hi Tec,
I'm glad that you managed to get it to work correctly. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Parameter thru Form Dialog Box for REPORT | Sandy | Setting Up & Running Reports | 16 | January 10th, 2006 10:06 AM |
PST file has reached maximum size | Jeff C | General Discussion | 2 | October 6th, 2005 01:35 PM |
Help PLEASE! Not sure what answer is: Match? Index? Other? | baz | Worksheet Functions | 7 | September 3rd, 2005 03:47 PM |
filter out rows in a report with hidden data | efaye | Setting Up & Running Reports | 2 | May 27th, 2005 02:08 AM |
Report detail does not show data from subform | Ivan | Setting Up & Running Reports | 0 | April 15th, 2005 09:13 PM |