Default value on subform

[Bruce]

I have a form that describes an event, and a subform that
lists attendance. Both form and subform are tied to
tables. The PK from the table describing the event is a
FK in each attendance record (each person in the
attendance table is a separate record). In case it
matters, there is a Roster table from which names are
drawn for the attendance table, which is a junction table
between the Event and Roster tables.

To use the database, a user enters the Event information,
then the attendance. The first attendee is John Doe,
5/10/04. When the user tabs to the second record (the
next attendee), I want the date to default to 5/10/04.
When it is time to enter information for another event (as
recorded in the main form), I would like the date field to
revert to being blank.

The date is entered in the control txtDate (on the
subform). Somebody suggested Me!txtDate.DefaultValue = Me!
txtDate as the After Update event for that control, but
this has the effect of making the date default to either
Dec 30 or Dec 31, 1899 for each new record, no matter what
date is entered in the previous record. Further, clicking
into the text box with the 1899 date usually shows a time
of day as well (e.g. 6:00:00 AM). The control is
formatted mmm dd", "yyyy, and behaves as expected when an
actual date is entered. Clearly there is something wrong
with the code, which has at least yielded results that are
more entertaining than some other errors have been.

Here is the outline of the table structure, if it
helps.

Event Table (main form)
PK (autonumber)
Description of event
Department
etc.

Attendance Table (subform)
PK (autonumber)
FK (from Event table)
FK (from Roster table)
Date

 

[Allen Browne]

The DefaultValue property is a string. Try:
With Me!txtDate
If Not IsNull(.Value) then
.DefaultValue = """" & Format(.Value, "mmm dd yyyy") & """"
End If
End With

The other alternative is to use the BeforeInsert event of the form to assign
the same value as the previous row (read from the form's RecordsetClone), if
there is one. That also handles the issue of not copying from the previous
record when you move to a new record in the main form.

 

[Kevin Sprinkel]

Hi, Bruce; sorry I missed on this one. You can also type
the string as a date by:

Me!YourControlName.DefaultValue = "#" & Me!YourControlName
& "#"

 

[Bruce]

Thanks. I would like to learn about using RecordsetClone,
but that will take a bit of doing, as the Access Hlep
files are rather opaque on that subject and it will take a
while to research it in newsgroups, etc., so for now I
went with your first suggestion, except that I added Forms!
frmEvent!fsubAttendance.Form!txtDate.DefaultValue = "" to
the main form's On Current event. This seems to do the
trick.

 

[Bruce]

Your suggestion works too, as does the suggestion in the
other posting. Now I need to decide if one has an
advantage over the other. I will do that with apostrophes
in the code, trying first one, then the other. Gets
confusing when there are so many ways of doing the same
thing. Thanks again for your help.