DLookup function to return previous record (Access 2003)

[Dave K.]

I am trying to use a Dlookup function in a form to return a value from the
last entered record for a set of criteria. my current SQL Statement is as
follows:

Private Sub Mail_Type_AfterUpdate()
Me![Beginning Meter Reading] = DLookup("[beggining Meter Reading]",
"CopytblMeterReadingstest", "[mail type] = Forms![CopyFrmTest].Form![Mail
Type] and [Machine Model]=Forms![CopyFrmTest].Form![Machine Model]")

End Sub
For example Table data

Entry Date Machine Model Mail Type Beggining Reading Ending Reading
1 10/29/2008 250 REG PS FLAT 60116.246 60116.246

11 10/30/2008 250 REG PS FLAT 60116.246 60129.519

want to fill in the Beginning Field on the form with the Previous ending
Reading

 

[Beetle]

A few things to note here;

1) You said you wanted to find the highest Ending Reading but you
are looking up the Beginning Reading.

2) DLookup is only going to return the first record it finds that meets your
criteria, which won't necessarily be the one with the highest Ending
Reading value.

3) In the criteria string, the references to the form controls need to be
outsie of the quotes.

4) If this code is behind the form in question, then you can shorten things
up a bit by using the Me keyword.

So your revised code might look more like;

Private Sub Mail_Type_AfterUpdate()

Me![Beginning Meter Reading] = DMax("[Ending Meter Reading]",
"CopytblMeterReadingstest", "[mail type] = """ & Me![CopyFrmTest].Form![Mail
Type] & """ And [Machine Model] = """ & Me![CopyFrmTest].Form![Machine
Model] & """")

End Sub

In the above I have assumed that Mail Type and Machine Model are text
values. If they are numeric values then you will remove some of the
quotes like;

"[mail type] = " & Me![CopyFrmTest].Form![Mail
Type] & " And [Machine Model] = " & Me![CopyFrmTest].Form![Machine Model])

 

[Dave K.]

Yes Thanks I modified what you gave me and came up with the following and it
works great.

Me![Beginning Meter Reading] = IIf([Machine Model] = 1100, 0, DMax("[Ending
Meter Reading]", "CopytblMeterReadingstest", "[mail type] =
Forms![CopyFrmTest1].Form![Mail Type] and [Machine
Model]=Forms![CopyFrmTest1].Form![Machine Model]"))

This does exactly what I want. Thanks again Beetle.
--
Dave K.

A few things to note here;

1) You said you wanted to find the highest Ending Reading but you
are looking up the Beginning Reading.

2) DLookup is only going to return the first record it finds that meets your
criteria, which won't necessarily be the one with the highest Ending
Reading value.

3) In the criteria string, the references to the form controls need to be
outsie of the quotes.

4) If this code is behind the form in question, then you can shorten things
up a bit by using the Me keyword.

So your revised code might look more like;

Private Sub Mail_Type_AfterUpdate()

Me![Beginning Meter Reading] = DMax("[Ending Meter Reading]",
"CopytblMeterReadingstest", "[mail type] = """ & Me![CopyFrmTest].Form![Mail
Type] & """ And [Machine Model] = """ & Me![CopyFrmTest].Form![Machine
Model] & """")

End Sub

In the above I have assumed that Mail Type and Machine Model are text
values. If they are numeric values then you will remove some of the
quotes like;

"[mail type] = " & Me![CopyFrmTest].Form![Mail
Type] & " And [Machine Model] = " & Me![CopyFrmTest].Form![Machine Model])

--
_________

Sean Bailey

I am trying to use a Dlookup function in a form to return a value from the
last entered record for a set of criteria. my current SQL Statement is as
follows:

Private Sub Mail_Type_AfterUpdate()
Me![Beginning Meter Reading] = DLookup("[beggining Meter Reading]",
"CopytblMeterReadingstest", "[mail type] = Forms![CopyFrmTest].Form![Mail
Type] and [Machine Model]=Forms![CopyFrmTest].Form![Machine Model]")

End Sub
For example Table data

Entry Date Machine Model Mail Type Beggining Reading Ending Reading
1 10/29/2008 250 REG PS FLAT 60116.246 60116.246

11 10/30/2008 250 REG PS FLAT 60116.246 60129.519

want to fill in the Beginning Field on the form with the Previous ending
Reading