Show some data from subforms on the main form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using Access 2003 and have created a form with several subforms and
everything works fine. The subforms display in datasheet format.

I've been asked if I can modify the main form slightly to display the most
recently entered data in the subforms on the main form. I had thought about
using a query but am not sure how I should set it up. Not all of the
subforms have a date field but they do all have an autonumber field. I can
use a query to sort by autonumber in descending order, but how do I specify
that the (now) first entry in specific fields are the ones that I want to
display in specific text boxes on the main form?

Thanks for any solutions.
 
I managed to get it sorted, so here's what I did, just in case anyone else
needs anything similar.

I came across DMax, DMin, DLookUp, DFirst and DLast. It's the final one
that I used to retrieve the final entries in the various fields for the last
record in the table.
 
DFirst and DLast are actually fairly useless functions: you can't assume
anything about the order in which records are stored in relational tables,
therefore the concept of "the first row" or "the last row" is not reliable.

DMin or DMax is more reliable.
 
Thank you Doug.

In that case, how would I go about it in a more "stable" manner? I have an
autonumber in column 1 and, lets say, column 2 is FirstName and column 3 is
LastName. At present, I'm working to get DLast of the FirstName and LastName
columns. I assume that DMin and DMax would get the first and last of these
alphabetically. I can see how I could use DMax using the autonumber column,
but, having got that, how would I "scroll" across to retrieve the relevant
entries in the FirstName and LastName columns?
 
Once you know the ID of the field, you look up the other values using the
ID.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Popeye said:
Thank you Doug.

In that case, how would I go about it in a more "stable" manner? I have an
autonumber in column 1 and, lets say, column 2 is FirstName and column 3 is
LastName. At present, I'm working to get DLast of the FirstName and LastName
columns. I assume that DMin and DMax would get the first and last of these
alphabetically. I can see how I could use DMax using the autonumber column,
but, having got that, how would I "scroll" across to retrieve the relevant
entries in the FirstName and LastName columns?
Click to expand...
 
Having done some research, I think I've got it now, so here's my solution
just in case someone else might need it:

Some assumptions - the table is called Data, the autonumber field in Data is
ProjectID and the field containing the information to be retrieved is
LastName.

I created a form and added an unbound text box. I set it's control source as:

=DLookUp("[LastName]","Data","ProjectID=" & DMax("ProjectID","Data"))

This returns the last entered LastName in the table. It took me quite a
while to get the various brackets and double quotation marks sorted.
 
Back
Top