Default Value

[Secret Squirrel]

I have a main form with just a date field on it and then on my subform I have
fields where the users can log members in. The problem I'm having is when I
set the default date in the date field to the current date and then create a
new record it will not save that record even if I enter data on my subform.
But when I take the default value out of the date field it works fine. Why is
that? And how can I fix it?

 

[Jeff Boyce]

A standard main form/subform construction uses a field from each form's
underlying table to "join" the two forms (and to inform the subform which
"parent" its records belong to).

If you only have a date/time field on your main form, how does the subform
"know" which parent record(s) it should use?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Secret Squirrel]

I have the date field linked to my subform. On my main form I have a field
called DateID and then on my subform I also have a field called DateID. They
are linked in a parent/child link.

 

[Jeff Boyce]

I'm not sure I understand the underlying table structure yet.

More info, please?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Secret Squirrel]

On the main form I have the DateID field which is the only field in the
tblDates. It is also the primary key. The subform has my member detail
information. It is tied to the tblDailyDetail. This table also has a DateID
field which is linked to the tblDates. So when a date is created in the
tblDates it is linked to the members info in the tblDailyDetail.

 

[Jeff Boyce]

If the only field in tblDates is a dateID field, how do you know what Date
is associated with that ID?

Moreover, you want to associate a date with a member detail record, why not
just put the date into that record? What does having a DateID field allow
you to do?

What is the data type of the DateID field in tblDates? ... in
tblDailyDetail?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Secret Squirrel]

The DateID field in both tables is set to date/time. The reason I have a
separate table is so I can group the detail by date. When a new date record
is created in tblDates then the users will log in all the members for that
day in the subform which is located in tblDailyDetail. I hope I'm making
sense. I can send you a copy of the DB if you'd like to look at it. It might
be easier to understand it then.

 

[Jeff Boyce]

Here's another approach...

Remove the tblDate. Change the DateID field in tblDailyDetail to something
like [DetailDate] (keep it a date/time data type).

Remove the main form (date only). Add a control on the (no longer a)
sub-form to get a date (or date/time) value -- and you can have it default
to something like Now() so that new detail records get date/time stamped.

Create a query against tblDailyDetail. Change that query to a Totals query,
and GroupBy the [DetailDate]. This should give you a way to "group the
detail by date". If you don't want date/time grouping, first use
DateValue([DetailDate]), then GroupBy that.

This approach saves having a second table and having to coordinate forms.
(What I noticed about your earlier post is that it focused on "how" you
wanted to do something, not on what you needed to accomplish -- i.e., group
by date.)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP