Access subform - Current event does not fire on first record selected (4 Viewers)

[bodders24]

I have an annoying little problem with the following setup.

I have a SQL Server back-end database with a Microsoft Access (2019) front-end. One of the Access forms contains a subform which has as its recordsource a pass-through query reading data from the SQL Server back-end.

What I want to be able to do is select multiple records from the subform to edit them elsewhere. As I cannot use the Recordsetclone for an ADODB recordset I have set global variables for the SelTop and SelHeight properties of the subform. These are set on the Current event on the subform. I then use these to select the required records from the pass-through query.

The problem occurs when I open the form and include the first record on the subform in a group to be selected – the Current event does not fire. For all other records than the first it works perfectly.

Is there a simple solution that I am missing ?

Thanks for your help

Bodders

 

[MajP]

As I cannot use the Recordsetclone for an ADODB recordset

Why? Why not work with the forms recordset if ado or the clone of the forms recordset? But if it is a pass through query it is a dao recordset. Not sure I understand.

 

[Gasman]

If you walk through the events, you should find that the Current event is fired evern when opening a form/subform.

In fact in my test it ran 3 times?

Current event fired on main form opening, this current event is in the subform.
Current event fired on main form opening, this current event is in the subform.
Current event fired on main form opening, this current event is in the subform.

 

[arnelgp]

maybe you can save the selected records (IDs) to a textbox (text9) and use the value of textbox as filter to get your records.
see this demo, open Mainform and Click on the Recordselector of the subform.
you need to clean the values of text9 (remove leading and trailing commas).

 

[bodders24]

Thanks for your replies. I have found one way round it (sort of). If you select the first x records starting with record x the Current event fires. If you select the first x records starting with the first record it does not fire - but only immediately after the form has been opened. It works on subsequent selections. A bit odd but not a showstopper.

 

[cheekybuddha]

As I cannot use the Recordsetclone for an ADODB recordset

I don't know why you are using an ADODB recordset, but you do realise that an ADODB recordset has a .Clone property that works similarly to the form's .RecordsetClone.

So, instead of accessing Me.RecordsetClone, use Me.Recordset.Clone

See the docs

 

[Pat Hartman]

Here is a database that will help you to understand when form events fire. Please watch at least one of the videos so you will understand how the logging form works.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[MajP]

Thanks for your replies. I have found one way round it (sort of). If you select the first x records starting with record x the Current event fires. If you select the first x records starting with the first record it does not fire - but only immediately after the form has been opened. It works on subsequent selections. A bit odd but not a showstopper.

That is as expected. If you select the first record which is the current record when the form opens then the on current event is not going to happen again when you select the record. You will need another event or another code to capture selecting the current record.