Changing record displayed in subform

[IanC]

My database has three tables linked in sort of a CUSTOMER > ORDERS > ITEMS ORDERED fashion. (The data is different but the linking concept is the same).

My main data entry/display form is similarly linked in a MAIN FORM > SUBFORM > SUB-SUBFORM display.

When a "customer" is chosen, I would like to display the most recent "order" (ie last order). Similarly, I'd like to display the last item on that "order".

Because of the real type of data, and the ability of the data entry clerk, I don't want to change the sort order on the two sub-tables.

Ideally I'd like to just go to the last "order" record for that client, and last "item" for that order.

I assume I'd do this in the OnCurrent event in the main form.

I've tried every syntax of "DoCmd.GoToRecord" that I can think of, but none has worked right. I usually just end up changing to the last customer on file.

I also tried triggering the "last record" buttons on the sub-forms, but still ended up changing the customer record that was displayed.

Any suggestions or help would be appreciated.

I'm using Access 97 if that has any bearing on your suggestions.

[This message has been edited by IanC (edited 11-20-2000).]

 

[Richie]

If your entering dates for orders rec'. just sort that field desc.

 

[Karl K]

Do the same thing you did on the main form, but do it on the On Current event of the subform. See how that works. I also like the suggestion of having a sort order set up of either Date descending, or Order Number descending (assuming your order numbers increase)

 

[IanC]

Perhaps I didn't state my question properly.

The record on the main form is chosen using a dropdown combo-box listing all available records.

I DO NOT WANT TO CHANGE THE SORT ORDER on the two sub-tables. I know that sorting differently is the easiest way to achive this, but I want to maintain the "previous record is a earlier date" and "next record takes you to a later date" order. The person who will use this system is not very computer literate. It is my believe that keeping the data in a natural chronological order is a better user interface for this application.

I have tried putting a "GoToRecord" command in the "OnCurrent" event of the sub-form. But doing this does not work. The result is that you cannot look at any record other than the last record for that client. Whenever you change records, it immediately jumps to the last record for that client.

So once again it lookins like I need to use the "OnCurrent" event of the master from to change records on the sub-form.

The question is HOW! What is the syntax?