Missed dates from date last visited (1 Viewer)

InvoiceID AutoNumber
CustomerID Number
InvoiceDate Date/Time
InvoicePaid Yes/No
ProposedDueDate Date/Time
NextDueDate Date/Time
BillAmount Number
PaidAmount Number
DiscountAmount Number
BalanceAmount Calculated
Program ShortText
AlertStatus Yes/No

Respected pros

I have this particular table where the invoices are stored, there is an append query which
Creates invoices date every day, but if the customer fails to open the access database then that particular day's invoices will not be generated.


Every day A query called "NewInvoiceQ" will be run.
The invoices will be generated based on next due date; if the "NextDueDate" is equal to today's date then invoice will be generated with the "InovoiceDate" to be today's date and the "NextDueDate" will be calculated based on the Column "Program". (Which is monthly, quarterly, halfyearly, annually. And it will copy the Bill amount )


I want a vb code that sees if NextDueDate is today, if not then the code should see the difference between the maximum date in Column "NextDueDate" and todays date and run "NewInvoiceQ" for all the missed dates.

You don't tell us how you run the query, only THAT you run the query. Therefore, we are a bit hobbled by not knowing your setup. Therefore, I can only answer this in the way that I would do it.

To begin, users should never see the database window, navigation pane, or ribbon. I always used a control-panel or switchboard type of form. I might put some code in the switchboard's Form_Load event (because my switchboards were unbound and thus you would never see a Form_Current event for that form).

The code might be something like this, and I'm leaving out declarations, just showing basic code:


When you do this, if the user opened the form yesterday, you get 1 iteration and then the dtLastRun variable matches today and the loop ends. If the user opened the form once already today, the loop does not run because the While's terminating condition is already satisfied. If the user has a larger gap, the loop steps through and does the operation once for each missed day.

Thanks for the Reply sir, that solves my problem. Thank you once again.

Glad to point the way. Don't hesitate to come back with other questions. I might not catch the next one but we have lots of good folks who respond.