Am I trying to be too clever?

[christheancient]

With a lot of help from people on this site who know a darned sight more than I do, I am trying to build a customer database. I have been given a lot of help to date when I got stuck. And I've been extremely grateful!

A lot of my forms and reports etc work well in isolation, but I am well and truly stuck on what (I hope) is the final hurdle.

The aim is to produce a payments received report based on data between two given dates.

These dates are chosen using a neat calendar-driven form (thanks to the guys who helped there) and stored in a table.

A report on the payments is made using a query that gets the payments between the two dates.

What I want to do is...

From the switchboard, the user selects that a report is needed. The form 'pops up' to get the required between dates. The dates are loaded into the table (no problems there). Next, the query runs in the background (ideally) to work out all the payments received between the two dates. And finally, the report pops on the screen in preview mode.

Using macros, I can, of course, make the calendar form open up and I can make the report open up, but I can't run the query in between. Also, the report has never waited until the calendar form worked.

So - bottom line now - is there a way I can, from the switchboard:

1. Open the calendar form and allow the underlying table to action the new dates. Then...

2. Run the query (invisibly) so that the data is ready for the next stage...

3. Open the report in print preview, ready to print, with all the most up-to-date info in it.

I have tried (extremely unsuccessfully) to crack this and I am about ready to go back to a much simpler (but not as snazzy) way that I used to do it. But I'd like to make people think I'm an ace developer!

Or am I just trying to be too clever for my own good?

TIA

Chris

 

[Rob.Mills]

You're not trying to be TOO clever. Hey, that's where I learned most of my knowledge when I was trying to create more complex automation.

What you're trying to do is definitely doable. If you're trying to use macros I'll have a tough time helping you there - haven't used macros since I studied vb.

But if you're going to use them you'll need to set up a couple different ones. The first macro will simply open the 'pop-up' form. The user can enter the dates there and then have a button on the form for them to hit such as 'View Report'. When they hit that it will open up the report.

Next question: Is the report based off the query? If so you don't have to worry about the opening the query itself. Just opening the report will simply run the query to create the recordset for the report.

Hope that gets you headed in the right direction. Let me know.

 

[christheancient]

Thanks for the help Rob.

I have been trying to work on the lines you said, but I wasn't 100% sure that the query was running on opening the report - but I may well have been wrong.

Where you have given me some food for thought is this idea of opening the report on closing the calendar form. Just goes to show where the mind does (or doesn't) go when you're so close to the darned thing for too long.

I shall toddle off and try that.

Thanks for the food for thought.

Chris

 

[christheancient]

OK, have tried it.

Great idea using the close form to open the report.

However, the report opening is does not seem to be updating the query.

So, the big question now has to become....

How can I make the report update and run the query as it opens?

Chris

 

[christheancient]

Aaaahhh. I've spotted why the query is apparently not updating.

Up until now, when the calendar form was used, it always replaced existing data in the dates 'table' and, therefore, was always current.

However, now it is adding another line of data to the table and still using the first line of the table! The one with the old data.

So I shall struggle on working out why that is happening - and keeping an eye on this forum and praying for guidance!

Chris

 

[christheancient]

I think I've cracked it.

It is probably the most ridiculous, clumsy, stupid and kack-handed way of doing it, but....

I made a macro On Open of the Calendar form that...

runs a Delete query to empty the Dates table...

and then a subProc On_Close() goes on to open the report when the Calendar form is closed.

The report Record Source then uses a Select query that reads the most up-to-date Dates table that has been updated on closing the Calendar form.

If there is a tidier, more logical and cleaner way of doing it, I'm open to suggestions (please).

Chris

 

[Rich]

You don't need to store the dates, you can reference the dates selected on your form in the criteria section of the query,
ie. Between Forms!MyFormName!MyStartDate And Forms!MyFormName!MyEndDate

 

[christheancient]

It's s-o-o-o-o easy when you know how!

Thanks Rich, I shall give that a go when I have a few minutes in hand.

Sorry to be a while getting back, but didn't see your reply until just now as I was (sort of) passing. Probably won't get a chance until early evening - and then if I'm lucky! Der Boss has plans for today.

Thanks to all that have helped on this. I appreciate your time and effort.

Chris