Show a Field from Last Record in a Form

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

Guest

I have a FORM where the user enters new students. The new student gets an ID
number (manual input; "autonumber" was not appropriate for what I'm doing).
A macro opens the form to a NEW RECORD. However, I need the form to display
the ID_Num FIELD of the LAST RECORD in the DB. In reading some of the posts,
it seems this can't be accomplished without writing code. If there is an
alternative, I would certainly appreciate it. If not, is there a module out
there that will do this (since I haven't written a program since college and
have no knowledge of VB)? It would be a disappointment to think MS didn't
have a simplier solution for this common task other than learning and writing
VB. --
DGSpencer
 
Define what the "last record" is and how the database will know what it is.
Chances are that, if you're using a sequential number for ID_Num, a DLookup
function might be used in the control source of a textbox on the form, but
we need more details.
 
The ID_Num (a numeric field, not text) is to supposed to be sequential in
this DB. Therefore, the last record entered into the DB would have the last
number used.

Thanks
 
Put a textbox on the form. Set its ControlSource to an expression similar to
this:

=DMax("ID_Num", "NameOfTable", "ID_Num<>" &
Nz([NameOfControlBoundToID_NumField], 0))
 
Worked PERFECTLY! I guess it's back to the books to learn some VB.

thanks :)
--
DGSpencer


Ken Snell said:
Put a textbox on the form. Set its ControlSource to an expression similar to
this:

=DMax("ID_Num", "NameOfTable", "ID_Num<>" &
Nz([NameOfControlBoundToID_NumField], 0))

--

Ken Snell
<MS ACCESS MVP>

dgspencer said:
The ID_Num (a numeric field, not text) is to supposed to be sequential in
this DB. Therefore, the last record entered into the DB would have the
last
number used.

Thanks
Click to expand...
Click to expand...
 
dgspencer said:
Worked PERFECTLY! I guess it's back to the books to learn some VB.

thanks :)
Click to expand...

But you'll notice that you didn't need any VB for this one ... just a
bit of SQL, and the Query Design View helps with that. You can do a lot
with Access without ever touching VB. (And when possible, I avoid
including VB simply because it's likely to make maintenance look kinda
frightening to the customer.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
I'm a rookie but I found I could display the last record by opening the form
in design view and go to Properties, Form, Data, OrderBy property. Enter the
field you wish to sort followed with DESC (short for DESCENDING) without
quotation marks. Using your example:

ID_Num DESC

This will open the form and show the records in descending order by the
ID_Num.
 
In the properties menu for the form, select open --

Private Sub Form_Open(Cancel As Integer)
'add this line to the open event
DoCmd.GoToRecord acDataForm, "YourformName", acLast
' you will have 6 choices on which record to open - first, last, new etc.
End Sub

Damon
 
Back
Top