I'm sure this is easy, but I just can't get it to work...

I have a sales data entry (datasheet) subform, the fields are SalesDate, ProductID, Qty
I would like to have it so that the user enters the sales date the first time, but then it is automatically filled with the same date as the previous record, until the user overrides it with a new date, then it carries that new date forward for new records and so on. (then the user can enter sales from a range of dates into the forma in one session, but won't have to enter the date on every record.

My first thought was to change the DefaultValue for the field in the underlying table each time a record is added, but I just can't seem to do this.

after that I tried doing it by storing the last entered date in a variable and putting the date straight into the text box with:
Me.SalesDate = StoredDate

which is fine, but I can't find a suitable event to put this operation into, what I really need is an OnNewRecord Event, but there isn't one

help!

Mike

Mike,

As I recall, in table view if you type
CTRL &' it fills in the info from the field in the previous record. I'm sure this will work in a form using a sendkeys command on exit of the previous field or ongotfocus of the date field.

The only thing you'll have to trap for is when there is no previous record.

Hope it works

Ian

Ian

If I understand your question correctly you could do it this way and put it in the On_Exit event of the last record of the form before a new record is started.

With CodeContextObject

.SalesDate.DefaultValue = """" & .SalesDate & """"
.ProductID.DefaultValue = """" & .ProductID & """"
.Qty.DefaultValue = """" & .Qty & """"

End With

If you are using a subform you may have to set your focus to the first record and a command to start a new record like this.

DoCmd.GoToControl "SalesDate"
DoCmd.GoToRecord acForm, "FormName", acNewRec

Talismanic

This very nearly worked, I didn't need the WITH statement, as it's only the date I'm carrying forward (the product and qty are different on every line), so I put:

Me.Date_of_Sale.DefaultValue = "#" & Me.Salesdate & "#"

which seemed to work, except that it gets confused over date formats, if the date entered is 28/11/00, it carries forward fine, if the date entered is 01/12/00, it carries forward as 12/01/00... Grrrrr

So, I tried:
Me.Date_of_Sale.DefaultValue = "#" & Format(Me.Salesdate, "ddmmyy") & "#"

which doesn't work at all, it just says
#Name?

any ideas?

Mike

You mentioned in your original question that you would like to use a NewRecord Event. You could use the On Current event and then say: If Me.NewRecord Then... If it is a new record then your field will be updated, otherwise it won't.

Thanks Jack, I'll try that - the date format thing is driving me bonkers though, I've tried using Cdate and Format to no avail.

It can't be impossible can it?

Mike

[This message has been edited by Mike Gurman (edited 12-07-2000).]

There is a sample function on the "Forms" sampler from MS I think it will do what you want.
HTH

Where do I find it richie?

Thanks

Mike

Microsoft downloads support etc., sorry I can't be more specific Mike such a long time ago I found it.

Mike -

If you do not find the Form Sampler that Richie mentioned then email me and I will send you a copy....

Jack

EUREKA!!!!

In sheer desperation I tried replacing:
Me.Date_of_Sale.DefaultValue = "#" & Me.Salesdate & "#"

with:
Dim Mydate as Date
mydate = Me.Salesdate
Me.Date_of_Sale.DefaultValue = mydate

and it now preserves the date format all of the time, and I can see why; it forces Access to keep the value as a date, rather than treating it as a text item.

Mike

[This message has been edited by Mike Gurman (edited 12-08-2000).]