I am trying to accomplish the following with VBA:

When the user moves to a new record the new record date should be incremented by 1 from the previous record date.

This is how far i've got:

I have declared a global variable for the OrderDate and used the following code to take the date from the old record, put it into the variable from which the new record takes it's 'OrderDate' value:

Private Sub Form_AfterUpdate(Cancel As Integer)
' store the existing date in a globally available variable

strOrderDate = Forms!frm_orders!OrderDate

End Sub


Private Sub Form_AfterInsert()
' add the date on the new record from a globally available variable and increment by a month

Forms!frm_orders!OrderDate = DateAdd("m", 1, strOrderDate)

End Sub

Now, I feel totally lost with this, it's not working for me and is probably blindingly obvious to you.

Anyone got any ideas?

Thanks a lot,

Mark

Why do you need to do this? Entering the date and selecting the month part later is much easier and will prevent other problems associated with storing dates by month.

Hi Rich,

Easier for who?

It's all in the name of convenience. My client has two hundred + customers that typically repeat the same order each month (with a few changes). Therefore, if each new record fills in the next month field then data entry is that much quicker, plus i would like the order date to be the same (initially) on each order detail within each order.

This way, for each client and each month he just clicks 'add new record' and the new record already has an incremented date field, plus the previous month's orders are copied in with an updated date field.

Thanks for any replies,
Mark

200 customers is not very many in terms of some of the numbers quoted in this forum.
I run my own small business with app 600 customers issuing appr. 1000 invoices a year again small in comparison. I have no trouble with date fields esp monthly tots. What if your clients customers do not order for one or two months? In any case you don't need code just add Date() as the default value for new records and format the control to mmmm yyyy however you will have problems storing date fields in this way.

Hi Rich,

I really didn't want to get into a discussion as to the merits of why i'm trying to achieve this result. And as for you having loads of customers and invoices - well good for you but it doesn't really make any difference to me or my client.

Thanks for the tip about formatting controls for mmmm and yyyy - i'll try it out.

Thank you,
Mark

I wish you the very best of luck my friend you are going to need it.