Navigating buttons on form 1

[TrekBiker]

I’m setting up a database for a hedgehog hospital to record new arrivals.

Records have an RefID autonumber primary key, AdmissionDate, Refindex number if more than one arrive in a batch on the same day, and other details. The user wants a Reference field, which is created in the user form by linking AdmissionDate and the Refindex order of being entered into the database.

This is a section from the Hogdata table.

The user form has this

Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord , , acLast
End Sub

The result at present is that the form opens to RefID 81, or the first record for the latest date 04/12/2019. This is the first of 4 for this most recent batch of arrivals.

I want it to open to the most recent one, RefID 178. The Previous/Next buttons need to give the right sequence too.

Opening the form to the highest RefID would solve this but there’s always a possibility that hogs might need be added to an earlier arrival. Purely sorting on RefID would make a wrong sequence when navigating using Previous/Next.

Ideal would be to sort on the Reference field, which is a combination of a date and a number. How do I do this?

I tried making a numerical AdmissionDate and adding the RefID to it but this just moved the date forward.

 

[sxschech]

I want it to open to the most recent one, RefID 178

Should this be 179?, otherwise, how do we know that 04/12/19 - 03 is more recent than 04/12/19 - 04 ?

Assuming that you meant refid 179, but want to use Reference as the field, you could put an order by on your form

Also can add DESC if you want to sort in descending order.
[tt]Reference DESC[/tt]

 

[TrekBiker]

Thanks sxschech.

The order is correct, so 03/12/19 - 03 came before 03/12/19 - 04. This is automatic because for batch items the code allocates the next index number so the Autonumber increments by 1.

Using Reference as the Order By property produced an odd result with the apparent most recent record being one with n AdmissionDate in the middle of the Hogdata table range. I'll check out why but it'll be because of the way the AdmissionDate + RefIndex values are interpreted in a sort. In fact, applying a sort to this field in HogData confirms this.

But, the suggestion of using the Order By property is very helpful if I change it to the Autonumber field RefID. This makes the form open to RefID 179, being the highest, and allows the Previous button to move records back in correct order. The one thing it doesn't cater for is if details of a hedgehog belonging to a batch are entered out of sequence. But I think that's more a theoretical situation than likely.