How to show the last record in an UnBound TxtBox? 2

[sanan]

Hi there
I have an Unbound TxtBox, which I would like to show the last value of Column in a table.
Do you have any Idea, that How this can be done?
I know it could be done using DlookUp, But I just do not know what to use for criteria for it.

=DlookUp(“SalesID”,”TableSales”,”me!LastRecord”)
This ”me!LastRecord” is what I am not so sure about.

Best Regards
Sanan

 

[GingerR]

have you tried "Dmax" or "DLast" instead of dlookup?

 

[Zion7]

Sanan, just for the record, the optional argument, in aggregate functions, DMax, DMin. DAvg, DCount etc..., is simply a WHERE statement, without the "WHERE".

DlookUp(“SalesID”,”TableSales”,”txtSales =" & me!LastRecord)

of course, you must consider, what data type, is your criterion. If string, surround with quotes,
”,”txtSales ='" & me!LastRecord & "'"

Date...hash,

”,”txtSales =#" & me!LastRecord & "#"

numeric nothing (as above).

My question is, what do you mean by "Last record"?

Last record according to a calculation, or hiearchy of some sort, or simply the last inputted record?

Last input would be...rec.MoveLast idea. Or as Ginger said,

=DMAX("SalesID","TableSales") no 3rd argument required.

...assuming SALESID is either autonumber, or incremental according to latest sales.

Hope this helps, Good luck!

 

[sanan]

Hi GingerR, Zion7

Thanks again for your excellent help.
It is working just fine.
But my Problem still remains. Actually the actual problem, which leads to this Thread is quite different.
In Access FE and SQL BE, I have a form called “Sales”, and in this form there is another SubForm Called “Account Details”. There is a Link Child / Master Field by name of “salesID”.
I have Clear “Me.Undo” problem; Upon Entering any data in the Form “sales” My Erase Button which is just a “Me.Undo” works, But as soon as I am about to enter any value in my subForm “Account Details” the Erase Button Does not work any more, and the Data gets saved in their respected Tables.

Any suggestions?

Best Regards
Sanan

 

[Zion7]

Sanan, it's because "Me", is not referring to the subform any more.

It should be
Forms!frmSales!AccountDetails.Form.Undo

Hope this helps, good luck!

 

[sanan]

Hi Zion7
Thanks so much for your Comment.
I tried the following codes;

Private Sub Command156_Click()
Me.Undo
Forms!Sales![AccountDetails].Form.Undo
End Sub

But no success.
Again as I said, It works fine while I am in the Parent Form “sales”, (Off Course The Me.Undo Part of it) As I am entering Data in any Controls of this form “sales” my AutoNumber TxtBox “SalesID” remains (shows) Empty or Blank.
But as soon as I reach the subForm This TxtBox which is located in my “Sales” form Immediately Generates it’s AutoNumber, And as you know, What ever Data that has entered in any of the Forms Gets Saved in it’s respected Table.
And my Command156_click Button does not work at all any more.
A reminder Our subForm “accoutDetails” has a DefaultView of datasheet and has 2 Primary Keys “salesID” and “Account#”.


Best Regards
Sanan

 

[GingerR]

how about making your subform invisible unless the FORM has data in it? That's what I've done in one of my databases. When the form first opens, i have the subform visible = false.
then AfterUpdate of the ID field on the main form, i make the subform visible = true.
then in your UNDO button, you could easily set subform visible back to false. that way it's not there for the user to even enter into.

 

[Zion7]

If Ginger's idea, doesn't work, try setting focus to the subform first, then Me.Undo...

Forms!Sales![AccountDetails].Form.SetFocus
Me.Undo

or

Forms!Sales![AccountDetails].Form.SetFocus
Forms!Sales![AccountDetails].Form.Undo

Not positive, but possibly?...

Good Luck!