Sorting & Alerting User that Subform has more than 1 record

[Jen-Jen]

Hi all.
I have a form with 2 tab/subforms, one for general info on a candidate and another for submissions details.
The submissions tab/subform has the little navigation bar at the bottom with ability to add another submission for that candidate or move to next.
The submissionsTbl is sorted by newest first.
However when you open the form on a particular candidate that has more than 1 submission, it displays the first submission entered and then you click on the arrow to go to the next one.

1/ I would like the form's tab/subform to first display the newest submission for that candidate .
2/ And I would also really like to have a number come up on the subform to alert the users that there is more than 1 submission.
The info on the little navigation bar at the bottom is easily overlooked

AND would it be possible to only show a number if there are 2 or more submissions?

Many thanks!!

 

[arnelgp]

you add Sort Order to your submission subform (order by submission_date field DESC) so the latest will be the first.

on the second (alert), maybe add the number to the Caption of the submission tab on the Current event of the main form

Private Sub Form_Current()
Dim n As Long
n = DCount("1", "submission_table", "candidate_id = " & me.candidate_id)
If n > 1 then
    'note Page2 on my sample is the tab control of summision
    Me!Page2.Caption = "Submission (" & n & ")"
Else
    Me!Page2.Caption = "Submission"
End If
End Sub

 

[Gasman]

I would probably be using a query, then you can sort as you wish. However as you add one, you will need to requery to get the latest to the top?
For a table you can use the OrderBy

As for the count, use a DCount() and only set your control if more than 1.

 

[Pat Hartman]

If you only ever add one row at a time, then sorting after it is added to pop it to the top would be fine. However, this would be very disconcerting if the users typically enter several items at one sitting.