Using arrow keys in a subform, refreshing main form

[HanskiT]

I have a main form and a subform showing data from the same tables. The subform is shown in datasheet to display a list of records. The idea is that the user can use arrow keys in the subform to quickly see (and edit) all fields in the main form.

I found three problems. The first one was to find a suitable event, the one I found is "Form_KeyDown". So far so good. The second problem was that the focus was moved to the main form, solved by setting the subform's tab index to "0". Third, found out that the event is triggered for the current record, not the "landing" record in case of the Arrow up/down keys. So I solved by following:

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
LngAbspos = Me.Form.Recordset.AbsolutePosition
if KeyCode = vbKeyDown Then
If LngAbspos < Me.Form.Recordset.RecordCount Then DoCmd.GoToRecord , , acNext

MainformRefresh me.evID
Me.Form.Recordset.AbsolutePosition = LngAbspos
End If

End Sub

Feels clumsy but seems to work. If there is a better way I would be happy to know. If not, hopefully this entry is useful to somebody facing the same issue.

 

[MajP]

you may be able to simplify this by binding the subform and mainform to the same recordset. Then no code is needed. Whatever is current in the main or sub will be the current record in the other. Move in one it synchs and moves the other.

So in the forms load event
set me.subformname.form.recordset = me.recordset

 

[HanskiT]

Thanks, it would be an obvious solution. I will give it a try.

 

[MajP]

Thanks, it would be an obvious solution.

Actually after thinking about it, you probably will not gain anything or make it much simpler. You already have just one line to set the absolute position.

 

[Pat Hartman]

I am confused by your explanation. The point of a subform is that there are many subform records for each individual mainform record. Your description makes it sound like your relationship is 1-1 so that each subform record has a different mainform parent. If that is the case, create a query that joins the two tables and base the form on that. you will be able to update both records on the same form.

 

[MajP]

That is my fault I used the term mainform and subform and should have said subform datasheet and single form view. Basically it is a "split form" where you can navigate the subform datasheet and select the single form view.

 

[isladogs]

Agree with Pat.
If the main form & subform use the same recordset, you are effectively creating a split form. In which case you may as well use the built in split form or, for more flexibility, use an emulated version such as this: https://www.access-programmers.co.uk/forums/showthread.php?t=294421

 

[MajP]

If the main form & subform use the same recordset, you are effectively creating a split form. In which case you may as well use the built in split form or, for more flexibility, use an emulated version such as this: https://www.access-programmers.co.uk...d.php?t=294421

I might be missing something, but how is that any different from what has been already done or already suggested? That appears to be exactly what the OP did. There are lots of ways to synch. You can use the same recordset, you can link the master and child to an unbound control and set the value of the control on the on current, or you can do what the OP did and code the absolute position. You can argue which is the best approach, but all should work. The thing they were demonstrating is the ability to use an up and done arrows to navigate a datasheet like you can with a spread sheet.

 

[isladogs]

If you look at the times, you posted your last reply whilst I was writing mine.
It seems we both said much the same thing

 

[HanskiT]

you may be able to simplify this by binding the subform and mainform to the same recordset. Then no code is needed. Whatever is current in the main or sub will be the current record in the other. Move in one it synchs and moves the other.

So in the forms load event
set me.subformname.form.recordset = me.recordset

Noticed that if link master/child fields is set, only one record is shown in the subform, so it cannot be used for browsing. I have been using the subform to show linked records but not for browsing the same records as the main form. In this case the intention was to see the full list of records (but only some fields) in the sub form (datasheeet view) and the selected one with all fields in the main form.