Populate field on form/query with current date

[heidihutton]

I want to populate a field on a form(and in turn the query that runs the form)with the current date.

It's a survey database. I want to keep a record of the call dates (up to five). I want the date fields to automatically populate with the current date when a submit button is pushed.

How do I tell access in which of the five fields to put the current date?

 

[joshuar]

Since you didn't say how you know which date field to store the date in, I'm guessing that you want to store it in the first blank date field. Try

If isnull(DateField1) then
Me!Datefield1 = Date()
Elseif isnull(DateField2) then
Me!Datefield2 = Date()
Elseif isnull(DateField3) then
Me!Datefield3 = Date()
Elseif isnull(DateField4) then
Me!Datefield4 = Date()
Elseif isnull(DateField5) then
Me!Datefield5 = Date()
End if

Another way (and probable correct Third Normal Form) would be to store your survey dates in another table shown as a subform and have the submit button insert a record into the survey dates table.

Did this help or did I misinterpret your question?

-Joshua

 

[grnzbra]

How about in the OnCurrent event of the form

If IsNull(me!calldate1) then
Me!calldate1 = Date()
Exit Sub
End If

If IsNull(me!calldate2) then
Me!calldate2 = Date()
Exit Sub
End If

etc.

etc.

etc.

The only problem is that every time you go to a particular record, you will add a date to the next field. Perhaps you could control the mode in which you open it (read, enter data) so that just reading it wouldn't run the code. or you could have a hidden field to which you add the Date() when you run the code and compare that with Date(). If they are the same, the code is skipped (this would limit you to 1 call per day)

Of course, another way of doing it would be to create a CallDates table with just 2 fields, the foreign key of whatever is driving the form and the date. Join them on the key and use a subform to display the dates stacked or loop through the recordset of 5 and place them in order in 5 separate fields on the form. This could be set up to either set off a warning if a 6th call was being made (so could the if statements) or drop the oldest of the 5 dates and replace it with the new date.

 

[heidihutton]

that's what I wanted it to do. I'll try this and see if it works. Thanks.