CODEWITHSUNDEEP
ms-accessvbarecordset
jaromey
jaromey

Reputation: 676

Programmatically changing the RecordsetType in ms access or a better method

In ms access I have a form which also has a sub-form. I wanted to programmatically change the RecordsetType in the main-form to snapshot when the sub-table/sub-form has associated records in the one-to-many relationship. I wrote some vba code to do this but I was experiencing some very odd behaviour.

I then discovered that it was because when I change the RecordsetType, the form refreshes and navigates back to the first record. That then causes the On Current event and associated code to fire in both the sub-form and main-form twice. I was thinking of using DoCmd.SearchForRecord to navigate back to the original record but quickly realized that it wouldn't work because the the WHERE condition in the DoCmd.SearchForRecord gets overridden when the events fire the second time around. It all seemed so inefficient any way.

Are there any other methods to do what I am trying to do? I don't really want to set the controls in the form to 'Disabled'.

Upvotes: 1

Views: 1133

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

MS Access has a selection of form properties that should suit: AllowEdits, AllowDeletions and AllowAdditions. These can be set separately or together. In this case, it seems that you need AllowAddition set to Yes and AllowEdits set to No.

form properties

Upvotes: 1

Related Questions