UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exra Line - Repost Topic ( Add Blank Lines On The Report), Access 2007    
 
   
phil_andre
post Dec 23 2017, 11:35 PM
Post#1



Posts: 212
Joined: 7-December 16



Dear Support Team,

Greetings!

I would like to ask your kind further assistance on the same topic before and i am going to update my apps.

I followed Mr. Joe's instruction attached demo, and here is the screenshot for the result got error.

SELECT 0 as ExtraLine, tbl_MR.MRID, tbl_MR.MRDate, tbl_MR.ProjectName, tbl_MR.ProjectNo, tbl_MR.DLocation, tbl_MR.DDate, tbl_MR.PBName, tbl_MR.QuoteRef, tbl_MR.QuoteDate, tbl_MR.GLAccount, tbl_MR.SupplierName, tbl_MRDetails.ItemID, tbl_MRDetails.ItemCodeSys, tbl_MRDetails.ItemCodeStr, tbl_MRDetails.ItemName, tbl_MRDetails.ItemCatID, tbl_MRDetails.ItemTypID, tbl_MRDetails.ItemLoc, tbl_MRDetails.ItemUnit, tbl_MRDetails.MRQty, tbl_MRDetails.Remarks, tbl_MR.PBDate, tbl_MR.Note, tbl_MRDetails.MRDetailsID, tbl_MRDetails.MRDetailsID, tbl_MR.PBTitle, tbl_MR.MRNo, tbl_MR.PriorityID, tbl_MRDetails.Rate, tbl_MRDetails.Total, tbl_MRDetails.LevelCode, tbl_MR.Curcode, tbl_MR.Discount, tbl_MR.AppMR, tbl_MRDetails.DeliveryTime, tbl_MR.ApprovedBy, tbl_MR.PBNameDT, tbl_MR.MRDateDT, tbl_MR.EmailApproval, tbl_MR.PBCode, tbl_MRDetails.DNRef, tbl_Region.BranchName
FROM (tbl_MR LEFT JOIN tbl_MRDetails ON tbl_MR.MRID = tbl_MRDetails.MRID) LEFT JOIN tbl_Region ON tbl_MR.RegionID = tbl_Region.RegionID;
WHERE tbl_MR.MRID = 12820
UNION ALL SELECT ExtraLine,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null
FROM tbl_ExtraLines
WHERE ExtraLine < TempVars!RecordCheck;


Can it be possible to convert it to VBA code instead of Union query.

Thanks to all in advance.


http://www.UtterAccess.com/forum/index.php...2046373&hl=



Attached File(s)
Attached File  Phil_Demo_Rev4.zip ( 32.15K )Number of downloads: 3
Attached File  Capture.JPG ( 86.56K )Number of downloads: 3
 
Go to the top of the page
 
theDBguy
post Dec 23 2017, 11:47 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,907
Joined: 19-June 07
From: SunnySandyEggo


Hi Phil,

You do know it is against Forum Guidelines to "repost" a thread, right?

Anyway, I think you're getting an error because there's an extra semicolon before the first WHERE clause.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
phil_andre
post Dec 23 2017, 11:53 PM
Post#3



Posts: 212
Joined: 7-December 16



Sir DB,

Noted with thanks and will add semi colon.

Alternatively, can it be run thru VBA code to add extra line , instead of Union query which.
Go to the top of the page
 
theDBguy
post Dec 23 2017, 11:57 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,907
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Yes, I think it should be possible - but why?

Also, I was saying to remove - not add, the extra semicolon.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
phil_andre
post Dec 24 2017, 12:05 AM
Post#5



Posts: 212
Joined: 7-December 16



If possible can you please advise and update the attached demo. I really preferred in VBA. Because this is my first union query in my apps. all are thru VBA.

Also please, can repost my union query SQL for the semi colon. Little bit confusing for me. sorry.
Go to the top of the page
 
theDBguy
post Dec 24 2017, 12:13 AM
Post#6


Access Wiki and Forums Moderator
Posts: 71,907
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Take a look at your SQL. How many semicolons do you see? If you see more than one, try removing all of them except for the last one. What happens?

Sorry, due to the Holidays, I won't be able to modify your code for a while.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
phil_andre
post Dec 24 2017, 12:30 AM
Post#7



Posts: 212
Joined: 7-December 16



Thanks DB.

Done removing the semi and union is okay now, testing filter = 12820


SELECT 0 as ExtraLine, tbl_MR.MRID, tbl_MR.MRDate, tbl_MR.ProjectName, tbl_MR.ProjectNo, tbl_MR.DLocation, tbl_MR.DDate, tbl_MR.PBName, tbl_MR.QuoteRef, tbl_MR.QuoteDate, tbl_MR.GLAccount, tbl_MR.SupplierName, tbl_MRDetails.ItemID, tbl_MRDetails.ItemCodeSys, tbl_MRDetails.ItemCodeStr, tbl_MRDetails.ItemName, tbl_MRDetails.ItemCatID, tbl_MRDetails.ItemTypID, tbl_MRDetails.ItemLoc, tbl_MRDetails.ItemUnit, tbl_MRDetails.MRQty, tbl_MRDetails.Remarks, tbl_MR.PBDate, tbl_MR.Note, tbl_MRDetails.MRDetailsID, tbl_MRDetails.MRDetailsID, tbl_MR.PBTitle, tbl_MR.MRNo, tbl_MR.PriorityID, tbl_MRDetails.Rate, tbl_MRDetails.Total, tbl_MRDetails.LevelCode, tbl_MR.Curcode, tbl_MR.Discount, tbl_MR.AppMR, tbl_MRDetails.DeliveryTime, tbl_MR.ApprovedBy, tbl_MR.PBNameDT, tbl_MR.MRDateDT, tbl_MR.EmailApproval, tbl_MR.PBCode, tbl_MRDetails.DNRef, tbl_Region.BranchName
FROM (tbl_MR LEFT JOIN tbl_MRDetails ON tbl_MR.MRID = tbl_MRDetails.MRID) LEFT JOIN tbl_Region ON tbl_MR.RegionID = tbl_Region.RegionID
WHERE tbl_MR.MRID = 12820
UNION ALL SELECT ExtraLine,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null,Null, Null, Null, Null, Null, Null
FROM tbl_ExtraLines
WHERE ExtraLine < TempVars!RecordCheck;


Yes, advance Merry Xmas and Happy new Year.

Hopefully, will received feedback for the VBA code. as of now i will work with union query. instead of design view, i have to look always in SQL view. this is my first union query.
Go to the top of the page
 
GroverParkGeorge
post Dec 24 2017, 07:56 AM
Post#8


UA Admin
Posts: 32,352
Joined: 20-June 02
From: Newcastle, WA


Why would you rather do this with VBA? What advantage would you anticipate from doing that?

And while Union queries do require you to use the SQL view to edit, that's not really a bad thing. All queries are actually rendered as SQL and becoming more familiar with what it looks like is probably a useful addition to your understanding.

--------------------
Go to the top of the page
 
RJD
post Dec 24 2017, 06:51 PM
Post#9


UtterAccess VIP
Posts: 8,221
Joined: 25-October 10
From: Gulf South USA


Hi again Phil: I think you are really missing the point in how you do this using my procedure. You use VBA to set the initial TempVars value (the number of extra blank lines, initially set to the maximum that might be needed), then open the report. When the report opens, the footer looks at the number of lines required for the specific list to be displayed, and resets the TempVars value. Then the report is closed and then opened again, using the new TempVars value. This happens so quickly that you only see the final report previewed on the screen.

The report calls on the UNION query without any action from you except calling the report as described above. The UNION query is not optional - it is mandatory. And it is not developed in VBA unless you want to do something much more difficult, and which would create a UNION query anyway. Once set up, you never have to deal with the query again. The query (and thus the report) is limited by the MRID selected on the form. This does not have to be done in VBA - and should not be done in VBA, unless, as above, you just want to go to more trouble.

I cannot accommodate your new query in the db you sent, since the tables are obviously now different and even named differently.

Go back to my last demo. If you study the demo and follow that same approach, the report should work just fine. It already limits the report to a selected MRID.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 24 2017, 10:02 PM
Post#10



Posts: 212
Joined: 7-December 16



Hi Joe,

Good Morning.

Well said, Ok let me study it till i will fully understand your procedure. .

I have to walkthrough again on this.


Thanks.
This post has been edited by phil_andre: Dec 24 2017, 10:03 PM
Go to the top of the page
 
RJD
post Dec 24 2017, 10:45 PM
Post#11


UtterAccess VIP
Posts: 8,221
Joined: 25-October 10
From: Gulf South USA


Let us know how this works out for you.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 25 2017, 11:27 AM
Post#12



Posts: 212
Joined: 7-December 16



Dear Joe,

Good Day & Merry Xmas to all!

I studied and analyzed your revised demo and i have noticed it will still create a next blank page when multiples fields are growing.

please find attached Phil Demo_Rev4.01.zip and kindly try mainID=1 in tbl_Main.

In my application, the description datatype is set to memo coz there are times we are entering long details of the items and we put it together in same line details.

This is my feedback after testing...

Thanks and appreciate your soonest reply.


Attached File(s)
Attached File  Phil_Demo_Rev4.01.zip ( 73.05K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Dec 25 2017, 12:42 PM
Post#13


UtterAccess VIP
Posts: 8,221
Joined: 25-October 10
From: Gulf South USA


I tested the db you posted - and the report works as designed. There is no blank page. For ID 1, there are two pages, but both have data on them. The second page is completed with a single blank row.

I don't see the issue.

If you are having this issue with another db, please post that db. You may have applied the technique incorrectly.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
phil_andre
post Dec 25 2017, 01:40 PM
Post#14



Posts: 212
Joined: 7-December 16



Hi Joe,

I mean the main id=1 contain of 2 pages now but the 2nd page does not fill completely with extra line.

Or maybe i run wrongly. I will come back to you tomorrow morning as i am shutting down my pc.

Go to the top of the page
 
phil_andre
post Dec 26 2017, 12:39 AM
Post#15



Posts: 212
Joined: 7-December 16



Hi Joe,

Something went wrong from my test last night and yes you are right the demo still working well with extra lines.

sorry for the confusion.

am going to retest it to my actual application.

thanks.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 06:28 AM