Solved

Report Designer

Posted on 2002-06-27
8
218 Views
Last Modified: 2013-12-03
I have a simple application for adding and updating Order information, using Access as the database.
I'm using DAO to add and update as I normally do (will get round to ADO one day), all works fine.

My experience wilth the report designer is limited but I have had no trouble creating what I want so far but I have some questions \ problems with them.

1) One Report lists all orders that are UnDespatched, very simple report and works fine, BUT the problem I have is when I close the report (after using .show) and update an order to Despatched, run the report again, it is the same as before, the Order that is now Despatched still shows, I have tride the refresh command.  It only works correctly when I exit the application and run it again.
When I use DAO I close the Recordset and Database using code, is there a way of making sure the reports do that??
I've also noticed the access.lockfile is always around now, whereas previously it is only there when updating the database.  (I can open it exclusivly though so it is not really locked out, report designer is set to lockreadonly)

2) I have been using SQL commands to retreive data and am aware of how to get data involving DATES, however, when using the SQL Builder to build the reports I have no idea where to start to put a date in.  I'd like a report to be generated that shows all UnDespatched orders for Today but I can't use Now or Date and as the date will always be changing I can't put a fixed one in, any ideas????

Thanks in advance

0
Comment
Question by:kitmax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 

Accepted Solution

by:
VBGuy91917 earned 100 total points
ID: 7113411
You might try the requery method on your recordset before opending your report again.  That basically re-runs the query that got you your recordset.  I'm not sure if that will, but see if that works.  The report designer can be a pain sometimes.

As for the 2nd question, I would create a variable that stores the date you want to pull records from and pass that to as SQL statement that you execute to get your data.  Example:


    dim dteMyDate as Date
    dteMyDate = Date  (or whatever you want to set it to)

    db.openrecordset( "SELECT * FROM rs WHERE UnDespatched = UnDespatched AND DespatchDate = #" & dteMyDate & "#")

Hope that helps a little.
0
 
LVL 1

Author Comment

by:kitmax
ID: 7114807
Thanks, I'll try the first point in the morning.

The 2nd point is a little more difficult (for me), I have used SQL by dates before....  

CriteriaDate = Format(frmStockReport.DTDate, "mm/dd/yyyy")
CriteriaDate = "#" & CriteriaDate & "#"
Set SkyRS = SkyDB.OpenRecordset("SELECT * FROM Stock WHERE Despatched = " & CriteriaDate & "")

But, how would it be done for a report, when I used the SQL builder in report designer it tells me it can't do dates, but does let me put an actual date in.  The reports are completly done in designer with no code by myself.
I've seen somewhere, sql statements for reports running in code, that would help.

Kit
0
 
LVL 1

Expert Comment

by:sksahir
ID: 7115050
hi kitmax
try out following
1) when u use  datareport1.show method before it use
   dataenvironment1.open method
2) and then go to datareport right click on report then  
   choose show code and from listing of datareport method
   choose unload method and type in that method
   dataenvironment1.close
it will solve your problem


sahir
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7115474
sksahir.

I suspect you hit the answer button in error.  If this is not the case, please re-read the EE Guidelines regarding locking questions with answers, especially when they do not even come close to answering the original question.

Anthony
0
 
LVL 1

Author Comment

by:kitmax
ID: 7115912
I have no requery methods, only refresh which does not work.

Also, there is no unload option on the report.

Kit
0
 
LVL 1

Author Comment

by:kitmax
ID: 7116035
OK, Thanks to you both so far, found out how to use the requery, close methods, also found this site which helped a little http://dbforums.com/archive/130/2001/06/4/82124

Any thoughts on the date issue, is it possible to create a query for a report during initialisation??

Thanks
Kit
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7964598
Hi kitmax,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept VBGuy91917's comment(s) as an answer.

kitmax, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
0
 
LVL 5

Expert Comment

by:Netminder
ID: 8014831
Per recommendation, force-accepted.

Netminder
EE Admin
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month6 days, 2 hours left to enroll

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question