Dlookup in Form want pull data correctly from Table

[Ttallie]

I need to pull the date form the last record of a Table and populate a text
box in a form. Why does the following code pull the date from the first
record instead of the last?

I also created a query that pulls the correct date and put this in the
Control Source of the text box's property sheet and it returns "#NMAE?". I
prefer to do this in the code. Is there better way to accomplish this task?

Private Sub CmdTest_Click()
Dim DimLastDate As Date
DoCmd.OpenTable "TblFuelServiceCharge", acViewNormal, acEdit
DoCmd.GoToRecord , , acLast
DimLastDate = DLookup("[DateEnd]", "TblFuelServiceCharge")
DoCmd.Close acTable, "TblFuelServiceCharge"
Me!TxtTest.SetFocus
Me!TxtTest.Value = DimLastDate
End Sub

 

[Jeff Boyce]

It looks like your code is telling Access to look up "A" record, not the
first, not the last, just "A" record.

Anyway, Access stores data in tables as a set, not as "first" or "last".
How are YOU deciding which is first and which is last?

You need to tell Access how to do that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ttallie]

Thanks for your quick response!

I thought that the acLast portion of the GoToRecord Method would bring the
last created record into focus.

Since records will constantly be added to this table, how can I modify me
original code to get the date from the very last record created? Could the
SQL statement be converted to code? Or????

SELECT Last(TblFuelServiceCharge.DateEnd) AS LastOfDateEnd
FROM TblFuelServiceCharge;

It looks like your code is telling Access to look up "A" record, not the
first, not the last, just "A" record.

Anyway, Access stores data in tables as a set, not as "first" or "last".
How are YOU deciding which is first and which is last?

You need to tell Access how to do that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to pull the date form the last record of a Table and populate a text
box in a form. Why does the following code pull the date from the first
record instead of the last?

I also created a query that pulls the correct date and put this in the
Control Source of the text box's property sheet and it returns "#NMAE?".
I
prefer to do this in the code. Is there better way to accomplish this
task?

Private Sub CmdTest_Click()
Dim DimLastDate As Date
DoCmd.OpenTable "TblFuelServiceCharge", acViewNormal, acEdit
DoCmd.GoToRecord , , acLast
DimLastDate = DLookup("[DateEnd]", "TblFuelServiceCharge")
DoCmd.Close acTable, "TblFuelServiceCharge"
Me!TxtTest.SetFocus
Me!TxtTest.Value = DimLastDate
End Sub

 

[fredg]

I need to pull the date form the last record of a Table and populate a text
box in a form. Why does the following code pull the date from the first
record instead of the last?

I also created a query that pulls the correct date and put this in the
Control Source of the text box's property sheet and it returns "#NMAE?". I
prefer to do this in the code. Is there better way to accomplish this task?

Private Sub CmdTest_Click()
Dim DimLastDate As Date
DoCmd.OpenTable "TblFuelServiceCharge", acViewNormal, acEdit
DoCmd.GoToRecord , , acLast
DimLastDate = DLookup("[DateEnd]", "TblFuelServiceCharge")
DoCmd.Close acTable, "TblFuelServiceCharge"
Me!TxtTest.SetFocus
Me!TxtTest.Value = DimLastDate
End Sub

The word 'Query' has specific meaning in Access.
You have written code, not a query.

1) No need for Code.
2) No need to Dim a variable.
3) No need to open a table.
4) No need to go to a record.
5) In Access tables there is no first or last record. Records are
stored like nails in a bucket. All mixed up.
6) So, what do you mean by the 'Last' record?
The one with the latest date?
Or the one most recently entered (which may, or may not, be the one
with the latest date)?

To find the latest date in the table regardless of when entered (there
may be several records with the same latest date) try, in the control
source of an unbound text control:

=DMax("[DateEnd]", "TblFuelServiceCharge")

Look up DMax in VBA help, as well as
Where Clause + Restrict data to a subset of records
for more information.

To fine the most recently entered record, you will need to have a
field in the table that contains the date and time of record entry.
Then use DMax to find that latest entry date.

 

[Jeff Boyce]

Check Access HELP for the syntax (and meaning) of acLast -- Access doesn't
"mean" what you do.


Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your quick response!

I thought that the acLast portion of the GoToRecord Method would bring the
last created record into focus.

Since records will constantly be added to this table, how can I modify me
original code to get the date from the very last record created? Could the
SQL statement be converted to code? Or????

SELECT Last(TblFuelServiceCharge.DateEnd) AS LastOfDateEnd
FROM TblFuelServiceCharge;

It looks like your code is telling Access to look up "A" record, not the
first, not the last, just "A" record.

Anyway, Access stores data in tables as a set, not as "first" or "last".
How are YOU deciding which is first and which is last?

You need to tell Access how to do that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to pull the date form the last record of a Table and populate a
text
box in a form. Why does the following code pull the date from the first
record instead of the last?

I also created a query that pulls the correct date and put this in the
Control Source of the text box's property sheet and it returns
"#NMAE?".
I
prefer to do this in the code. Is there better way to accomplish this
task?

Private Sub CmdTest_Click()
Dim DimLastDate As Date
DoCmd.OpenTable "TblFuelServiceCharge", acViewNormal, acEdit
DoCmd.GoToRecord , , acLast
DimLastDate = DLookup("[DateEnd]", "TblFuelServiceCharge")
DoCmd.Close acTable, "TblFuelServiceCharge"
Me!TxtTest.SetFocus
Me!TxtTest.Value = DimLastDate
End Sub