Conditional Printing

[anoob]

Here, I'll just post a picture so this makes a little more sense (hopefully). This is a screenshot of my DB.

Here: http://www.hangnailproductions.com/DB_Example.gif



Since you started helping me I have added 1 the "RUN REPORT" button. This shows all (and only) the records with the checkbox "PRINT" set to true. Awesome.



BUT, when this runs, I will be printing off all invoices for my hosting clients for this quarter. I would like to update 2 "LAST INVOICED" (to the current date when the report runs) and 3 "DUE DATE" (which will use DateAdd to add the 30 days needed) across their entire sets when I hit the "RUN REPORT" button.

Otherwise I will have to edit each date manually.
I wouldn't even bother but once a client is added, the invoices will be recurring until they cancel service.

Ok, hopefully than clarifies things a bit.
Again, sorry so vague.

[pbaldy]

My bride has been blushing for 16 years now! Contrats on the preggers wife! I've got 2 daughters in their 20's now; they are the best things that ever happened to me (my avatar is a painting by the younger one).

What I posted was VBA code, forgot you were using a macro (I don't like/use them). You could either create a saved query and run it from your macro or use RunSQL with SQL like I posted. Simplest would probably be to the first option, running a saved update query with OpenQuery.

[anoob]

Well thank you - and congrats on the 16 years / daughters.

I actually did some snooping when I started coming to the forum, and I remember reading that was a painting your a daughters. it's touching that you use it.

Anyway, I think the query will work. Sometimes the simplest solutions are best I'd say - and they are also the ones that seem to escape me the most.

Many thanks!

[pbaldy]

Thanks and no problem! Post back if you get stuck.

[anoob]

Oh real quick - when I run an update query, do i just run it with OpenQuery, then close it, then run the report - and the dates should be updated?

Just a ballpark of the sequence of events.

[anoob]

Ok problem...
I click the "Run Report" button as you see above (1).

This opens my query called "UpdatePrintedSubscriptions".
This does what it is supposed to do, it shows the records with the checkboxes set to true, and the date to current and due date to today +30.

http://www.hangnailproductions.com/helpagain.gif




Great.
Except, this does not save the values shown above in the table that the query is based off of.

This is problematic because the form (top of thread) with the red numbers on it, AND the report we finally got to run correctly, are based off the table. How can I take this open query with the correct data and get this to save in the table everything is based on?

The table is called "SubscriptionServices" for what it's worth...

Hope that makes sense.

[pbaldy]

What is the SQL of your query? An update query should be changing the underlying data.

[anoob]

Perhaps I did not specify an "update query"?
Not sure what / how that is.
Thanks for your help.

SELECT SubscriptionServices.Print_Invoice, Date() AS Last_Invoiced, DateAdd("d",30,Date()) AS Due_Date
FROM SubscriptionServices
WHERE (((SubscriptionServices.Print_Invoice)=True));

[pbaldy]

You did not. An update query would have syntax similar to what I posted earlier, and will start with "UPDATE":

UPDATE TableName
SET Field1 = 123[, Field2 = 'ABC']
[WHERE Field3 = -1]

The brackets indicate optional items.

[anoob]

Stupid Question Alert:

You would do this in SQL view?

Also, you said you used VBA for other functions - I believe this means Visual Basic? The only experience I have with VB is making some stupid calculator thing in a college course. How do you apply this elusive VBA to a database?

As you can see, I plan to drain all your knowledge like a baby alien might drain your life force...

Many thanks.

[pbaldy]

No such thing as a stupid question! You can do it in SQL view, but it can also be done in design view. If you have your query open in design view, on the Design tab of the ribbon you'll see an icon that says "Update". Clicking on that will change the query and what you see in design view, the primary difference being the "Update To" line, which is where you tell it what the new value should be.

VBA is Visual Basic for Applications. It's pretty much VB, with some differences:

http://en.wikipedia.org/wiki/Visual_...r_Applications

Here's a primer on how to get into it:

http://www.baldyweb.com/FirstVBA.htm

[anoob]

We got 'er - thanks very much!!!!!!!!!!!!!!!!!!

[anoob]

It's working like a charm. Thanks again.

[pbaldy]

Happy to help!