Can only interact with first record of a form

[speedball]

Hello
I have a form (FormA) that contains a continuous subform (SubformB). When I open or refresh FormA, some VBA code updates the records in SubformB using the following line of code:

Set Me.SubformB.Form.Recordset = rs

Where rs is a recordset that is populated via a stored procedure in SQL Server.

I have a button on the SubformB, which when clicked on opens another form.

This was all working fine. However, I made some changes to something and now I'm only able to click on the button for the first record of the SubformB, i.e. the one at the top.

If I scroll down the list and click on another button, it just moves me to the top of the list.

If I filter or sort SubformB, I'm only able to click on the record at the top.

I'm not even able to highlight the text of the other records.

I have other similar Form/Subform combinations and don't have the same problem on those. I've checked the form properties and can't see any differences.

SubformB is on a tabbed control, but again I have other subforms on tabbed controls and don't have this issue.

Does anyone know what I may have inadvertantly changed, that has resulted in this problem?

Thanks in advance
Speedball

 

[boblarson]

Did you accidentally connect two autonumber fields as links?

 

[speedball]

Hi Bob

The recordset that the subform uses is the union of two SQL Server tables, each of which includes an identify field. Would that be a problem? It’s been setup like that for a while and has worked okay up until recently.

Something that I perhaps should have mentioned originally, is that I actually have 2 subforms – SubformB and SubformC. Both are populated using the same recordset. So I actually have:

[COLOR=black][FONT=Arial]Set Me.SubformB.Form.Recordset = rs[/FONT][/COLOR]
[COLOR=black][FONT=Arial]Set Me.SubformC.Form.Recordset = rs[/FONT][/COLOR]

Each subform is on a different tab of my tabbed control.

I’ve been experimenting a little more and if I remove SubformC, then it seems to solve the problem.

Any thoughts on why removing SubformC would fix it? And how can I get it to work with both SubformB and SubformC (and potentially more Subforms)?

Thanks
Speedball

 

[boblarson]

Unless I'm misunderstanding what you are saying, if you use a Union query it is not going to be updateable.

 

[speedball]

Hi Bob

I’m using a union query to generate a recordset for my subform, but I don’t need or want it to be updateable. The recordset just populates the form with a snapshot and there is no constant link between my Access front end and SQL Server backend.

All I need is to be able to click on a button for each record on the subform, which in turn will open another form and bring in another recordset from SQL Server. But something has happened and I’m now only able to click on the button on the first record of my subform.

Thanks
Speedball

 

[speedball]

Hi Bob

The problem appears to be due to having two subforms with the same recordset. If I have a different recordset for each subform, then it works as intended.

So I now have

Set Me.SubformB.Form.Recordset = rs1
Set Me.SubformC.Form.Recordset = rs2

And just make sure that rs1 and rs2 are the same. This seems to work fine.

Thanks for your help
Speedball