DLast

[Guest]

I would like to have the last date entered appear as the default value in a
subform that appears for new records. At first I thought I could do this
using Max, but I get 30-Dec-1899 for the new records.

In any case, I have tried numerous differenct things, including the
following attempt using the DLast function (which results in an Error):

=DLast("[DATE_VIDEO]","[ptbl_FISH_INDIV]","[ptbl_FISH_INDIV].[SAMPLEID]=#")

FYI: SAMPLEID is the one to many relationship to a table called
ptbl_MASTER_SAMPLE (where SAMPLEID is the primary key).

I thought this would be a simple thing to figure out (and I am a bit green).
What am I missing? THANKS!!

 

[Guest]

Hi Beks,

How about this:

dlookup("max([DATE_VIDEO])","[ptbl_FISH_INDIV]","[ptbl_FISH_INDIV].[SAMPLEID]=" & lngSampleID)

Assuming that lngSampleID is a variable containing the SampleID.

Hope this helps.

Damian.

 

[John Vinson]

I would like to have the last date entered appear as the default value in a
subform that appears for new records. At first I thought I could do this
using Max, but I get 30-Dec-1899 for the new records.

In any case, I have tried numerous differenct things, including the
following attempt using the DLast function (which results in an Error):

=DLast("[DATE_VIDEO]","[ptbl_FISH_INDIV]","[ptbl_FISH_INDIV].[SAMPLEID]=#")

FYI: SAMPLEID is the one to many relationship to a table called
ptbl_MASTER_SAMPLE (where SAMPLEID is the primary key).

I thought this would be a simple thing to figure out (and I am a bit green).
What am I missing? THANKS!!

DLast() is misleading. It does not refer to the last date; it refers
to the last record, in disk storage order - and that order is
arbitrary!

If you want to return the maximum date, you need DMax() instead. The
30-Dec-1899 is the starting point for date values, so these records
apparently have zero or NULL in the date field.

I have NO idea what you mean by the # in the expression above. Where
do you want to get the SampleID?

Try

=DMax("[DATE_VIDEO]", "[ptbl_FISH_INDIV]", "[SAMPLEID] = " & <some
control on your form containing the sample ID that you want>)

John W. Vinson[MVP]

 

[Rick Brandt]

Hi Beks,

How about this:

dlookup("max([DATE_VIDEO])","[ptbl_FISH_INDIV]","[ptbl_FISH_INDIV].[SAMPLEID]="
& lngSampleID)

Assuming that lngSampleID is a variable containing the SampleID.

Hope this helps.

It won't. The first argument of DLookup() cannot be an aggregate function.
However instead of DLookup() he could use DMax() and that would work.

 

[Guest]

Hi Rick,

It won't. The first argument of DLookup() cannot be an aggregate function.

<end quote>

Thanks for your feedback, but it works for me (using Access 2003)... so you
might need to have another crack at it.

Damian.

Hi Beks,

How about this:

dlookup("max([DATE_VIDEO])","[ptbl_FISH_INDIV]","[ptbl_FISH_INDIV].[SAMPLEID]="
& lngSampleID)

Assuming that lngSampleID is a variable containing the SampleID.

Hope this helps.

It won't. The first argument of DLookup() cannot be an aggregate function.
However instead of DLookup() he could use DMax() and that would work.

 

[Rick Brandt]

Hi Rick,



Thanks for your feedback, but it works for me (using Access 2003)...
so you might need to have another crack at it.

Fair enough. Expressions have always been allowed in the first argument, but it
never occurred to me to use Max() or any other aggregate function inside of
DLookup() when there are already other Domain Aggregate functions that provide
that functionality. Using the appropriate outer function just makes more sense
to me.

 

[Guest]

Arggh - I have tried all of the suggestions and am still not quite there. I
think I am so close but missing a key element.

SAMPLEID is the Autonumber (unique) Primary key in ptbl_MASTER_SAMPLE that
links to ptbl_FISH_INDIV. I would like the default value to be the most
recent date entered into the subform (not the entire dataset).

So, from what I Understand, I should use:

DMax("[DATE_VIDEO]","[ptbl_FISH_INDIV]","[SAMPLEID]"="[ptbl_MASTER_SAMPLE].[SAMPLEID]")

But the result is blank. I appreciate your help and your patience (I am a
bit green with these type of controls). Thanks!

I would like to have the last date entered appear as the default value in a
subform that appears for new records. At first I thought I could do this
using Max, but I get 30-Dec-1899 for the new records.

In any case, I have tried numerous differenct things, including the
following attempt using the DLast function (which results in an Error):

=DLast("[DATE_VIDEO]","[ptbl_FISH_INDIV]","[ptbl_FISH_INDIV].[SAMPLEID]=#")

FYI: SAMPLEID is the one to many relationship to a table called
ptbl_MASTER_SAMPLE (where SAMPLEID is the primary key).

I thought this would be a simple thing to figure out (and I am a bit green).
What am I missing? THANKS!!

DLast() is misleading. It does not refer to the last date; it refers
to the last record, in disk storage order - and that order is
arbitrary!

If you want to return the maximum date, you need DMax() instead. The
30-Dec-1899 is the starting point for date values, so these records
apparently have zero or NULL in the date field.

I have NO idea what you mean by the # in the expression above. Where
do you want to get the SampleID?

Try

=DMax("[DATE_VIDEO]", "[ptbl_FISH_INDIV]", "[SAMPLEID] = " & <some
control on your form containing the sample ID that you want>)

John W. Vinson[MVP]

 

[John Vinson]

Arggh - I have tried all of the suggestions and am still not quite there. I
think I am so close but missing a key element.

SAMPLEID is the Autonumber (unique) Primary key in ptbl_MASTER_SAMPLE that
links to ptbl_FISH_INDIV. I would like the default value to be the most
recent date entered into the subform (not the entire dataset).

So, from what I Understand, I should use:

DMax("[DATE_VIDEO]","[ptbl_FISH_INDIV]","[SAMPLEID]"="[ptbl_MASTER_SAMPLE].[SAMPLEID]")

But the result is blank. I appreciate your help and your patience (I am a
bit green with these type of controls). Thanks!

Not quite. You've got some misplaced quote marks. Try

DMax("[DATE_VIDEO]", "[ptbl_FISH_INDIV]","[SAMPLEID]=" &
[ptbl_MASTER_SAMPLE].[SAMPLEID])


John W. Vinson[MVP]

 

[Guest]

Now I get #Name? when entering a new record. Gosh, I never thought this
would be so complicated. Maybe I should try a new method - how can I run a
max query and have the result update the field in the subform when starting a
new record?
Thanks again.

Arggh - I have tried all of the suggestions and am still not quite there. I
think I am so close but missing a key element.

SAMPLEID is the Autonumber (unique) Primary key in ptbl_MASTER_SAMPLE that
links to ptbl_FISH_INDIV. I would like the default value to be the most
recent date entered into the subform (not the entire dataset).

So, from what I Understand, I should use:

DMax("[DATE_VIDEO]","[ptbl_FISH_INDIV]","[SAMPLEID]"="[ptbl_MASTER_SAMPLE].[SAMPLEID]")

But the result is blank. I appreciate your help and your patience (I am a
bit green with these type of controls). Thanks!

Not quite. You've got some misplaced quote marks. Try

DMax("[DATE_VIDEO]", "[ptbl_FISH_INDIV]","[SAMPLEID]=" &
[ptbl_MASTER_SAMPLE].[SAMPLEID])


John W. Vinson[MVP]

 

[John Vinson]

Now I get #Name? when entering a new record. Gosh, I never thought this
would be so complicated. Maybe I should try a new method - how can I run a
max query and have the result update the field in the subform when starting a
new record?
Thanks again.

You can use the Form's (the one you're using as a subform)
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtDate_Video = DMax("[DATE_VIDEO]", _
"[ptbl_FISH_INDIV]","[SAMPLEID]=" & _
[ptbl_MASTER_SAMPLE].[SAMPLEID])
End Sub

assuming that you have a textbox named txtDate_Video bound to that
field.

John W. Vinson[MVP]

 

[Guest]

Oh dear now I am really confused. What do you mean by textbox bound to that
field? The DATE_VIDEO field??

All I want is the last date entered to be automatically (like a default)
into the DATE_VIDEO field in a new record on the subform, so that it wouldn't
have to be re-entered for each record on each day. Should I consider a
database redesign? Here is the current design:

PARENT TABLE - ptbl_MASTER_SAMPLE
FIELDS ARE: SAMPLEID, SITENAME AND HARDDRIVE

CHILD TABLE - ptbl_FISH_INDIV
FIELDS ARE: SAMPLEID, DATE_VIDEO, TIME, CAMERA, MARKINGS, REVIEWER

DATE_VIDEO and REVIEWER are fields that can be repetitive and to save time,
if the most recent value for each could be a default would make data entry
more efficient.

I appreciate your support.

Now I get #Name? when entering a new record. Gosh, I never thought this
would be so complicated. Maybe I should try a new method - how can I run a
max query and have the result update the field in the subform when starting a
new record?
Thanks again.

You can use the Form's (the one you're using as a subform)
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtDate_Video = DMax("[DATE_VIDEO]", _
"[ptbl_FISH_INDIV]","[SAMPLEID]=" & _
[ptbl_MASTER_SAMPLE].[SAMPLEID])
End Sub

assuming that you have a textbox named txtDate_Video bound to that
field.

John W. Vinson[MVP]

 

[John Vinson]

CHILD TABLE - ptbl_FISH_INDIV
FIELDS ARE: SAMPLEID, DATE_VIDEO, TIME, CAMERA, MARKINGS, REVIEWER

DATE_VIDEO and REVIEWER are fields that can be repetitive and to save time,
if the most recent value for each could be a default would make data entry
more efficient.

Ok... that's a bit different.

One simple way to do this is to set the subform control's DefaultValue
property in its AfterUpdate event. This lets the user enter a date or
a reviewer once, and it will "stick" and become the default for any
new records, until they enter a different value.

To do so, open the Form (that you're using as a subform) in design
view. Select the DATE_VIDEO control. Note that Access gives Controls
the same name as their Bound Column, but this can be confusing to the
code and to the developer - you might want to consider changing the
Name property of this textbox from DATE_VIDEO to txtDate_Video so it's
unambiguous!

Anyway, view the properties of this control. On the Events tab select
the AfterUpdate event, and click the ... icon. Choose Code Builder
from the options. You'll get the Sub and End Sub lines for free; just
add one more:

Private Sub txtDate_Video_AfterUpdate()
Me.txtDate_Video.DefaultValue = Chr(34) & Me.txtDate_Video & Chr(34)
End Sub

Chr(34) is a doublequote character "; a Default Value must be a
string, no matter what the datatype of its underlying field is. This
will set the DefaultValue property of the control to

"10/24/2006"

if that's the date the user entered. When a new record is created, it
will default to that date. Use your actual control name in place of
txtDate_Video.

You can do the same for the Reviewer control.

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[Guest]

Yay - it works! Thanks for your help. I sure appreciate your step by step
explanation. Also thanks for the tip for renaming the textbox - makes things
much clearer.