DLookup for a Label in a Form

[grgimpy]

I've set up the code for a DLookup into a specific table which fills in a label on one of my forms as:

Me.LSL.Caption = DLookup("[SMD-pHLSL]", "SM Developer-PM Log", "[SMD-PMType]='ControlLimits'")

which fills in the value upon load. How would I change the code so that it looks up the most recent value entered into the table (for SMD-pHLSL). Right now I am using a generic row I typed into the table (ControlLimits), which I don't really want in there. I would like it to look up an entry for SMD-PMType, which will be common, and look up the most recent entry under that "heading". Hope that makes sense.

Thanks

 

[TheAceMan1]

How are ya grgimpy . . .

A discrete example is required! . . .

See Ya! . . . . . .

 

[WildHare]

How do you define "Most Recent" ??? - is there a timestamp that can be "DMAX'ed" to get a key value, then use the key in a DLOOKUP?

This may require two steps, that's all...



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[TheAceMan1]

. . . and:
[ol][li]Is there any sequential order to [blue]SMD-pHLSL[/blue]?[/li]
[li]Is [blue]SMD-pHLSL[/blue] numeric, text, alphanumeric?[/li][/ol]

Agree with [blue]WildHare[/blue] in the use of DMax. If the primarykey is numeric . . . it may be all you need.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[grgimpy]

SMD-pHLSL is numeric and a column in the table SMD-PMType. SMD-PMType also contains a date/time column for each entry made containing a new SMD-pHLSL entry. My definition of most recent would be by time. Each number I am trying to bring up from the SMD-pHLSL column has a discrete date/time associated with it in the same table (SMD-PMType).

Thanks

 

[grgimpy]

I tried adding an autonumber field to my table and finding the DMax value for that, which would be the most recent entry.

Here's the code I came up with, but I don't think I'm entering it correctly:

Dim LSLLookup
LSLLookup = DMax("[ID]", "SM Developer-PM Log")
Me.LSL.Caption = DLookup("[SMD-pHLSL]", "SM Developer-PM Log", "[ID] = 'LSLLookup'")

 

[grgimpy]

alright, i figured it out, just in case any was wondering...


Dim LSLLookup
LSLLookup = DMax("[ID]", "SM Developer-PM Log")
Me.LSL.Caption = DLookup("[SMD-pHLSL]", "SM Developer-PM Log", "[ID] = " & LSLLookup)


works when ID is an autonumber field

Thanks for pointing me in the right direction guys

 

[WildHare]

Glad it's working the way you want - the DateTime stamp would have also worked, if I read your prior posting correctly. If each record gets a NOW()-kind of timestamp on insert, then finding the DMAX of that timestamp would, of course, return the "most recent" record - but most recently INSERTED. If your timestamp is indicative of some other external time, then obviously it would not.

And,you could have cut it down to just one line, as well, if that's any worth:

Me.LSL.Caption = DLookup("[SMD-pHLSL]", "SM Developer-PM Log", "[ID] = " & DMax("[ID]", "SM Developer-PM Log"))

--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman